Solved

Handle PLSQL table through java

Posted on 2002-05-29
5
429 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Spring Framework HTTPSession management 1 45
windows explorer path to command prompt 5 54
collection output issue 9 36
web project error add remove 1 27
Introduction Java can be integrated with native programs using an interface called JNI(Java Native Interface). Native programs are programs which can directly run on the processor. JNI is simply a naming and calling convention so that the JVM (Java…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

809 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