Solved

Handle PLSQL table through java

Posted on 2002-05-29
5
427 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
ID: 7043958
What is the type definition of ERRTBL?
0
 

Author Comment

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

Accepted Solution

by:
Krolim earned 100 total points
ID: 7044092
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
ID: 8813271
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Securing Jmx Console and web console 2 64
starter POM and spring-boot-starter,  spring-boot-web 2 43
Java DateChooser? 3 29
difference of if loops 23 41
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…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

910 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

16 Experts available now in Live!

Get 1:1 Help Now