Come chiamare la stored procedure di Oracle che include il tipo definito dall’utente in java?

In Oracle DB:

Ho la seguente stored procedure:

procedure getInfo ( p_ids IN IDS_TABLE, p_details OUT cursor ) 

Il tipo IDS_TABLE è:

 create or replace type IDS_TABLE as table of IDS create or replace type IDS as object ( id1 NUMBER, id2 NUMBER, id3 NUMBER ) 

Come posso chiamare getInfo in Java?

L’impostazione manuale di un collegamento tra oggetti Oracle SQL e oggetti java non è un’operazione banale. In particolare, gli array (o le tabelle nidificate) di oggetti definiti dall’utente sono più complessi per passare da Java a Oracle rispetto agli array di tipi di dati standard. In altre parole, è più facile chiamare una procedura con firma:

 (TABLE OF NUMBER, TABLE OF NUMBER, TABLE OF NUMBER)` 

di una procedura la cui firma è:

 (TABLE OF (NUMBER, NUMBER, NUMBER)) <- your case 

Puoi scrivere un wrapper attorno alla tua procedura per trasformare il secondo caso nel primo caso.


Detto questo, non è imansible mappare la tua procedura. L'esempio seguente è ampiamente ispirato a un post di Tom Kyte . Tom descrive come mappare una TABLE OF NUMBER usando oracle.sql.ARRAY . Nel tuo caso dovremo anche usare oracle.sql.STRUCT per mappare l'object SQL IDS .

Si potrebbe anche voler sfogliare il documento Oracle JDBC, in particolare il capitolo Lavorare con i tipi di oggetti Oracle .

La prima è una configurazione simile alla tua:

 SQL> CREATE OR REPLACE TYPE IDS AS OBJECT ( id1 NUMBER, id2 NUMBER, id3 NUMBER ); 2 / Type created SQL> CREATE OR REPLACE TYPE IDS_TABLE AS TABLE OF IDS; 2 / Type created SQL> CREATE OR REPLACE PROCEDURE getInfo(p_ids IN IDS_TABLE) IS 2 BEGIN 3 FOR i IN 1 .. p_ids.COUNT LOOP 4 dbms_output.put_line(p_ids(i).id1 5 || ',' || p_ids(i).id2 6 || ',' || p_ids(i).id3); 7 END LOOP; 8 END getInfo; 9 / Procedure created 

Questa è la procedura java:

 SQL> CREATE OR REPLACE 2 AND COMPILE JAVA SOURCE NAMED "ArrayDemo" 3 as 4 import java.io.*; 5 import java.sql.*; 6 import oracle.sql.*; 7 import oracle.jdbc.driver.*; 8 9 public class ArrayDemo { 10 11 public static void passArray() throws SQLException { 12 13 Connection conn = 14 new OracleDriver().defaultConnection(); 15 16 17 StructDescriptor itemDescriptor = 18 StructDescriptor.createDescriptor("IDS",conn); 19 20 Object[] itemAtributes = new Object[] {new Integer(1), 21 new Integer(2), 22 new Integer(3)}; 23 STRUCT itemObject1 = new STRUCT(itemDescriptor,conn,itemAtributes); 24 25 itemAtributes = new Object[] {new Integer(4), 26 new Integer(5), 27 new Integer(6)}; 28 STRUCT itemObject2 = new STRUCT(itemDescriptor,conn,itemAtributes); 29 30 STRUCT[] idsArray = {itemObject1,itemObject2}; 31 32 ArrayDescriptor descriptor = 33 ArrayDescriptor.createDescriptor( "IDS_TABLE", conn ); 34 35 ARRAY array_to_pass = 36 new ARRAY( descriptor, conn, idsArray ); 37 38 OraclePreparedStatement ps = 39 (OraclePreparedStatement)conn.prepareStatement 40 ( "begin getInfo(:x); end;" ); 41 42 ps.setARRAY( 1, array_to_pass ); 43 ps.execute(); 44 45 } 46 } 47 / Java created 

Chiamiamolo:

 SQL> CREATE OR REPLACE 2 PROCEDURE show_java_calling_plsql 3 AS LANGUAGE JAVA 4 NAME 'ArrayDemo.passArray()'; 5 / Procedure created SQL> exec show_java_calling_plsql ; 1,2,3 4,5,6 PL/SQL procedure successfully completed 

Questo è un buon esempio. se vedi java.sql.SQLException: pattern name non valido: still. Controlla l’ambito del tipo che hai dichiarato in Oracle. Sto usando Oracle 11g e ho dovuto dichiarare sia Object of String Array che la Table of Objects del mio tipo a livello di schema. Ho trascorso circa 3 ore e ho trovato che.

 oracle.sql.StructDescriptor docObjDescriptor = StructDescriptor.createDescriptor("SSIADM.DOCUMENT_OBJECT",conn); String[] strArray = new String[] {"doc1","file1"}; oracle.sql.STRUCT DocObject1 = new STRUCT(docObjDescriptor,conn,strArray); strArray = new String[] {"doc2","file2"}; oracle.sql.STRUCT DocObject2 = new STRUCT(docObjDescriptor,conn,strArray); oracle.sql.STRUCT[] docObjArray = {DocObject1,DocObject2}; arrDesc = ArrayDescriptor.createDescriptor("DOCUMENT_TABLE", conn); oracle.sql.ARRAY array = new ARRAY(arrDesc, conn, docObjArray); 

La soluzione che ho usato consente a Spring di analizzare l’object invece di dover creare manualmente gli array STRUCT. Sfortunatamente, non è ancora indipendente dall’ambiente.

DAO di stored procedure:

 package ****.dao.storedProcedures; import java.sql.Array; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Types; import java.util.Date; import java.util.HashMap; import java.util.Map; import org.apache.commons.lang3.Validate; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.springframework.beans.factory.InitializingBean; import org.springframework.jdbc.core.SqlOutParameter; import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.core.SqlTypeValue; import org.springframework.jdbc.object.StoredProcedure; import ****.persistent.ComplexTypeObj; import ****.persistent.InnerType; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; public class SaveStoredProc extends StoredProcedure implements InitializingBean { public static final String IT_COMPLEX_TYPE = "it_complex_type"; public SaveStoredProc() { } @Override public void afterPropertiesSet() { Validate.notNull(getJdbcTemplate()); super.setFunction(true); super.declareParameter(new SqlOutParameter(RESULT, Types.NUMERIC)); super.declareParameter(new SqlParameter(IT_COMPLEX_TYPE, Types.OTHER, ComplexTypeObj.ORACLE_OBJECT_NAME)); compile(); } public long execute(final ComplexTypeObj complexTypeObj) { Map inParameters = new HashMap(); inParameters.put(IT_COMPLEX_TYPE, new ComplexSqlTypeValue(complexTypeObj)); @SuppressWarnings("unchecked") Map resp = super.execute(inParameters); return ((Number)resp.get(RESULT)).longValue(); } private static final class ComplexSqlTypeValue implements SqlTypeValue { private final Log logger = LogFactory.getLog(getClass()); private final ComplexTypeObj complexTypeObj; public ComplexSqlTypeValue(ComplexTypeObj complexTypeObj) { this.complexTypeObj = complexTypeObj; } @Override public void setTypeValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName) throws SQLException { Connection conn = ps.getConnection(); try { conn = conn.unwrap(oracle.jdbc.OracleConnection.class); } catch (Exception e) { logger.debug("Could not unrap connection"); } Map> typeMap = conn.getTypeMap(); typeMap.put(typeName, ComplexTypeObj.class); //The name of the outer object type. typeMap.put(InnerType.ORACLE_OBJECT_NAME, InnerType.class); //The name of the inner object type. ArrayDescriptor des = ArrayDescriptor.createDescriptor(InnerType.ORACLE_LIST_NAME, conn); //The name of the inner list type. Array objArray = new ARRAY(des, conn, complexTypeObj.getInnerList().toArray()); complexTypeObj.setInnerArray(objArray); ps.setObject(paramIndex, complexTypeObj); } } } 

Tipo esterno:

 import java.sql.*; import java.util.*; public class OuterType extends BaseSQLData implements SQLData { public static final String ORACLE_OBJECT_NAME = "T_OUTER_TYPE"; private List innerList; private Array innerArray; public OuterType() { this.innerList = new ArrayList(); } public String getSQLTypeName() throws SQLException { return ORACLE_OBJECT_NAME; } @Override public void writeSQL(SQLOutput stream) throws SQLException { stream.writeArray(innerArray); } 

Tipo interno:

 public final class InnerType extends BaseSQLData { public static final String ORACLE_OBJECT_NAME = "T_INNER_TYPE"; public static final String ORACLE_LIST_NAME = "T_INNER_TYPE_LIST"; private String valueA; private Long valueB = 0; public String getSQLTypeName() throws SQLException { return ORACLE_OBJECT_NAME; } @Override public void readSQL(SQLInput stream, String typeName) throws SQLException { throw new UnsupportedOperationException("This class doesn't support read opperations."); } @Override public void writeSQL(SQLOutput stream) throws SQLException { stream.writeString(valueA); stream.writeBigDecimal(valueB == null ? null : new BigDecimal(valueB.toString())); } 

Se stai usando Spring , potresti voler dare un’occhiata alle estensioni JDBC di Spring Data , che fornisce un tipo SqlArrayValue .

Capitolo 7.2.1 L’impostazione dei valori ARRAY utilizzando SqlArrayValue per un parametro IN spiega come chiamare le procedure con i parametri dell’array.