|
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-------
Date: Wednesday, August
22, 2001 08:05:05 AM
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;
/
|
