Matt Quiros Matt Quiros - 1 year ago 81
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

with properties
, and
. 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
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
, but how do I pass the OUT parameter from Java considering it has to be of type
? 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

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


Answer Source

"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") suggests the same. You're probably better off returning a cursor, or just execute the SQL from JDBC directly.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download