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;
/
