How to return a ROWTYPE in PL/SQL and retrieve it in Java?

Question

I have a PL/SQL function , that returns a ROWTYPE. I need to call this function from Java and retrieve the data ( as an array, a map, a struct, a class,whatever, I really don't care , I just need it, preferably without having to screw around the PL/SQL code, like changing the return type of the function). I have looked into multiple "solutions" and forums but I did not find the answer. I have already tried to register the out parameter as a struct and a class , none worked. This is my PL/SQL function:

  FUNCTION DAR_CLIENTE(cedula VARCHAR2) RETURN CLIENTE%ROWTYPE AS
  RET CLIENTE%ROWTYPE;
  BEGIN
    -- TAREA: Se necesita implantación para FUNCTION P_CLIENTE.DAR_CLIENTE
    SELECT * INTO RET FROM CLIENTE WHERE Persona_cedula=cedula;
    RETURN RET;
  END DAR_CLIENTE;

This is my java code:

public static void main(String args[]) throws SQLException {
    Properties info = new Properties();
    info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
    info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);
    info.put(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, "20");

    OracleDataSource ods = new OracleDataSource();
    ods.setURL(DB_URL);
    ods.setConnectionProperties(info);

    // With AutoCloseable, the connection is closed automatically.
    try (OracleConnection connection = (OracleConnection) ods.getConnection()) {
        // Get the JDBC driver name and version
        DatabaseMetaData dbmd = connection.getMetaData();
        System.out.println("Driver Name: " + dbmd.getDriverName());
        System.out.println("Driver Version: " + dbmd.getDriverVersion());
        // Print some connection properties
        System.out.println("Default Row Prefetch Value is: " + connection.getDefaultRowPrefetch());
        System.out.println("Database Username is: " + connection.getUserName());
        System.out.println();
        System.out.println(connection.getSchema());
        // Perform a database operation

        Map<String, Class<?>> myMap = new HashMap<String, Class<?>>();
        myMap.put("P09551_1_5.CLIENTE", Cliente.class);

        connection.setTypeMap(myMap);
        CallableStatement storedProc = connection
                .prepareCall("{? = call P09551_1_5.p_cliente.dar_cliente('1144102435')}");

        storedProc.registerOutParameter(1, oracle.jdbc.OracleTypes.JAVA_STRUCT);
        storedProc.execute();

    }
}

I am using ojdbc8.jar.

This information is printed to the console at the start of the program:

Driver Name: Oracle JDBC driver
Driver Version: 18.3.0.0.0

Answer 1

Using a third party: jOOQ

If using a third party library is acceptable to you, you could use jOOQ's stored procedure support, which includes support for PL/SQL package types. jOOQ's code generator will generate a class PCliente containing a method darCliente() for you, which you can call as follows:

ClienteRecord result = PCliente.darCliente(
    configuration, // This wraps your JDBC connection
    "1144102435"
);

No need to wrap your head around JDBC bindings. Everything is type safe. The ClienteRecord is the same data type as if you ran that SELECT query directly with jOOQ.

The SQL generated by jOOQ

Because ojdbc still (I think?) can't bind all PL/SQL package types, jOOQ flattens your %ROWTYPE into individual bind values, e.g. like this (actual SQL will vary, because it's trickier than that, considering all the edge cases)

DECLARE
  r CLIENTE%ROWTYPE;
BEGIN
  r := P_CLIENTE.DAR_CLIENTE(?);
  ? := r.COL1;
  ? := r.COL2;
  ? := r.COL3;
END;

Of course, instead of using jOOQ, you could generate this SQL yourself and continue binding to JDBC directly.

Disclaimer: I work for the company behind jOOQ.

Answer 2

I have resolved the issue by changing the return type to a SYS_REFCURSOR, like this:

FUNCTION DAR_CLIENTE(cedula VARCHAR2) RETURN SYS_REFCURSOR AS
  RET SYS_REFCURSOR;
  BEGIN
    -- TAREA: Se necesita implantación para FUNCTION P_CLIENTE.DAR_CLIENTE
    OPEN RET FOR
    SELECT * FROM CLIENTE WHERE Persona_cedula=cedula;
    RETURN RET;
  END DAR_CLIENTE;

Java code as follows:

public static void main(String args[]) throws SQLException {
    Properties info = new Properties();
    info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
    info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);
    info.put(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, "20");

    OracleDataSource ods = new OracleDataSource();
    ods.setURL(DB_URL);
    ods.setConnectionProperties(info);

    // With AutoCloseable, the connection is closed automatically.
    try (OracleConnection connection = (OracleConnection) ods.getConnection()) {
        // Get the JDBC driver name and version
        DatabaseMetaData dbmd = connection.getMetaData();
        System.out.println("Driver Name: " + dbmd.getDriverName());
        System.out.println("Driver Version: " + dbmd.getDriverVersion());
        // Print some connection properties
        System.out.println("Default Row Prefetch Value is: " + connection.getDefaultRowPrefetch());
        System.out.println("Database Username is: " + connection.getUserName());
        System.out.println("Schema: "+connection.getSchema());
        System.out.println();
        // Perform a database operation

        Map<String, Class<?>> myMap = new HashMap<String, Class<?>>();
        myMap.put("P09551_1_5.CLIENTE", Cliente.class);

        connection.setTypeMap(myMap);
        CallableStatement storedProc = connection
                .prepareCall("{? = call P09551_1_5.p_cliente.dar_cliente('1144102435')}");

        storedProc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
        storedProc.execute();
        ResultSet resultSet = (ResultSet) storedProc.getObject(1);
        ResultSetMetaData meta = resultSet.getMetaData();
        int columnCount = meta.getColumnCount();
        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++) {
                System.out.println(meta.getColumnLabel(i)+":"+resultSet.getObject(i).toString());
            }
            //System.out.println(resultSet.getString(1));

        }

    }
}

Getting this response on the console (I did it so all fields would be printed with their column labels, though this particular table only has one column):

Driver Name: Oracle JDBC driver
Driver Version: 18.3.0.0.0
Default Row Prefetch Value is: 20
Database Username is: P09551_1_5
Schema: P09551_1_5

PERSONA_CEDULA:1144102435

Here is the source of the solution: https://www.mkyong.com/jdbc/jdbc-callablestatement-stored-procedure-cursor-example/