PL SQL Record in Java

0 pts.
Tags:
J2EE
Java
SQL
Dear all, I have a Stored Package written as follows:

CREATE OR REPLACE PACKAGE firstPackage IS --Type definition TYPE sampleType IS RECORD ( name VARCHAR2(128), interface VARCHAR2(128), tablespc VARCHAR2(128) ); TYPE c_mx1_type IS REF CURSOR; --procedure definition PROCEDURE iwelcome( iNum in NUMBER, iOutput out NUMBER, st out sampleType ); PROCEDURE uwelcome; END firstPackage; / create or replace PACKAGE BODY firstPackage is PROCEDURE iwelcome( iNum in NUMBER, iOutput out NUMBER, st out sampleType) IS i NUMBER; refcursor c_mx1_type; BEGIN iOutput := iNum; OPEN refcursor FOR 'select NAME,ID,DESC from TABLE1 where NUMBER = '||iNum||''; i := 0; LOOP FETCH refcursor into st; EXIT WHEN refcursor%NOTFOUND; i := i+1; END LOOP; --DBMS_OUTPUT.PUT_LINE(nvl(st.name,'NVL')); END; END; / And MY Java Programme to Call this Procedure in Package contains the following snippet: CallableStatement cs = (OracleCallableStatement)conn.prepareCall("{call firstPackage.iwelcome(?,?,?) }"); cs.setInt(1,3456); cs.registerOutParameter(1,java.sql.Types.INTEGER); cs.registerOutParameter(2,java.sql.Types.STRUCT,"firstPackage.sampleType"); cs.execute(); 


I am not able to register the second outputParameter. I have tried giving STRUCT, ARRAY etc. etc and every time I get the java.sql.SQLException: invalid name pattern: firstPackage.sampleType Exception. Please help me out. Thanks and Regards, tanmoy
ASKED: November 6, 2004  10:37 PM
UPDATED: July 10, 2013  4:36 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

PL/SQL RECORD type is not supported by Oracle JDBC. You will have to create a wrapper procedure that accepts the PL/SQL RECORD type from the original procedure and passes the individual components to JDBC using the supported types (CHAR, NUMBER, etc).

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Rdocena
    Instead of PL/SQL Record as OUT parameter, you could use a cursor as OUT parameter which is supported. CREATE OR REPLACE PACKAGE pkg_types IS ------------------------------ -- Type/Sub-type Definitions ------------------------------ TYPE typ_csr_generic IS REF CURSOR; END pkg_types; / CREATE OR REPLACE PACKAGE pkg_somepkg IS FUNCTION fn_somefn ( i_param1 IN ) RETURN pkg_types.typ_csr_generic IS v_cursor pkg_types.typ_csr_generic; BEGIN OPEN v_cursor FOR 'SELECT FROM WHERE col-1 = :b1' USING i_param1; RETURN v_cursor; END fn_somefn; PROCEDURE pr_somepr ( i_param1 IN o_param2 OUT pkg_types.typ_csr_generic ) IS BEGIN OPEN o_param2 FOR 'SELECT FROM WHERE col-1 = :b1' USING i_param1; END pr_somepr; END pkg_somepkg; / You have a choice of a function or procedure above. In your Java code (from our Java developer, i'm the PL/SQL developer): cs.registerOutParameter(2,OracleTypes.CURSOR)
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following