1 Reply Latest reply: May 29, 2012 11:24 AM by rp0428 RSS

    Error When Accessing PLSQL Function Return Array

    709156

      Hello,

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

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

      function

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

      Java

                    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?
      Thanks

        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
          rp0428
          >
          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
          http://docs.oracle.com/cd/B28359_01/java.111/b31224/datacc.htm#sthref519
          >
          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.