PLS-00306: wrong number or types of arguments in call to procedure UDT from ADF

Question

I need to insert data in dummy_emp table.

 dummy_table contains:
 emp_id number;
 emp_name varchar2(50);

 create table dummy_emp(empid number,empname varchar2(50));

 I have created Object type for dummy_emp table:

 CREATE OR REPLACE TYPE EMP_OBJ AS OBJECT (empid number,empname varchar2(50));

 I have created table for object type EMP_OBJ as EMP_OBJ_ARR

 create or replace type EMP_OBJ_ARR as table of EMP_OBJ;

 CREATE OR REPLACE PROCEDURE TEST_EMP_OBJ_ARRAY_PROC ( p_obj_array in EMP_OBJ_ARR ) AS
 begin
   for i in 1..p_obj_array.count loop
     insert into dummy_emp (empid, empname)
           values(p_obj_array(i).empid, p_obj_array(i).empname);
   end loop;
 end;
 /
I need to call TEST_EMP_OBJ_ARRAY_PROC from ADF AppModuleImpl.java class by passing parameter for EMP_OBJ_ARR.

For this,

I have created java class (EmployeeData.java) which implements sqlData.

package view;

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

 public class EmployeeData implements SQLData {
 private String sql_type="EMP_OBJ";
 public int empNo;
 public String empName;

public EmployeeData() {
    super();
}

public EmployeeData (String sql_type, int empNo, String empName)
{
this.sql_type = sql_type;
this.empNo = empNo;
this.empName = empName;

}
@Override
public String getSQLTypeName() throws SQLException {
    // TODO Implement this method
    return sql_type;
}

@Override
public void readSQL(SQLInput stream, String typeName) throws SQLException {
    // TODO Implement this method
        sql_type = typeName;
        empName = stream.readString();
        empNo = stream.readInt();
}

@Override
public void writeSQL(SQLOutput stream) throws SQLException {
    // TODO Implement this method
        stream.writeInt(empNo);
       stream.writeString(empName);
}

}

Code in AppModuleImpl.java

public void insert()
{
    DBTransaction dbTransaction = this.getDBTransaction();
    String query = "{call TEST_EMP_OBJ_ARRAY_PROC (?)}";
    CallableStatement cs = dbTransaction.createCallableStatement(query,0);
    try {
        //EmployeeData has sql_type as EMP_OBJ which I have defined in pl/sql.
        EmployeeData e=new EmployeeData("EMP_OBJ",1,"xyz");            
        cs.setObject(1,(Object) e);

        cs.executeUpdate();
        dbTransaction.commit();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

Here It should map Object e to TEST_EMP_OBJ_ARRAY_PROC ( p_obj_array in EMP_OBJ_ARR). But I am getting exception for

 java.sql.SQLException: ORA-06550: line 1, column 7:
 PLS-00306: wrong number or types of arguments in call to       'TEST_EMP_OBJ_ARRAY_PROC'
 ORA-06550: line 1, column 7:
 PL/SQL: Statement ignored

How to pass Object in callable statement to map User defined type in Pl/sql?

Answer 1

You need to call as below:

call TEST_EMP_OBJ_ARRAY_PROC(EMP_OBJ_ARR(EMP_OBJ(?,?)));

See demo:

SQL>  CREATE OR REPLACE PROCEDURE TEST_EMP_OBJ_ARRAY_PROC ( p_obj_array in EMP_OBJ_ARR ) AS
  2   begin
  3     for i in 1..p_obj_array.count loop
  4       insert into dummy_emp (empid, empname)
  5             values(p_obj_array(i).empid, p_obj_array(i).empname);
  6     end loop;
  7     commit;
  8   end;
  9   /

Procedure created.

SQL> show error
No errors.
SQL> select * from dummy_emp;

no rows selected

SQL> exec TEST_EMP_OBJ_ARRAY_PROC(EMP_OBJ_ARR(EMP_OBJ(1,'XXX')));

PL/SQL procedure successfully completed.

SQL> select * from dummy_emp;

     EMPID EMPNAME
---------- --------------------------------------------------
         1 XXX