Java SPs and Oracle Objects

From: Andrew Briers <andrewREMOVE.briers_at_cwcom.net>
Date: Thu, 23 Mar 2000 12:04:59 -0000
Message-ID: <QKnC4.288$%X3.6891_at_news1-hme0>

All,

I am trying to return a VARRAY of Oracle Objects from within a Java stored procedure and am not having much success.

I have created the Java classes to match the Oracle Objects using JPub, so the Java class matches the Oracle object.

When I try and create an ArrayDescriptor, I get a java.sql.SQLException Fail to construct descriptor: Unable to resolve type: "SYS.CASEINFO"

Java:
--snip--

    public static void GetCases(java.lang.Object caseDetails[])

            throws SQLException, ClassNotFoundException, IOException    {

...
...
...
           OracleConnection conn = (OracleConnection) new

OracleDriver().defaultConnection();

            Dictionary map = conn.getTypeMap();
            map.put(CaseInfo._SQL_NAME, Class.forName("CaseInfo"));

            CaseInfo javaCase[] = new CaseInfo[maxItems];

            for (int index = 0; index < maxItems; index++)
            {
                //javaCase[index] = new JavaCase(_oracleCaseInfo,
stringTok.nextToken());
                javaCase[index] = new CaseInfo(stringTok.nextToken());
            }

// The following line gives the 'unable to resolve type "SYS.CASEINFO"' error

            ArrayDescriptor arrayDesc = ArrayDescriptor.
                                        createDescriptor(CaseInfo._SQL_NAME,
conn);
            oracle.sql.ARRAY oArray = new ARRAY(arrayDesc, conn, javaCase);

            retObject[0] = oArray;

}
--snip--

Oracle
--snip--

CREATE OR REPLACE TYPE CaseInfo AS OBJECT (

            KeyData     VARCHAR2(128),
            Name        VARCHAR2(128),
            Description VARCHAR2(128)
         );

/

CREATE OR REPLACE TYPE Cases AS VARRAY(50) OF CaseInfo; /
CREATE OR REPLACE PACKAGE StaffWare AS

    PROCEDURE GetCases(swCases OUT SYS.Cases); END STAFFWARE;
/

CREATE OR REPLACE PACKAGE BODY StaffWare AS

    PROCEDURE GetCases(swCases OUT SYS.Cases) IS

        LANGUAGE JAVA
        NAME 'StaffWare.GetCases(java.lang.Object[])';

END STAFFWARE;
/

--snip--

Can anyone suggest what is wrong with the code ?

TIA,
Andrew Briers Received on Thu Mar 23 2000 - 13:04:59 CET