Link to home
Start Free TrialLog in
Avatar of qader99
qader99Flag for India

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).prcntFnlPnsn  := 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.getTrnsfrInDtls(?,?,?,?,?,?,?) )");
     out.println("hello1");
                                   
          stmt.setString(1,"businessgroup");
          stmt.setString(2,"user");
          stmt.setString(3,"password");
          stmt.setString(4,"emp_number");
          stmt.registerOutParameter(5,Types.ARRAY);
          stmt.registerOutParameter(6,Types.INTEGER);
     
     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
Avatar of Krolim
Krolim

What is the type definition of ERRTBL?
Avatar of qader99

ASKER

errtabl is also a PLSQL table then return errors if the procedure fails.
ASKER CERTIFIED SOLUTION
Avatar of Krolim
Krolim

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of girionis
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