Solved

Handle PLSQL table through java

Posted on 2002-05-29
5
426 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:qader99
  • 2
5 Comments
 

Expert Comment

by:Krolim
Comment Utility
What is the type definition of ERRTBL?
0
 

Author Comment

by:qader99
Comment Utility
errtabl is also a PLSQL table then return errors if the procedure fails.
0
 

Accepted Solution

by:
Krolim earned 100 total points
Comment Utility
This is a quote from the Oracle 9i documentation (Oracle9i - JDBC Developer’s Guide and Reference(a90211).pdf):

PL/SQL TABLE, BOOLEAN, and RECORD Types
It is not feasible for Oracle JDBC drivers to support calling arguments or return values of the PL/SQL RECORD, BOOLEAN, or table with non-scalar element types.
However, Oracle JDBC drivers support PL/SQL index-by table of scalar element types. For a complete description of this, see "Accessing PL/SQL Index-by Tables"
on page 16-21. As a workaround to PL/SQL RECORD, BOOLEAN, or non-scalar table types, create wrapper procedures that handle the data as types supported by JDBC. For example,
to wrap a stored procedure that uses PL/SQL booleans, create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, create a stored procedure that handles a record in its individual components (such as CHAR and NUMBER) or in a structured
object type. To wrap a stored procedure that uses PL/SQL tables, break the data into components or perhaps use Oracle collection types.
0
 
LVL 35

Expert Comment

by:girionis
Comment Utility
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
This video teaches viewers about errors in exception handling.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now