llamar - procedimientos almacenados oracle

Recupere el tipo de tabla de Oracle del procedimiento almacenado usando JDBC

Question

Estoy tratando de comprender diferentes formas de obtener datos de tablas de procedimientos / funciones almacenados de Oracle usando JDBC. Las seis formas son las siguientes:

1. procedimiento que devuelve un tipo de tabla de nivel de esquema como un parámetro OUT
2. procedimiento que devuelve un tipo de tabla de nivel de paquete como un parámetro OUT
3. procedimiento que devuelve un tipo de cursor de nivel de paquete como un parámetro OUT
4. función que devuelve un tipo de tabla de nivel de esquema
5. función que devuelve un tipo de tabla de nivel de paquete
6. función que devuelve un tipo de cursor de nivel de paquete

Aquí hay algunos ejemplos en 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;

Logré llamar a 3, 4 y 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

Entonces, obviamente, estoy teniendo problemas para entender

1. Cómo recuperar los tipos de tabla a nivel de esquema y de paquete de los parámetros OUT en procedimientos almacenados
2. Cómo recuperar tipos de tablas de nivel de paquete desde funciones almacenadas

Parece que no puedo encontrar ninguna documentación sobre esto, ya que todos siempre usan cursores en lugar de tipos de tablas. Tal vez porque no es posible? Sin embargo, prefiero los tipos de tabla porque están formalmente definidos y se pueden descubrir utilizando las vistas de diccionario (al menos los tipos de tabla de nivel de esquema).

Nota: obviamente, podría escribir una función contenedora que devuelva los parámetros OUT y los tipos de tabla de nivel de paquete. Pero preferiría la solución limpia.

Answer 1

No puede acceder a los objetos PLSQL (casos 2 y 5 = objetos de nivel de paquete) desde Java, consulte "java - paso de la matriz en el procedimiento almacenado de Oracle" . Sin embargo, puede acceder a los tipos de SQL (caso 1 y 4).

Para obtener los parámetros OUT de PL / SQL a java, puede usar el método descrito en uno de los hilos de Tom Kyte usando OracleCallableStatement. Su código tendrá un paso adicional ya que está recuperando una tabla de Object en lugar de una tabla de VARCHAR.

Aquí hay una demostración usando Table of SQL Object, primero la configuración:

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

La clase real de Java (usando dbms_output.put_line para iniciar sesión porque la llamaré desde SQL, use System.out.println si se llama desde 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<elements.length;i++){
 34           Object[] element = ((STRUCT) elements[i]).getAttributes();
 35           String value = (String)element[0];
 36           log("array(" + i + ").val=" + value);
 37        }
 38     }
 39  }
 40  /
Java created

Llamémoslo:

SQL> CREATE OR REPLACE
  2  PROCEDURE show_java_calling_plsql
  3  AS LANGUAGE JAVA
  4  NAME ''ArrayDemo.getArray()'';
  5  /

Procedure created

SQL> EXEC show_java_calling_plsql;

array(0).val=a
array(1).val=b

Answer 2

También puedes usar el de abajo

public List<EmployeeBean> fetchDataFromSPForRM(String sInputDate) {

         List<EmployeeBean> employeeList = new ArrayList<EmployeeBean>();

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