CREATE OR REPLACE PACKAGE TEST_PACKAGE
IS
TYPE array_int IS TABLE OF NUMBER;
TYPE a_array IS TABLE OF VARCHAR (500)
INDEX BY PLS_INTEGER;
PROCEDURE get_all_data (empIds IN array_int, map OUT a_array);
FUNCTION get_detail (empId IN NUMBER)
RETURN VARCHAR2;
END TEST_PACKAGE;
------------------------------------------------------------------------------------------------
PROCEDURE get_all_data (empIds IN array_int, map OUT a_array)
AS
BEGIN
OPEN employee;
FOR i IN empIds.FIRST .. empIds.LAST
LOOP
map (empIds (i)) := TEST_PACKAGE.get_detail (empIds (i));
END LOOP;
END get_all_data;
FUNCTION get_detail (empId IN NUMBER)
RETURN VARCHAR2
AS
emp_name VARCHAR (50);
BEGIN
SELECT FIRST_NAME
INTO emp_name
FROM employees
WHERE EMPLOYEE_ID = empId;
RETURN emp_name;
END get_detail;
------------------------------------------------------------------------------------------------
JAVA CODE:
int array[] = {100,101,102};
ArrayDescriptor des = ArrayDescriptor.createDescriptor("ARRAY_INT", connection);
ARRAY arrayList = new ARRAY(des,connection,array);
OracleCallableStatement st =(OracleCallableStatement)
connection.prepareCall("begin TEST_PACKAGE.get_all_data(?,?); end;");
st.setArray(1, arrayList);
st.registerIndexTableOutParameter(2, array.length, OracleTypes.VARCHAR, 500);// I tried Binary/Number but didn't help.
st.execute();
Error:
java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_ALL_DATA'
ORA-06550: line 1, column 7:
PL/SQL: statement Ignored
What am I doing wrong?? Please help. I'm stuck.