Passing String array as out parameter to stored procedure

[Ragupathirajan Venkatesan]

Dear All,

I am getting the ORA-06553: wrong number or types of arguments in call to 'Procedure' error when i am trying to pass the string array to oracle procedure.

I have created one oracle string array type like below.

Create or replace TYPE  STR_ARRAY AS VARRAY(100) OF VARCHAR2(50); 

Then I created one oracle procedure like below
create or replace PROCEDURE       PROCEDURE_TEST(
 RESULT_ARRAY   OUT   STR_ARRAY
) IS

 TEMP_PRICING_AREA_CODE   VARCHAR2(5);
 DEALCOUNT                 NUMBER;

  .
  .
  .some code here
  .
  . at last assigning my string values to array like below

  RESULT_ARRAY:=STR_ARRAY(TEMP_PRICING_AREA_CODE);

then my oracle procedure ends.

The above proc got compiled and successfully. I am getting the exception while I am trying to execute it from java like below

                        Connection connection=dataSource.getConnection();			
			statement = connection.prepareCall("call PROCEDURE_TEST(?)");
			statement.registerOutParameter(1, oracle.sql.ARRAY);

Please suggest me, how should i pass the out parameter in the above java code.

Thanks in advance.

[Sudheer Bhat]

You would need to change your code to

statement.registerOutParameter(1, oracle.sql.ARRAY,"STR_ARRAY");

[Ragupathirajan Venkatesan]

Thanks for your reply. Due to some technical requirements we dropped using the return string array in procedure. will use your ideas in future.