Passing REF Cursor from Java to PLSQL as IN parameter [message #91535] |
Thu, 24 October 2002 06:24 |
Vaishali Ovalekar
Messages: 1 Registered: October 2002
|
Junior Member |
|
|
Hi,
I am able to pass REF Cursor from PLSQL to Java using
cstmt.registerOutParameter(2,OracleTypes.CURSOR);
Similarly Can you please advice how to pass multidimensional Array from Java to PLSQL as IN parameter where it is defined as REF CURSOR .
Thanks & Regards
Vaishali Ovalekar
Software Engineer
Patni Computer Systems Limited
|
|
|
Re: Passing REF Cursor from Java to PLSQL as IN parameter [message #91536 is a reply to message #91535] |
Thu, 24 October 2002 07:24 |
|
Mahesh Rajendran
Messages: 10708 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
I am not into JAVA and i have no idea abut it.
but this code snipped may help you...
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).
SQL> create or replace
2 and compile java source named "ArrayDemo"
3 as
4 import java.io.*;
5 import java.sql.*;
6 import oracle.sql.*;
7 import oracle.jdbc.driver.*;
8
9 public class ArrayDemo
10 {
11 public static void passArray() throws SQLException
12 {
13 Connection conn =
14 new OracleDriver().defaultConnection();
15
16 int intArray[[]] = { 1,2,3,4,5,6 };
17
18 ArrayDescriptor descriptor =
19 ArrayDescriptor.createDescriptor( "NUM_ARRAY", conn );
20
21 ARRAY array_to_pass =
22 new ARRAY( descriptor, conn, intArray );
23
24 OraclePreparedStatement ps =
25 (OraclePreparedStatement)conn.prepareStatement
26 ( "begin give_me_an_array(:x); end;" );
27
28 ps.setARRAY( 1, array_to_pass );
29
30 ps.execute();
31
32 }
33
34 }
35 /
Java created.
SQL>
SQL>
SQL> create or replace type NUM_ARRAY as table of number;
2 /
Type created.
SQL>
SQL> 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 /
Procedure created.
SQL>
SQL> create or replace
2 procedure show_java_calling_plsql
3 as language java
4 name 'ArrayDemo.passArray()';
5 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> exec show_java_calling_plsql
1
2
3
4
5
6
PL/SQL procedure successfully completed.
|
|
|