How to read a pl/sql table which is an OUT parameter from a stored proc from java?

[mamata surapaneni]

Hi,
I have my code:

CURSOR C1 IS SELECT .........;
TYPE T1 is TABLE OF C1%ROWTYPE INDEX BY INTEGER;
TYPE T2 IS TABLE OF T1 INDEX BY BINARY INTEGER;

CREATE OR REPLACE PROCEDURE P1( OUT T2) IS
{}

I am trying to read T2 from my java code.
How to register this OUT parameter in java code?

[vanlalhmangaiha khiangte]

You can use java.sql.Array .. you can read more about it here

Suppose that input and output parameters IN_PHONE and OUT_PHONE in stored procedure GET_EMP_DATA are arrays that are defined like this:
CREATE TYPE PHONENUMBERS AS VARCHAR(10) ARRAY[5]
Call GET_EMP_DATA with the two parameters.

    
 
Connection con;
CallableStatement cstmt;
ResultSet rs;
java.sql.Array inPhoneData;
�
stmt = con.prepareCall("CALL GET_EMP_DATA(?,?)");
                                  // Create a CallableStatement object
cstmt.setObject (1, inPhoneData); // Set input parameter
cstmt.registerOutParameter (2, java.sql.Types.ARRAY);
                                  // Register out parameters
cstmt.executeUpdate();            // Call the stored procedure
Array outPhoneData = cstmt.getArray(2);                
                                  // Get the output parameter array
System.out.println("Parameter values from GET_EMP_DATA call: ");
String [] outPhoneNums = (String [])outPhoneData.getArray();
                          // Retrieve output data from the JDBC Array object
                          // into a Java String array
for(int i=0; i<outPhoneNums.length; i++) {
  System.out.print(outPhoneNums[i]);  
  System.out.println();   
}
 

Hope this helps ..

[Avi Abrami]

Mamata,
Please note that Vanlalhmangaiha's example uses a database type which is a VARRAY, whereas your example uses a PL/SQL table type.
As far as I know, Vanlalhmangaiha's example does not work with PL/SQL table types.

Good Luck,
Avi.