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
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.
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 ]