1 Reply Latest reply on May 29, 2012 4:24 PM by rp0428

    Error When Accessing PLSQL Function Return Array



      I have a function which returns array object and for this I created

      create or replace type dates       is array(10000) of varchar2(32)


      CREATE OR REPLACE function datefunc (        id1    IN number,     id2    IN NUMBER        ) RETURN dates AS datearray       dates; sdate VARCHAR2 (32); edate VARCHAR2 (32); BEGIN 


                    connection = datacon.getConnection();             callablestatement = connection.prepareCall("{? = call datefunc (?,?)}");             callablestatement.registerOutParameter(1, Types.ARRAY);             callablestatement.setInt(2, param1);             callablestatement.setInt(3, param2);             callablestatement.execute(); 

      I am calling this java, however I am getting exception. How can I resolve this error?

        java.sql.SQLException: ORA-06550: line 1, column 13: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored 

      Edited by: ponic on May 29, 2012 9:12 AM

        • 1. Re: Error When Accessing PLSQL Function Return Array
          I am calling this java, however I am getting exception. How can I resolve this error?
          By using the proper syntax.

          This code
          callablestatement.registerOutParameter(1, Types.ARRAY);
          Says that an array will be used but doesn't say which array. You have to specify the actual Oracle type name as the third parameter.

          The two-parameter format is fine for standard data types since a date is a date and a varchar is a varchar2. But an array is not an array; the contents of an array can be different so the array definition is needed.

          See 11 Accessing and Manipulating Oracle Data in the JDBC Dev guide
          Other setXXX Methods
          As with the getXXX methods, there are several specific setXXX methods. Standard setXXX methods are provided for binding standard Java types, and Oracle-specific setXXX methods are provided for binding Oracle-specific types.
          . . .
          Similarly, the registerOutParameter method has a signature for use with REF, ARRAY, or STRUCT data:
          void registerOutParameter
          (int parameterIndex, int sqlType, String sql_type_name)
          The type name (dates for your case) is needed to get the type definition metadata from the database in order to construct the proper buffer object.

          I strongly recommend that you not use object names such as 'dates' that so closely resemble reserved words; that is asking for trouble.