Women in Technology

Hear us Roar



Article:
  Stored Procedures for Java Programmers
Subject:   Issue in passing input parameters to my stored procedure and retreiving cursor as output
Date:   2007-10-23 22:43:20
From:   coolmaverick
Hi


I am facing Issue in passing input parameters to my stored procedure.


These are the stored procedure input and output parameter.


CREATE OR REPLACE
PROCEDURE getnextfield (
product_id IN NUMBER,
prod_field_value_array IN product_field_value_array,
field_sequence_id IN NUMBER,
responsecursor OUT TYPES.ref_cursor
)


where "product_field_value_array" is PRODUCT_FIELD_VALUE_ARRAY as table of INTEGER


Now when I am passing parameter from my java class I am getting the following error:-


Exception Trace Error code:-6550SQL State:-65000Message:-ORA-06550: line 1, column 13:
PLS-00306: wrong number or types of arguments in call to 'GETNEXTFIELD'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored



The java code for setting input parameters is as follows:-


OracleCallableStatement stmt = (OracleCallableStatement) con.prepareCall(" {? = call GETNEXTFIELD(?, ?, ?) }");


ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("PRODUCT_FIELD_VALUE_ARRAY",con);


oracle.sql.ARRAY array_to_pass_item =new ARRAY(descriptor, con , templist);


stmt.setInt(1,Integer.parseInt(productId));


stmt.setArray(2,array_to_pass_item);


stmt.setInt(3,Integer.parseInt(presentFieldId));


stmt.registerOutParameter(4, OracleTypes.CURSOR);


stmt.execute();


Note:-here templist I have passed is of type Integer[] and I am getting the exception mentioned above on the execute statement


Any quick help will be really appreciated.