| 
		
			| 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 MemberAccount 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. 
 |  
	|  |  |