Obter o tipo de tabela do Oracle a partir do procedimento armazenado usando o JDBC

Eu estou tentando entender diferentes maneiras de obter dados de tabela de procedimentos / funções armazenados do Oracle usando JDBC. As seis formas são as seguintes:

  1. procedimento retornando um tipo de tabela no nível do esquema como um parâmetro OUT
  2. procedimento retornando um tipo de tabela no nível do pacote como um parâmetro OUT
  3. procedimento retornando um tipo de cursor no nível do pacote como um parâmetro OUT
  4. Função Retornando um Tipo de Tabela no Nível do Esquema
  5. function retornando um tipo de tabela no nível do pacote
  6. function retornando um tipo de cursor no nível do pacote

Aqui estão alguns exemplos em PL / SQL:

-- schema-level table type CREATE TYPE t_type AS OBJECT (val VARCHAR(4)); CREATE TYPE t_table AS TABLE OF t_type; CREATE OR REPLACE PACKAGE t_package AS -- package level table type TYPE t_table IS TABLE OF some_table%rowtype; -- package level cursor type TYPE t_cursor IS REF CURSOR; END library_types; -- and example procedures: CREATE PROCEDURE p_1 (result OUT t_table); CREATE PROCEDURE p_2 (result OUT t_package.t_table); CREATE PROCEDURE p_3 (result OUT t_package.t_cursor); CREATE FUNCTION f_4 RETURN t_table; CREATE FUNCTION f_5 RETURN t_package.t_table; CREATE FUNCTION f_6 RETURN t_package.t_cursor; 

Eu consegui chamar 3, 4 e 6 com o JDBC:

 // Not OK: p_1 and p_2 CallableStatement call = connection.prepareCall("{ call p_1(?) }"); call.registerOutParameter(1, OracleTypes.CURSOR); call.execute(); // Raises PLS-00306. Obviously CURSOR is the wrong type // OK: p_3 CallableStatement call = connection.prepareCall("{ call p_3(?) }"); call.registerOutParameter(1, OracleTypes.CURSOR); call.execute(); ResultSet rs = (ResultSet) call.getObject(1); // Cursor results // OK: f_4 PreparedStatement stmt = connection.prepareStatement("select * from table(f_4)"); ResultSet rs = stmt.executeQuery(); // Not OK: f_5 PreparedStatement stmt = connection.prepareStatement("select * from table(f_5)"); stmt.executeQuery(); // Raises ORA-00902: Invalid data type // OK: f_6 CallableStatement call = connection.prepareCall("{ ? = call f_6 }"); call.registerOutParameter(1, OracleTypes.CURSOR); call.execute(); ResultSet rs = (ResultSet) call.getObject(1); // Cursor results 

Então, obviamente, estou tendo problemas para entender

  1. Como recuperar tipos de tabela de nível de esquema e nível de pacote dos parâmetros OUT em stored procedures
  2. Como recuperar tipos de tabela no nível de pacote de funções armazenadas

Eu não consigo encontrar nenhuma documentação sobre isso, como todo mundo sempre usa cursores em vez de tipos de tabela. Talvez porque não seja possível? Eu prefiro os tipos de tabela, porque eles são formalmente definidos e podem ser descobertos usando as visualizações de dictionary (pelo menos os tipos de tabela no nível de esquema).

Nota: obviamente, eu poderia escrever uma function wrapper retornando os parâmetros OUT e os tipos de tabela no nível do pacote. Mas eu prefiro a solução limpa.

Você não pode acessar objects PLSQL (casos 2 e 5 = objects de nível de pacote) de java, consulte “array de passagem de java no procedimento armazenado oracle” . Você pode, no entanto, acessar tipos SQL (casos 1 e 4).

Para obter os parâmetros OUT de PL / SQL para java, você pode usar o método descrito em um dos segmentos de Tom Kyte usando OracleCallableStatement. Seu código terá uma etapa adicional desde que você esteja recuperando uma tabela de Object em vez de uma tabela de VARCHAR.

Aqui está uma demonstração usando o Table of SQL Object, primeiro a configuração:

 SQL> CREATE TYPE t_type AS OBJECT (val VARCHAR(4)); 2 / Type created SQL> CREATE TYPE t_table AS TABLE OF t_type; 2 / Type created SQL> CREATE OR REPLACE PROCEDURE p_sql_type (p_out OUT t_table) IS 2 BEGIN 3 p_out := t_table(t_type('a'), t_type('b')); 4 END; 5 / Procedure created 

A class java real (usando dbms_output.put_line para log porque eu a chamarei de SQL, use System.out.println se chamado de java):

 SQL> CREATE OR REPLACE 2 AND COMPILE JAVA SOURCE NAMED "ArrayDemo" 3 as 4 import java.sql.*; 5 import oracle.sql.*; 6 import oracle.jdbc.driver.*; 7 8 public class ArrayDemo { 9 10 private static void log(String s) throws SQLException { 11 PreparedStatement ps = 12 new OracleDriver().defaultConnection().prepareStatement 13 ( "begin dbms_output.put_line(:x); end;" ); 14 ps.setString(1, s); 15 ps.execute(); 16 ps.close(); 17 } 18 19 public static void getArray() throws SQLException { 20 21 Connection conn = new OracleDriver().defaultConnection(); 22 23 OracleCallableStatement cs = 24 (OracleCallableStatement)conn.prepareCall 25 ( "begin p_sql_type(?); end;" ); 26 cs.registerOutParameter(1, OracleTypes.ARRAY, "T_TABLE"); 27 cs.execute(); 28 ARRAY array_to_pass = cs.getARRAY(1); 29 30 /*showing content*/ 31 Datum[] elements = array_to_pass.getOracleArray(); 32 33 for (int i=0;i
		      	

Você também pode usar o abaixo um

 public List fetchDataFromSPForRM(String sInputDate) { List employeeList = new ArrayList(); Connection dbCon = null; ResultSet data = null; CallableStatement cstmt = null; try { dbCon = DBUtil.getDBConnection(); String sqlQuery = "{? = call PKG_HOLD_RELEASE.FN_RM_PDD_LIST()}"; cstmt = dbCon.prepareCall(sqlQuery); cstmt.registerOutParameter(1, OracleTypes.CURSOR); cstmt.execute(); data = (ResultSet) cstmt.getObject(1); while(data.next()){ EmployeeBean employee = new EmployeeBean(); employee.setEmpID(data.getString(1)); employee.setSubBusinessUnitId((Integer)data.getObject(2)); employee.setMonthOfIncentive((Integer)data.getObject(3)); employee.setPIPStatus(data.getString(5)); employee.setInvestigationStatus(data.getString(6)); employee.setEmpStatus(data.getString(7)); employee.setPortfolioPercentage((Integer)data.getObject(8)); employee.setIncentive((Double)data.getObject(9)); employee.setTotalSysemHoldAmt((Double)data.getObject(10)); employee.setTotalManualHoldAmt((Double)data.getObject(11)); employeeList.add(employee); } } catch (SQLException e) { e.printStackTrace(); }finally{ try { if(data != null){ data.close(); data = null; } if(cstmt != null){ cstmt.close(); cstmt = null; } if(dbCon != null){ dbCon.close(); dbCon = null; } } catch (SQLException e) { e.printStackTrace(); } } return employeeList; }