qader99
asked on
Handle PLSQL table through java
I have a following plsql procedure
************************
PROCEDURE getTrnsfrInDtls(
BusinessGroup IN VARCHAR2 ,
UserID IN VARCHAR2 ,
Password IN VARCHAR2,
p_in_empNmbr IN number,
trnsfr_info OUT TRNSFRLIST,
return_status OUT NUMBER,
error_info OUT ERRTBL) IS
BEGIN
logon(businessgroup, userid, password, 'Y', 'BASE', return_status, error_info);
trnsfr_info(1).schemeName := 'Cost Benefit';
trnsfr_info(1).dtrcvd := to_date('12-SEP-1999','DD- MM-YYYY');
trnsfr_info(1).amount := 2345;
trnsfr_info(1).addedMnths := 10;
trnsfr_info(1).accRate := 11;
trnsfr_info(1).prcntFnlPns n := 12;
trnsfr_info(1).sal := 2300;
trnsfr_info(1).protected := 3;
-- please add further for testing the multiple returns
error_info(1).origin := 'Get TransferIn Details';
error_info(1).return_code := '0';
error_info(1).context := 'GetTrnsfrInDtls';
error_info(1).message_text := 'Transfer In Details Retrieved Successfully';
logoff(businessgroup, userid, password, 'Y', trlog, return_status, error_info);
END;
**********************
As you can see the procedure returns a plsql table
Now I want to call this procedure through a java file using callable statement..
I have tried using this
*************
.......some code
CallableStatement stmt = null;
stmt = (CallableStatement) conn.prepareCall("(call sislwrapper.getTrnsfrInDtl s(?,?,?,?, ?,?,?) )");
out.println("hello1");
stmt.setString(1,"business group");
stmt.setString(2,"user");
stmt.setString(3,"password ");
stmt.setString(4,"emp_numb er");
stmt.registerOutParameter( 5,Types.AR RAY);
stmt.registerOutParameter( 6,Types.IN TEGER);
stmt.registerOutParameter( 7,ARRAY);
....rest of the code
*****
using Types.ARRAY for handling plsql table gives an sql exception I have tried using Types.OTHER also but then it gives an "Invalid Column name" exception
Please help me how to go about for above
Thanks
Qader
************************
PROCEDURE getTrnsfrInDtls(
BusinessGroup IN VARCHAR2 ,
UserID IN VARCHAR2 ,
Password IN VARCHAR2,
p_in_empNmbr IN number,
trnsfr_info OUT TRNSFRLIST,
return_status OUT NUMBER,
error_info OUT ERRTBL) IS
BEGIN
logon(businessgroup, userid, password, 'Y', 'BASE', return_status, error_info);
trnsfr_info(1).schemeName := 'Cost Benefit';
trnsfr_info(1).dtrcvd := to_date('12-SEP-1999','DD-
trnsfr_info(1).amount := 2345;
trnsfr_info(1).addedMnths := 10;
trnsfr_info(1).accRate := 11;
trnsfr_info(1).prcntFnlPns
trnsfr_info(1).sal := 2300;
trnsfr_info(1).protected := 3;
-- please add further for testing the multiple returns
error_info(1).origin := 'Get TransferIn Details';
error_info(1).return_code := '0';
error_info(1).context := 'GetTrnsfrInDtls';
error_info(1).message_text
logoff(businessgroup, userid, password, 'Y', trlog, return_status, error_info);
END;
**********************
As you can see the procedure returns a plsql table
Now I want to call this procedure through a java file using callable statement..
I have tried using this
*************
.......some code
CallableStatement stmt = null;
stmt = (CallableStatement) conn.prepareCall("(call sislwrapper.getTrnsfrInDtl
out.println("hello1");
stmt.setString(1,"business
stmt.setString(2,"user");
stmt.setString(3,"password
stmt.setString(4,"emp_numb
stmt.registerOutParameter(
stmt.registerOutParameter(
stmt.registerOutParameter(
....rest of the code
*****
using Types.ARRAY for handling plsql table gives an sql exception I have tried using Types.OTHER also but then it gives an "Invalid Column name" exception
Please help me how to go about for above
Thanks
Qader
What is the type definition of ERRTBL?
ASKER
errtabl is also a PLSQL table then return errors if the procedure fails.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
- points to Krolim
Please leave any comments here within the
next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !
girionis
Cleanup Volunteer
I will leave a recommendation in the Cleanup topic area that this question is:
- points to Krolim
Please leave any comments here within the
next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !
girionis
Cleanup Volunteer