Recupera il tipo di tabella Oracle dalla stored procedure utilizzando JDBC

Sto cercando di capire i diversi modi di ottenere i dati della tabella dalle procedure / funzioni memorizzate Oracle usando JDBC. I sei modi sono i seguenti:

  1. procedura che restituisce un tipo di tabella a livello di schema come parametro OUT
  2. procedura che restituisce un tipo di tabella a livello di pacchetto come parametro OUT
  3. procedura che restituisce un tipo di cursore a livello di pacchetto come parametro OUT
  4. funzione che restituisce un tipo di tabella a livello di schema
  5. funzione che restituisce un tipo di tabella a livello di pacchetto
  6. funzione che restituisce un tipo di cursore a livello di pacchetto

Ecco alcuni esempi in 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; 

Sono riuscito a chiamare 3, 4 e 6 con 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 

Quindi, ovviamente, ho problemi a capire

  1. Come recuperare i tipi di tabella a livello di schema e a livello di pacchetto dai parametri OUT nelle stored procedure
  2. Come recuperare i tipi di tabella a livello di pacchetto dalle funzioni memorizzate

Non riesco a trovare alcuna documentazione su questo, poiché tutti usano sempre i cursori invece dei tipi di tabella. Forse perché non è ansible? Preferisco i tipi di tabella, perché sono definiti formalmente e possono essere scoperti usando le viste del dizionario (almeno i tipi di tabella a livello di schema).

Nota: ovviamente, potrei scrivere una funzione wrapper restituendo i parametri OUT e i tipi di tabella a livello di pacchetto. Ma preferirei la soluzione pulita.

Non è ansible accedere agli oggetti PLSQL (casi 2 e 5 = oggetti a livello di pacchetto) da java, vedere “array java – passing in oracle stored procedure” . È comunque ansible accedere ai tipi SQL (caso 1 e 4).

Per ottenere i parametri OUT da PL / SQL a java, è ansible utilizzare il metodo descritto in una delle thread di Tom Kyte utilizzando OracleCallableStatement. Il tuo codice avrà un ulteriore passaggio dal momento che stai recuperando una tabella di oggetti invece di una tabella di VARCHAR.

Ecco una demo che utilizza Table of SQL Object, prima l’installazione:

 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 

L’effettiva class java (usando dbms_output.put_line per accedere perché chiamerò da SQL, usa System.out.println se chiamata da 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
		      	

Puoi anche usare quello sotto

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