JDBC, Stored Procedure and Rowtype

[Wai Iu]

I use jdbc to connect to Oracle 8.1.5 database. In my program,
I have a sentence:
CallableStatement cstmta=conna.prepareCall("begin selectall(?); end;");
It use a callablestatement to call a stored procedure-selectall.
The procedure selectall is:
create or replace procedure
selectall (allrow OUT employee_types%ROWTYPE)
as
begin select * into allrow from employee_types;
employee_types is a table in database.
My question are:
1)how could I associate datatype with the OUT parameter of the stored procedure? What will be xxxx in
"cstmta.registerOutParameter(1,xxxx);"?
2)how could I retrieve a Resultset from the CallableStatement?
Thanks!
Wai

[Carl Trusiak]

You can try
cstmta.registerOutParameter(1,java.sql.Types.OTHER);
....
ResultSet rs = (ResultSet)cstmta.getObject(1);
Hope this helps.

[parthasarathy madhira]

Hi...I used the same
and that doesnt work any suggestions on the same.
Thanks
Partha

[Anonymous]

Dear Madhira.
I've just crawled through a page that answers your (and also my) problem with Procedures that returns//accept rowTypes.
I attach you the part that worths:
(taken from Akadia)

Stored Function with Record Set
The PL/SQL package java_demo defines the procedure list_emp(), using the IN parameter p_job and the result parameter as ResultSet, analogous to the attributes of the table EMP (schema scott/tiger). The ResultSet contains all the employees matching the defined job.

  
    ...
   // Prepare a PL/SQL call
    // FUNCTION list_emp(p_job VARCHAR2) RETURN myreftype;
    String strQuery = "{ ? = call java_demo.list_emp(?)}";
    CallableStatement cstmt = dbConn.prepareCall(strQuery);
    // Select all the SALESMAN person
    // Declare that the first ? is a return value
    // of type OBJECT (Cursor Ref.)
    cstmt.registerOutParameter(1, OracleTypes.CURSOR);
    cstmt.setString(2, "SALESMAN");
    cstmt.execute();
    ResultSet rset = (ResultSet)cstmt.getObject(1);
    // Loop the cursor
    while (rset.next()) {
      System.out.println(rset.getString("ename"));
    }
    ...

This example shows the easy and smart way how to open and to use a cursor defined in
the PL/SQL package. Important in this case is the definition of the public cursor type:
TYPE myreftype IS REF CURSOR RETURN emp%ROWTYPE;
FUNCTION list_emp(p_job VARCHAR2) RETURN myreftype;
[ December 13, 2002: Message edited by: Angel Lacal L�pez ]
[ December 13, 2002: Message edited by: Angel Lacal L�pez ]

[Rickson gracie]

But why wouldn't the example work where Types.other and casting it as a resultset using (ResultSet) getObject(1) work??? Do you HAVE to use OracleTypes.CURSOR? Thanks

[Rashid Darvesh]

is there a possibility to return only first three rows in the cursor. Looks like in the above example the entire cursor is returned.