Home » Developer & Programmer » JDeveloper, Java & XML » Passing REF Cursor from Java to PLSQL as IN parameter
Passing REF Cursor from Java to PLSQL as IN parameter [message #91535] Thu, 24 October 2002 06:24 Go to next message
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 Go to previous message
Mahesh Rajendran
Messages: 10707
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. 

Previous Topic: JDBC Ref Cursor, Error message: Type Unknown
Next Topic: Java's answer to NCHAR/ NVARCHAR?
Goto Forum:
  


Current Time: Thu Mar 28 08:26:44 CDT 2024