?
Solved

Handle PLSQL table through java

Posted on 2002-05-29
5
Medium Priority
?
437 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
Suggested Courses

719 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