5 Replies Latest reply: Jul 11, 2012 10:12 AM by rp0428 RSS

    Get a STRUCT out of a CallableStatement

    929890

      I have a Java Stored Procedure and it returns an ARRAY of STRUCT obejcts.
      When I do the following in my Java application:

      ... oracle.sql.ARRAY a = cstmt.getARRAY(1); System.out.println(a.dump()); ...

      The output looks like this:

      name = MYSCHEMA.XML_DOC_REFS max length = 0 length = 226 element[0] =    name = MYSCHEMA.XMLDOCREF     length = 2     DBREF = ADS.DE     XML = oracle.sql.CLOB@eca36e element[1] =    name = MYSCHEMA.XMLDOCREF     length = 2     DBREF = AI.FR     XML = oracle.sql.CLOB@1dd9891 ...

      I have problems accessing the STRUCT objects to work with their values.

      The STRUCT is defined this way:

      CREATE OR REPLACE TYPE "MYSCHEMA"."XMLDOCREF" as object ( dbRef  varchar(256), xml    CLOB ) CREATE OR REPLACE TYPE "MYSCHEMA"."XML_DOC_REFS" AS TABLE OF xmlDocRef

      How can I achieve this? How must the code look like to access the returned STRUCT[] and subsequently the contained STRUCT elements?

      Thank you.

        • 1. Re: Get a STRUCT out of a CallableStatement
          gimbal2
          Quick google for "OJDBC STRUCT": Working with Oracle Object Types.

          http://docs.oracle.com/cd/B10500_01/java.920/a96654/oraoot.htm
          • 2. Re: Get a STRUCT out of a CallableStatement
            929890
            The art of using the proper search criteria... :-)

            Here's the 11g version: [http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraoot.htm#autoId1]

            But it does not explain well how to get the STRUCTS elements from the ARRAY being returned from my Java Stored Proceduer. I ever get a ClassCastException with every class I tried (STRUCT, STRUCT[], Struct, ...)
            Struct[] structs = (Struct[])a.getArray();         // --> ClassCastException
            STRUCT[] structs = (STRUCT[])a.getArray();         // --> ClassCastException
            Struct[] structs = (Struct[])a.getArray();         // --> ClassCastException
            Struct[] structs = (Struct[])a.getArray();         // --> ClassCastException
            
            
            Exception in thread "main" java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to [Loracle.sql.STRUCT;
            • 3. Re: Get a STRUCT out of a CallableStatement
              929890
              It seems as if it not possible to cast in the moment you call getArray().

              When I do it this way it works (for my case):
              Object[] structs = (Object[])a.getArray();      // Don't cast immediately with: STRUCT[] structs = (STRUCT[])a.getArray(); --> ClassCastException
              
              for(int i=0; i<structs.length; i++){
                   Struct str = (Struct)structs;
                   System.out.println("str: " + str);
                   Object[] attribs = str.getAttributes();
                   System.out.println("dbref: " + (String)attribs[0]);
                   System.out.println("clob: " + ((Clob)attribs[1]).toString());
              }
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
              • 4. Re: Get a STRUCT out of a CallableStatement
                gimbal2
                The devil is in the details. Arrays are objects too; an Object[] array is a different object type than a Struct[] array and so cannot be cast to each other (only to their parent type - Object).

                In essence the array was constructed like this:
                Object[] arr = new Object[3];
                arr[0] = new Struct(...);
                arr[1] = new Struct(...);
                arr[2] = new Struct(...);
                • 5. Re: Get a STRUCT out of a CallableStatement
                  rp0428
                  >
                  But it does not explain well how to get the STRUCTS elements from the ARRAY being returned from my Java Stored Proceduer
                  >
                  No - but you have an Array so you have to get it properly and then access its components. If you go to the index of that doc and look up Array you will see this for 'Retrieving from a result set'
                  http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraarr.htm#sthref889

                  And that section DOES describe how to access the components. The simplest way is to use the Oracle extensions and use 'getOracleArray'
                  >
                  getOracleArray
                  The getOracleArray method is an Oracle-specific extension that is not specified in the standard Array interface. The getOracleArray method retrieves the element values of the array into a Datum[] array. The elements are of the oracle.sql.* data type corresponding to the SQL type of the data in the original array.
                  For an array of structured objects, this method will use oracle.sql.STRUCT instances for the elements.
                  Oracle also provides a getOracleArray(index,count) method to get a subset of the array elements.
                  >
                  You should also review the section 'Retrieving Elements of a Structured Object Array According to a Type Map' in case you need to provide a type map for your struct.