ti-enxame.com

Como chamar o procedimento armazenado do Oracle, que inclui o tipo definido pelo usuário em java?

No Oracle DB:

Eu tenho o seguinte procedimento armazenado:

procedure getInfo ( p_ids IN IDS_TABLE, p_details OUT cursor )

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

Como posso chamar o getInfo em Java?

26
xmurobi

Configurar um link entre objetos Oracle SQL e objetos Java manualmente não é uma tarefa trivial. Em particular, matrizes (ou tabelas aninhadas) de objetos definidos pelo usuário são mais complexas para passar de Java para Oracle do que matrizes de tipos de dados padrão. Em outras palavras, é mais fácil chamar um procedimento com assinatura:

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

que um procedimento cuja assinatura é:

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

Você pode escrever um wrapper em torno de seu procedimento para transformar o segundo caso no primeiro caso.


Dito isto, não é de longe impossível mapear o seu procedimento. O exemplo a seguir é amplamente inspirado em um post de Tom Kyte . Tom descreve como mapear um TABLE OF NUMBER usando Oracle.sql.ARRAY. No seu caso, também teremos que usar Oracle.sql.STRUCT para mapear o objeto SQL IDS.

Você também pode querer navegar pelo documento Oracle JDBC, em particular o capítulo Trabalhando com Tipos de Objetos do Oracle .

Primeiro é uma configuração semelhante à sua:

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 é o procedimento 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

Vamos chamá-lo:

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
35
Vincent Malgrat

Se você estiver usando Spring , você pode querer olhar para Spring Data JDBC Extensions , que fornece um tipo SqlArrayValue.

Capítulo 7.2.1 Definir valores ARRAY usando SqlArrayValue para um parâmetro IN explica como chamar procedimentos com parâmetros de matriz.

1
Arlo

A solução que usei permite que o Spring analise o objeto em vez de ter que criar manualmente os arrays STRUCT. Infelizmente, ele ainda não é independente do ambiente.

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 exterior:

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

Este é um bom exemplo. se você vir Java.sql.SQLException: padrão de nome inválido: still. Verifique o escopo do tipo que você declarou no Oracle. Estou usando o Oracle 11g e tive que declarar tanto o Object of String Array como a Table of Objects do meu tipo no nível do esquema. Passou cerca de 3 horas e descobriu isso.

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);
0
Ravi Petchimuthu