Passing PL/SQL Table As Parameter.

Tags:
Database
DB2
Development
Oracle
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Techanalyst10
    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.
    0 pointsBadges:
    report
  • Techanalyst10
    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.
    0 pointsBadges:
    report
  • Techanalyst10
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/passing-plsql-table-as-parameter/ (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following