salida - ¿Cómo llamar al procedimiento almacenado de Oracle que incluye el tipo definido por el usuario en Java?

procedimiento almacenado oracle select

Question

En Oracle DB:

Tengo el siguiente procedimiento almacenado:

procedure getInfo ( p_ids IN IDS_TABLE, p_details OUT cursor )

Tipo IDS_TABLE es:

create or replace type IDS_TABLE as table of IDS    

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

¿Cómo puedo llamar a getInfo en Java?

Answer 1

Configurar un enlace entre objetos SQL de Oracle y objetos Java manualmente no es una tarea trivial. En particular, las matrices (o tablas anidadas) de objetos definidos por el usuario son más complejas de pasar de java a Oracle que las matrices de tipos de datos estándar. En otras palabras, es más fácil llamar a un procedimiento con firma:

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

que un procedimiento cuya firma es:

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

Puede escribir un envoltorio alrededor de su procedimiento para transformar el segundo caso en el primer caso.

Dicho esto, no es imposible, por mucho, mapear su procedimiento. El siguiente ejemplo está inspirado en gran medida por una publicación de Tom Kyte . Tom describe cómo mapear una TABLE OF NUMBER usando oracle.sql.ARRAY . En su caso, también tendremos que usar oracle.sql.STRUCT para mapear el objeto IDS SQL.

También puede navegar por el documento Oracle JDBC, en particular, el capítulo Trabajar con tipos de objetos de Oracle .

Primero es una configuración similar a la tuya:

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

Este es el procedimiento de 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

Llamémoslo:

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

Answer 2

Este es un buen ejemplo. si ve java.sql.SQLException: patrón de nombre no válido: todavía. Compruebe el alcance del tipo que declaró en Oracle. Estoy usando Oracle 11g y tuve que declarar ambos Object of String Array y Table of Objects de mi tipo en el nivel de esquema. Pasé unas 3 horas y encontré eso.

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);

Answer 3

La solución que utilicé permite a Spring analizar el objeto en lugar de tener que crear manualmente las matrices STRUCT. Desafortunadamente, todavía no es independiente del entorno.

Stored Proc DAO:

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<String, Object> inParameters = new HashMap<String, Object>();
        inParameters.put(IT_COMPLEX_TYPE, new ComplexSqlTypeValue(complexTypeObj));

        @SuppressWarnings("unchecked")
        Map<String, Object> 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<String, Class<?>> 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 externo:

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<InnerType> innerList;
    private Array innerArray;

    public OuterType() {
        this.innerList = new ArrayList<InnerType>();
    }

    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()));
    }

Answer 4

Si está utilizando Spring , le SqlArrayValue que consulte Spring Data JDBC Extensions , que proporciona un tipo SqlArrayValue .

El Capítulo 7.2.1 Configuración de valores ARRAY utilizando SqlArrayValue para un parámetro IN explica cómo llamar procedimientos con parámetros de matriz.