Matt Quiros Matt Quiros - 24 days ago 6
Java Question

how to reference an oracle pl/sql out parameter of table%rowtype as an object in java

In Oracle 11g I have a table

survey
with properties
id
,
title
, and
message
. The stored procedure below gets a single record based on a provided ID and stores it in the provided OUT parameter.

create or replace procedure get_survey(arg_id number, obj_survey out survey%rowtype) is
begin
select * into obj_survey from survey where id = arg_id;
end get_survey;


I need to call this stored procedure from Java and I know I can begin with a
CallableStatement
, but how do I pass the OUT parameter from Java considering it has to be of type
survey%rowtype
? I know I can use a cursor instead but I think that's not good practice since I'm retrieveing only one row. Also, I need to create a Survey object (POJO) from the OUT parameter and return it from the method. Here's how far I've currently gotten in the code:

public Survey getSurvey(int id) throws SQLException {
CallableStatement stmt = conn.prepareCall("begin get_survey(?, ?); end;");
stmt.setInt(1, id);
stmt.registerOutParameter(2, OracleTypes.[okay what do i put here?]); // not sure about this line either
stmt.execute();

// get the out parameter, convert it to a Survey object type and return it

}

Answer

"rowtype" here is an Oracle PL/SQL-specific type, I don't think it would be supported by JDBC. A quick search of the oracle forums (google "jdbc rowtype site:oracle.com") suggests the same. You're probably better off returning a cursor, or just execute the SQL from JDBC directly.