java - procedimientos - Llamando al procedimiento PL/SQL con SYS_REFCURSOR como parámetro IN usando JDBC

procedimientos y funciones oracle pl sql

Question

Estoy tratando de entender cómo puedo llamar a un procedimiento PL / SQL que toma un parámetro SYS_REFCURSOR como IN .

Considere el siguiente procedimiento PL / SQL:

print_cursor_contents(myCursor SYS_REFCURSOR , row_count OUT NUMBER);

En el momento del valor de enlace al parámetro IN, ¿qué método setXXX debo usar?

Para mí, una clase Java con campos de registro de cursor individuales, como miembros y una matriz de instancias de esta clase, parece la forma correcta de representar un CURSOR plsql. Obtengo una SQLException cuando hago esto:

Usé el siguiente método de configuración

         callStmt.setObject(1, curRec);

Aquí está la excepción que recibí por usar la declaración anterior:

Exception occured in the database
Exception message: Invalid column type
java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8921)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8396)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9176)
    at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:5024)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:234)
    at com.rolta.HrManager.printMaxSalAllDept(HrManager.java:1022)
    at com.rolta.HrManager.main(HrManager.java:1116)
Database error code: 17004

Answer 1

Para mí, una clase Java con campos de registro de cursor individuales, como miembros y una matriz de instancias de esta clase, parece la forma correcta de representar un CURSOR plsql.

Estoy en desacuerdo.

Si tiene una función o procedimiento almacenado que devuelve un cursor ref o tiene un cursor ref como parámetro OUT , el cursor ref sale de JDBC como ResultSet . Entonces, si fuera posible llamar a un procedimiento almacenado con un parámetro SYS_REFCURSOR , sospecharía que un ResultSet sería lo que necesitaría pasar.

De hecho, mis sospechas están confirmadas. Si echa un vistazo a la extensión de Oracle a CallableStatement , OracleCallableStatement , hereda un setCursor(int, ResultSet) de su superinterfaz OraclePreparedStatement . Por lo tanto, podría convertir el CallableStatement a OracleCallableStatement , llamar al método setCursor y listo.

Excepto que este enfoque en realidad no funciona.

Si intentas llamar a setCursor en OracleCallableStatement , obtendrás una excepción java.sql.SQLException: Unsupported feature .

Puede intentar llamar a setObject con un ResultSet , pero solo obtendrá otra java.sql.SQLException: Invalid column type excepción de java.sql.SQLException: Invalid column type setObject .

Aquí hay una clase de prueba que puede ejecutar para verificar cualquier caso. Llama a un procedimiento almacenado para obtener un cursor de referencia (y por lo tanto un ResultSet ) y luego intenta pasarlo al otro:

import java.sql.*;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleCallableStatement;

public class JavaRefCursorTest {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:XE", "user", "password");

        try (CallableStatement cstmt1 = conn.prepareCall(
                "{ call java_ref_curs_test.get_ref_cursor(?)}")) {
            cstmt1.registerOutParameter(1, OracleTypes.CURSOR);
            cstmt1.execute();

            try (ResultSet rSet = (ResultSet)cstmt1.getObject(1)) {
                try (CallableStatement cstmt2 = conn.prepareCall(
                        "{ call java_ref_curs_test.print_refcursor(?)}")) {

                    // Uncomment the next line to call setCursor:
                    // ((OracleCallableStatement)cstmt2).setCursor(1, rSet); 

                    // Uncomment the next line to call setObject:
                    // cstmt2.setObject(1, rSet);

                    cstmt2.execute();
                }
            }
        }
    }
}

(Los dos procedimientos en java_ref_curs_test toman un solo parámetro SYS_REFCURSOR : get_ref_cursor devuelve un cursor de referencia e print_refcursor toma uno como parámetro pero no hace nada con él).

Entonces, ¿qué método setXXX deberías usar? Yo diría que ninguno de ellos. Lo que estás pidiendo no es posible directamente.

Todavía es posible llamar a este procedimiento, pero deberá crear el cursor de ref en PL / SQL, no en Java, y luego pasarlo a su procedimiento.

Por ejemplo, podría usar el siguiente bloque PL / SQL para llamar a los dos procedimientos utilizados en el ejemplo anterior:

DECLARE
   l_curs   SYS_REFCURSOR;
BEGIN
   java_ref_curs_test.get_ref_cursor(l_curs);
   java_ref_curs_test.print_refcursor(l_curs); 
END;

Puede ejecutar esto fácilmente desde JDBC: póngalo en una cadena y páselo a Statement.executeUpdate() .