Calling PL/SQL procedure with user defined record as its IN parameter using JDBC

Question

I am trying to call the following PL/SQL procedure that takes a user defined record type as an IN parameter.

   -- User Defined Record
   TYPE EMP_REC IS RECORD
   (
    id employees.employee_id%type,
    name employees.last_name%type,
    dept_name departments.department_name%type,
    job_title jobs.job_title%type,
    salary employees.salary%type,
    manager_id employees.employee_id%type,
    city locations.city%type,
    phone employees.phone_number%type
   );

Here is the definition of the user defined record:

  -- PURPOSE: Prints all employee information from the employee record 
  -- Example Of: PROCEDURE that takes in a parameter of RECORD type 
  PROCEDURE print_employee_all_details(empl1 emp_rec , emp_rec_string OUT VARCHAR2)

I was looking at the Oracle JDBC Documentation that indicated JDBC does not support composite types like RECORDS:

Searching the internet took me to this link

Here is the code that I tried to pass a user defined record to a PL/SQL procedure:

public String printEmployeeAllDetails() {
    Connection conn = null;
    CallableStatement callStmt = null;
    String empDetails = null;

    try {
        // Register the Jdbc Driver
        // Class.forName(JDBC_DRIVER_ORACLE);

        // Create a Database Connection
        conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);

        // Create a query string
        String callProc = "{call HR.EMP_PKG.print_employee_all_details( ? , ?) }";

        // Create a Callable Statement
        callStmt = conn.prepareCall(callProc);


        // Create descriptor for the Oracle Record type "EMP_REC" required
        StructDescriptor recDescriptor = StructDescriptor.createDescriptor("EMP_REC", conn);

        // Stage values for each field in the Oracle record in an array
        Object[] javaEmpRec = new Object[8];


        // Populate those values in the Array
        javaEmpRec[0] = 100;
        javaEmpRec[1] = "Joe Matthew";
        javaEmpRec[2] = "IT";
        javaEmpRec[3] = "Senior Consultant";
        javaEmpRec[4] = 20000;
        javaEmpRec[5] = 101;
        javaEmpRec[6] = "lombard";
        javaEmpRec[7] = "222333444";

        // Cast the java array into the oracle record type
        STRUCT oracleEmpRec = new STRUCT(recDescriptor, conn, javaEmpRec);


        // Bind Values to the IN parameter
        callStmt.setObject(1, oracleEmpRec);

        // Register OUT parameter
        callStmt.registerOutParameter(2, java.sql.Types.VARCHAR);

        // Execute the Callable Statement
        callStmt.execute();

        // Retrieve the value from the OUT parameter
        empDetails = callStmt.getString(2);
        System.out.println("Emp Details: " + empDetails);

    } catch (SQLException se) {
        System.out.println("Exception occured in the database");
        System.out.println("Exception message: " + se.getMessage());
        System.out.println("Database error code: " + se.getErrorCode());
        se.printStackTrace();
    } finally {
        // Clean up
        if (callStmt != null) {
            try {
                callStmt.close();
            } catch (SQLException se2) {
                se2.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException se2) {
                se2.printStackTrace();
            }
        }
    }

    return empDetails;
}

On running this code I get the following exception:

Exception occured in the database
Exception message: invalid name pattern: HR.EMP_REC
java.sql.SQLException: invalid name pattern: HR.EMP_REC
    at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:554)
    at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:471)
    at oracle.sql.StructDescriptor.initPickler(StructDescriptor.java:324)
    at oracle.sql.StructDescriptor.<init>(StructDescriptor.java:254)
    at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:135)
    at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:103)
Database error code: 17074
    at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:72)
    at com.rolta.HrManager.printEmployeeAllDetails(HrManager.java:1214)
    at com.rolta.HrManager.main(HrManager.java:1334)

I am using the ojdbc6.jar the very first jar under heading JDBC Thin for All Platforms for Oracle Database 11g Release 2 (11.2.0.4) JDBC Driverson this page.

I want to know if passing user defined records (as IN parameter) to a PL/SQL procedure is allowed ? Has anybody tried doing the above ?

Answer 1

Yes, it's allowed to pass user-defined datatypes as IN parameters using JDBC. But it can't be a RECORD. It must be a schema level object, e.g.

CREATE TYPE EMP_REC AS OBJECT
(
 id employees.employee_id%type,
 name employees.last_name%type,
 dept_name departments.department_name%type,
 job_title jobs.job_title%type,
 salary employees.salary%type,
 manager_id employees.employee_id%type,
 city locations.city%type,
 phone employees.phone_number%type
);

In your PL/SQL, you could change references to your record to your new object type, or you could write a quick little translator function to translate the object type to the record type if you can't change the rest of the code.