Announcement Announcement Module
Collapse
No announcement yet.
Passing an Array to a PL/SQL Stored Procedure Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Passing an Array to a PL/SQL Stored Procedure

    I'm having trouble using a Java/Spring DAO to call a PL/SQL stored procedure with an Array type.
    I've used the AbstractSqlTypeValue class and it seems like I'm 95% there but right now I seem to be getting an error with the ArrayDescriptor type.
    The same error occurrs if I define the new Types in the Database, or the database package, and call the executeProcedure using by prefixing the package name or not.
    Any responses would be greatly appreciated!


    Here's the Error (): --
    JDBC ORA 17074 --
    org.springframework.jdbc.UncategorizedSQLException : (
    executing CallableStatementCallback [CallableStatementCreatorFactory.CallableStatementC reatorImpl:
    sql=[{call fee_insert_pkg.record_batch_errors(?, ?, ?)}]:
    params=[{param2=org.ddpa.npf.storedproc.OracleSpringStored ProcedureDAO$MyArray@12e7cb6,
    param0=31, param1=org.ddpa.npf.storedproc.OracleSpringStoredP rocedureDAO$MyArray@12cd8d4}]]):
    encountered SQLException [Fail to construct descriptor: Unable to resolve type:
    "DB3DEV.ERROR_CD_ARRAY"]; nested exception is java.sql.SQLException:
    Fail to construct descriptor: Unable to resolve type: "DB3DEV.ERROR_CD_ARRAY"
    java.sql.SQLException: Fail to construct descriptor: Unable to resolve type: "DB3DEV.ERROR_CD_ARRAY"




    Here's the PL/SQL definition:--

    -- Defined in Stored Procedure Spec
    -- Arrays of Error Codes and Field Types
    Type ERROR_CD_ARRAY IS Table of VARCHAR(5) INDEX BY BINARY_INTEGER;
    Type FIELD_TYPE_ARRAY IS Table of VARCHAR(5) INDEX BY BINARY_INTEGER;

    -- OR(?) Defined in Database:
    create or replace type ERROR_CD_ARRAY as table of VARCHAR(5) INDEX BY BINARY_INTEGER;
    create or replace type FIELD_TYPE_ARRAY as table of VARCHAR(5) INDEX BY BINARY_INTEGER;

    PROCEDURE record_batch_errors(p_bat_fee_id IN NUMBER
    , p_err_array IN ERROR_CD_ARRAY
    , p_field_array IN FIELD_TYPE_ARRAY);




    Here's the Java code: --
    package test;

    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Types;
    import java.util.ArrayList;
    import java.util.List;

    import proc.CallableParameter;
    import proc.OracleSpringStoredProcedureDAO;
    import oracle.jdbc.OracleConnection;
    import oracle.jdbc.OracleTypes;

    import org.springframework.jdbc.core.RowMapper;
    import org.springframework.jdbc.core.support.AbstractSqlT ypeValue;

    public void callArrayProcedure(){
    System.out.println("Calling proc");


    ArrayList list1=new ArrayList();
    list1.add("1");
    list1.add("2");

    ArrayList list2=new ArrayList();
    list2.add("A");
    list2.add("B");

    ArrayList params=new ArrayList();

    params.add(new CallableParameter(new Long(20),Types.NUMERIC));
    params.add(new CallableParameter(new MyArray(list1),Types.ARRAY,"ERROR_CD_ARRAY"));
    params.add(new CallableParameter(new MyArray(list2),Types.ARRAY,"FIELD_TYPE_ARRAY"));
    executeProcedure("fee_insert_pkg.record_batch_erro rs",params);
    }

    public class MyArray extends AbstractSqlTypeValue{
    private ArrayList values;

    public MyArray(ArrayList values){
    this.values=values;
    }

    public Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException {
    OracleConnection oracle=(OracleConnection)((org.apache.commons.dbcp .PoolableConnection)con).getDelegate();

    oracle.sql.ArrayDescriptor desc = new oracle.sql.ArrayDescriptor(typeName,oracle);

    return new oracle.sql.ARRAY(desc, oracle,(String[])values.toArray(new String[values.size()]));
    }
    }
    }

    public class OracleSpringStoredProcedureDAO extends JdbcDaoSupport
    implements StoredProcedureDAO {
    private static final String REF_CURSOR = "ref_cursor";

    public List executeProcedure(final String name,final List params){
    final StoredProcedure proc=new StoredProcedure(getJdbcTemplate(),name){ };

    HashMap paramValues=new HashMap();
    for(int i=0;i<params.size();i++){
    CallableParameter parameter=(CallableParameter)params.get(i);
    String paramName="param"+i;
    if(parameter.getType()!=null){
    proc.declareParameter(new SqlParameter(paramName,parameter.getSqlType(),para meter.getType()));
    }else {
    proc.declareParameter(new SqlParameter(paramName,parameter.getSqlType()));
    }
    paramValues.put(paramName,parameter.getValue());
    }
    proc.compile();

    return (List)proc.execute(paramValues).get(REF_CURSOR);
    }
    }

    public class CallableParameter {
    private Object value;
    private int sqlType;
    private String type;
    /**
    * Creates a new instance of CallableParameter.
    */
    public CallableParameter() {
    }
    /**
    * Creates a new instance of CallableParameter with the given parameters.
    *
    * @param value
    * The parameter object
    * @param sqlType
    * The SQL type of the parameter object.
    */
    public CallableParameter(Object value, int sqlType) {
    this.value = value;
    this.sqlType = sqlType;
    }

    public CallableParameter(Object value,int sqlType,String type){
    this.value=value;
    this.sqlType=sqlType;
    this.type=type;
    }
    /**
    * @return Returns the sqlType.
    */
    public int getSqlType() {
    return sqlType;
    }
    /**
    * @param sqlType
    * The sqlType to set.
    */
    public void setSqlType(int sqlType) {
    this.sqlType = sqlType;
    }
    /**
    * @return Returns the value.
    */
    public Object getValue() {
    return value;
    }
    /**
    * @param value
    * The value to set.
    */
    public void setValue(Object value) {
    this.value = value;
    }
    /**
    * @return Returns the type.
    */
    public String getType() {
    return type;
    }
    /**
    * @param type The type to set.
    */
    public void setType(String type) {
    this.type = type;
    }
    }

  • #2
    This is probably a known Oracle bug which is present in 8.1.7.3 and up, including 10g, see:

    http://support.oracle.com.sg/metalin..._id=241286.999

    Basically you should add the schema name in capitals to the front of the typeName argument of new ArrayDescriptor() call.

    Code:
    oracle.sql.ArrayDescriptor desc = new oracle.sql.ArrayDescriptor&#40;"XYZ." + typeName,oracle&#41;;
    I horrible hack I know, but it appears to be Oracles recommended work-around. You can always put the schema name in a parameter table or properties file.

    I will try and find the bug number and update this thread.

    regards,
    Chris

    Comment


    • #3
      Thanks Chris,

      Finally got it to work:

      Had to drop the PL/SQL Array definitions as Objects (using INDEX BY BINARY_INTEGER) and replace them with Collections as defined by:
      create or replace type ERROR_CD_ARRAY as table of VARCHAR(5);
      create or replace type FIELD_TYPE_ARRAY as table of VARCHAR(5);

      Then changed the call to:

      params.add(new CallableParameter(new Long(220),Types.NUMERIC));
      params.add(new CallableParameter(new MyArray(list1,"ERROR_CD_ARRAY"),Types.ARRAY));
      params.add(new CallableParameter(new MyArray(list2,"FIELD_TYPE_ARRAY"),Types.ARRAY));
      executeProcedure("fee_insert_pkg.record_batch_erro rs",params);

      This works!(?)
      Thanks for your help.

      Colum

      Comment

      Working...
      X