Passing PL/SQL Table As Parameter.
Hi,
I want to call the procedure returning PL/SQL table from java. What is the relevant datatype that can be used in java?
Following is the code for the PL/SQL table procedure created -
CREATE OR REPLACE PACKAGE test_plsqltable AS
TYPE EmpTabTyp IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER;
PROCEDURE test_proc(t_no IN NUMBER, emp_tab OUT EmpTabTyp);
END test_plsqltable;
/
CREATE OR REPLACE PACKAGE BODY test_plsqltable AS
PROCEDURE test_proc(t_no IN NUMBER, emp_tab OUT EmpTabTyp) IS
BEGIN
SELECT empno BULK COLLECT INTO emp_tab FROM emp WHERE deptno = t_no;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END test_proc;
END test_plsqltable;
/
Regards.
Software/Hardware used:
Software/Hardware used:
ASKED:
August 30, 2005 3:08 AM
UPDATED:
September 15, 2005 12:56 AM
Answer Wiki:
If you declare the array outside the Package with the "create type" command and still have the benfits of the BULK COLLECT. You can indeed pass the java a REFCURSOR which would look like a simple returnset to them. Just use a TABLE(CAST( construct in the FROM clause.
MrO
To see all answers submitted to the Answer Wiki: View Answer History.
Hi,
We had the specific requirement to use PLSQL table instead of using refcursor. I had logged in this as a TAR in metalink, they have provided a solution, which is currently working for oracle thick drivers. Will update you once this is resolved completely.
Regards,
Rohit.
Hi,
We had the specific requirement to use PLSQL table instead of using refcursor. I had logged in this as a TAR in metalink, they have provided a solution, which is currently working for oracle thick drivers. Will update you once this is resolved completely.
Regards,
Rohit.