Chiseled Chiseled - 19 days ago 11
Java Question

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

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 :

enter image description here

Searching the internet took me to this link

Here is the code that I tried inorder 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 Drivers
on 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

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.

Comments