This week's book giveaway is in the Mac OS forum. We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line! See this thread for details.
Hi All I have a stored procedure : SP_test() in the oracle database. I have an ArrayList in java. i want to send this list as an input parameter to this stored procedure....Any ideas how can this be done on java and oracle side? [ November 28, 2007: Message edited by: Nischal Tanna ]
http://download-east.oracle.com/docs/cd/A81042_01/DOC/java.816/a81354/oraarr3.htm#1056648 Here is a quick and dirty example showing PLSQL calling Java calling PLSQL and passing an array from Java to PLSQL. The trick is to use a SQL table type -- not a PLSQL type (eg: create the type OUTSIDE of plsql -- that way Java can in fact bind to it). Java cannot bind to PLSQL table types (eg: like owa_util.ident_arr was -- that was a plsql table type -- not a SQL type).
ops$tkyte@DEV816> ops$tkyte@DEV816> ops$tkyte@DEV816> create or replace type NUM_ARRAY as table of number; 2 /
ops$tkyte@DEV816> ops$tkyte@DEV816> create or replace 2 procedure give_me_an_array( p_array in num_array ) 3 as 4 begin 5 for i in 1 .. p_array.count 6 loop 7 dbms_output.put_line( p_array(i) ); 8 end loop; 9 end; 10 /
ops$tkyte@DEV816> ops$tkyte@DEV816> create or replace 2 procedure show_java_calling_plsql 3 as language java 4 name 'ArrayDemo.passArray()'; 5 /
ops$tkyte@DEV816> ops$tkyte@DEV816> set serveroutput on ops$tkyte@DEV816> exec show_java_calling_plsql 1 2 3 4 5 6
PL/SQL procedure successfully completed.
subject: Passing multiple values to a stored procedure