Need assistance making a Query with array parameters in SQL-Plus to work...can any SQLplus guru render some help?

Hi all,
I have a piece of SQL PLUS code that I am trying to make it work with little success. It is to be executed in a SQL PLUS environment and I need help with it...

The purpose of this code is to call a store procedure with parameter array and successfully return required data values. The part of the code that is giving me the blues is as follows:

DECLARE
  IN_FSFN_OBJECTS_ARRAY AMSCIC.FSFN_OBJECT_ARRAY := AMSCIC.FSFN_OBJECT_ARRAY();
 fsfn1  AMSCIC.fsfn_object_array_type := AMSCIC.fsfn_object_array_type(null,null);
cursor curDisposed is

select work_item_id,familY_safety_id
from amscic.work_item_t
where status_code = 'D'
and status_reason_code in ('APP','DEN'); recDisposed curDisposed%rowtype; i number(3) := 1; BEGIN for recDisposed in curDisposed loop

fsfn1:
=AMSCIC.fsfn_object_array_type(recDisposed.family_safety_id,recdisposed.work_item_id);
        IN_FSFN_OBJECTS_ARRAY.extend;
        IN_FSFN_OBJECTS_ARRAY(i) := fsfn1;
      i := i+1;
end loop;

--Modify the code to initialize this parameter

  FOR rec in (SELECT * FROM

TABLE(CAST(AMSCIC.FAMILY_SAFETY_WORK_PKG.GET_DISPOSED_APPLICATIONS_FNC(IN_FSFN_OBJECTS_ARRAY)
 AS AMSCIC.DISPOSED_APPLICATION_T)))
  LOOP
dbms_output.put_line('work item is ' ||  rec.work_item_id); dbms_output.put_line('case number is ' ||  rec.case_number);
    EXIT WHEN 1=1;
  END LOOP;
  COMMIT;
END;
/
How can I execute this code such that I can pass arrays. For example,
1. Exec SP_procname( 1,2)  works fine
But how would we execute a stored procedure where we have arrays.

2. Exec SP_procname( [1,5],[2,7]);  This seems to throw an error.

 Attached is ithe error message screen shot trhat I receive. Please take a moment to read to better understand what I am talking about. The attachment is a doc file and you may have to zoom out to read clearly.

Any help in this matter will be highly appreciated...

Thanks


Start Free Trial