You got to have Oracle8.1.7 oci drivers to execute the following. If you install 8.1.7 you will get documentation with that. These registerIndexTableOutParameter is in OracleCallableStatement. You can find more info in the documentation.
Here is my small example.
import java.io.*;
import java.util.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class OracleTest
{
public static void main(String args[])
{
Connection con = null;
OracleCallableStatement cstmt = null;
try
{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
con = DriverManager.getConnection ("jdbc:oracle:oci8:@VMSDEV", "prism", "prism");
cstmt = (OracleCallableStatement)con.prepareCall("begin Test.get(?); end;");
int elemMaxLen = 250;
int maxLen = 100;
int elemSqlType = Types.VARCHAR;
cstmt.registerIndexTableOutParameter (1, maxLen, elemSqlType, elemMaxLen);
cstmt.execute();
String[] values = (String[])cstmt.getPlsqlIndexTable (1);
for(int i=0; i<values.length; i++)
{
System.out.println(values[i]);
}
} catch(Exception ex)
{
ex.printStackTrace(System.err);
} finally
{
if(cstmt != null) try{cstmt.close();}catch(Exception _ex){}
if(con != null) try{con.close();}catch(Exception _ex){}
}
}
}
Hope this helps you.
Kesav Kumar Kolla
Voquette Inc
650 356 3740(W)
510 889 6840(R)
Voquette....Delivering Sound Information
> -----Original Message-----
> From: Komal Kandi (Contractor) [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 23, 2001 9:23 AM
> To: Orion-Interest
> Subject: RE: Registering PL/SQL table OUT parameter in jdbc
> andretrieving ( challenging question)
>
>
>
>
> Hi Bombalaa,
> Thank Very much for your Help.
> Could You please send me example or where can i find full information.
> I was not able find registerIndexTableOutParameter method.
> (cstmt.registerIndexTableOutParameter(1,30, Types.INTEGER,0);
> cstmt.registerIndexTableOutParameter(2,30, Types.VARCHAR,0);)
>
> thanks once again,
>
> Regards,
> Komal Kandi.
>
>
>
> -----Original Message-----
> From: bombalaa-orion [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, August 21, 2001 6:59 PM
> To: Orion-Interest
> Subject: Re: Registering PL/SQL table OUT parameter in jdbc
> andretrieving ( challenging question)
>
>
>
> Hi Komal.
>
> seems that your procedure has 2 OUT Parameters ...
>
> TYPE id_tab IS TABLE OF pmnt_rsch_scn.PMNT_RSRCH_SCN_ID%TYPE
> INDEX BY BINARY_INTEGER;
>
> TYPE scn_tab IS TABLE OF pmnt_rsch_scn.SCENARIO_DESC%TYPE
> INDEX BY BINARY_INTEGER;
>
> For table of index , RegisterOutParameter does not work ...
> also if you are using thin client then also it would not work ...
>
> works for oci ...
>
> Instead try this :
>
> cstmt.registerIndexTableOutParameter(1,30, Types.INTEGER,0);
> cstmt.registerIndexTableOutParameter(2,30, Types.VARCHAR,0);
>
> and then you have to use Datum to get the values...
>
> if u still want more info then mail me ..
>
> bombalaa.
>
> -------Original Message-------
>
> From: Orion-Interest <mailto:[EMAIL PROTECTED]>
> Date: Wednesday, August 22, 2001 08:05:05 AM
> To: Orion-Interest <mailto:[EMAIL PROTECTED]>
> Subject: Registering PL/SQL table OUT parameter in jdbc and
> retrieving (
> challenging question)
>
> Hi All,
> Thanks in advance if i would be given the suggestion.I'm now
> calling a stored proceudre in Oracle from Java,where I use
> CallableStatement,but i've got to stop at the point that when i
> register the output parameter,i found no data type matches this
> nested table type within this procedure in Oracle.
>
> could you please any body have any idea.
>
> Thanks
> Komal Kandi.
>
> pls see here
>
> import java.sql.*;
> import java.util.*;
>
> public class GetTsysNoteOptions
> {
> public static void main(String args[])throws SQLException
> {
> String url="jdbc:odbc:PRA";
> try
> {
> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
> }catch(ClassNotFoundException e)
> {
> System.err.println("ClassNotFoundException thrown");
> System.err.println(e.getMessage());
> }
>
> Connection
> conn=DriverManager.getConnection(url,"pmtrsh_tmp","pmtrsh_tmp2000
> ");
> CallableStatement callst=conn.prepareCall("{call
> Call_Proc(?,?)}");
> callst.registerOutParameter(1, ???);
> callst.registerOutParameter(2, ???); // what i have to
> register i tried
> other type but it's not working
> callst.execute();
> /*
> case 1:??? --->> java.sql.Types.OTHER
> int that case iam geting these error
>
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to
> 'GET_ARCHIVE_DATE'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> case 2:??? --->> java.sql.Types.OTHER,"type"
> int that case iam geting these error
>
> ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to
> 'GET_ARCHIVE_DATE'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> or some other tries iam geting Sql type cast exception
> */
>
> System.out.println("The TSYS Variables are :");
> System.out.println(intArr[1]);
> System.out.println(strArr[1]);
> callst.close();
> }
> }
>
>
> the procedure( in a package):
> CREATE OR REPLACE PACKAGE GetTSYSNoteOpt_Pkg AS
> TYPE id_tab IS TABLE OF pmnt_rsch_scn.PMNT_RSRCH_SCN_ID%TYPE
> INDEX BY BINARY_INTEGER;
> TYPE scn_tab IS TABLE OF pmnt_rsch_scn.SCENARIO_DESC%TYPE
> INDEX BY BINARY_INTEGER;
> PROCEDURE Sp_GetTSYSNoteOptions (id out id_tab,scn out scn_tab);
> END GetTSYSNoteOpt_Pkg;
> /
>
> CREATE OR REPLACE PACKAGE BODY GETTSYSNOTEOPT_PKG AS
> cursor c1 is select
> pmnt_rsch_scn.PMNT_RSRCH_SCN_ID,pmnt_rsch_scn.SCENARIO_DESC from
> pmnt_rsch_scn;
> procedure Sp_GetTSYSNoteOptions(id out id_tab,scn out scn_tab) is
> --type rec is record
> --( l_id pmnt_rsch_scn.PMNT_RSRCH_SCN_ID%type,
> -- l_scn pmnt_rsch_scn.SCENARIO_DESC%type);
> --rec1 rec;
> begin
>
> open c1;
> for i in 1..40
> loop
> fetch c1 into id(i),scn(i);
> exit when c1%notfound;
> end loop;
> close c1;
> EXCEPTION
> WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE('An Exception has occured');
> for i in 1..40
> loop
> dbms_output.put_line(id(i));
> dbms_output.put_line(scn(i));
> end loop;
> end;
> end GetTSYSNoteOpt_Pkg;
> /
>
>
>
> _________________________________________________
> IncrediMail - Email has finally evolved -
> <http://www.incredimail.com/redir.asp?ad_id=109> Click Here
>
>
>
