If you want to use the user defined types which are created inside the package you got get the latest Oracle drivers.
Orcale 8.1.7 with OCI has only support for retrieving the user defined types inside the package. You can get more info in the oracel documentation.
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: Tuesday, August 21, 2001 2:02 PM
> To: Orion-Interest
> 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;
> /
>
>
