Error while retrieving data from an ARRAY resultset
0 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Sathya_Sekar
Posted On:   Wednesday, February 13, 2002 06:23 AM

Yesterday, I had enquired about how to retrieve data from a PL/SQL table. Well, I managed to find a way to do that - by using the ARRAY object of Oracle. Today, I am facing an issue in getting the values from the ARRAY's resultset object :-( . Here is ho wI have gone about it : The Oracle Package I created a table type called "PlSqlTable": CREATE OR REPLACE TYPE PlSqlTable IS TABLE OF VARCHAR2(20); I defined this as the out parameter for my procedure : PROCEDURE testSQL ( arrayOutID OUT PlSqlTable ); Then po   More>>


Yesterday, I had enquired about how to retrieve data from a PL/SQL table. Well, I managed to find a way to do that - by using the ARRAY object of Oracle.



Today, I am facing an issue in getting the values from the ARRAY's resultset object :-( . Here is ho wI have gone about it :



The Oracle Package


I created a table type called "PlSqlTable":



CREATE OR REPLACE TYPE PlSqlTable IS TABLE OF VARCHAR2(20);




I defined this as the out parameter for my procedure :



PROCEDURE testSQL

(

arrayOutID OUT PlSqlTable

);




Then populated the object :



arrayOutID := PlSqlTable();

arrayOutID.extend(4);

arrayOutID(1):= 'Hello';

arrayOutID(2) := 'Test';

arrayOutID(3) := 'Ora';




The procedure executes fine - all debug statements are printed right till the end of execution.




The Java class


Here is how I have defined the parameters :




OracleCallableStatement stmnt = (OracleCallableStatement)connection.prepareCall("begin testSQL(?);end;");


stmnt.registerOutParameter(2,OracleTypes.ARRAY,"PLSQLTABLE");

System.out.println("Executing..");

stmnt.execute();

System.out.println("Executed..");

ARRAY outArray = stmnt.getARRAY(1);

System.out.println("Got array");

ResultSet rset = outArray.getResultSet();

System.out.println("Got Resultset..");

int i = 1;

while(rset.next()){

System.out.println("VALUE : " + rset.getString(i));

i = i+1;

}


..


On execution, the debug messages display :




Executing..

Executed..

Got array

Got Resultset..

VALUE : 1

VALUE : Test

ERROR : java.sql.SQLException: Invalid column index




But I have populated upto 3 values in th e procedure. Then why this error ?



PLLLEEEASE help me out on this.


Thanks,
Sathya

   <<Less
About | Sitemap | Contact