SQL & PL/SQL

Hi,

I am passing an array to a PL/SQL package function. I am doing this to use this array in a query inside the function which has IN clause.

My declaration of package looks like :

create or replace 
PACKAGE selected_pkg IS

  TYPE NUM_ARRAY IS TABLE OF NUMBER;

   FUNCTION get_selected_kml(
      in_layer        IN NUMBER,
     in_id      IN NUMBER,
      in_feature_ids  IN selected_pkg.NUM_ARRAY,
      in_lx           IN NUMBER,
      in_ly           IN NUMBER,
      in_ux           IN NUMBER,
      in_uy           IN NUMBER
      )
    RETURN CLOB;

END selected_pkg;

In my PL/SQL function I am firing a query like following

select a.id, a.geom  from Table_FIELD a  where  a.id in (select * from table (in_feature_ids)) and  sdo_filter(A.GEOM,mdsys.sdo_geometry(2003,4326,NULL,mdsys.sdo_elem_info_array(1,1003,3), mdsys.sdo_ordinate_array(0,57,2.8,59)),'querytype= window') ='TRUE'

The same query runs fine if I run it from anonymous block like

CREATE TYPE num_arr1 IS TABLE OF NUMBER;

declare
    myarray num_arr1 := num_arr1(23466,13396,14596);
BEGIN
    FOR i IN (select a.id, a.geom  from Table_FIELD a  where  a.id in (select * from table (in_feature_ids)) and  sdo_filter(A.GEOM,mdsys.sdo_geometry(2003,4326,NULL,mdsys.sdo_elem_info_array(1,1003,3), mdsys.sdo_ordinate_array(0,57,2.8,59)),'querytype= window') ='TRUE'
    loop
       dbms_output.put_line(i.id);
    end loop;
end;

If I try to run it by calling function as below

create type num_arr1 is table of number;

--Running function from passing array for IDs
declare
  result CLOB;
  myarray selected_pkg.num_array := selected_pkg.num_array(23466,13396,14596);
begin
    result:=SELECTED_PKG.get_selected_kml(3, 19, myarray, 0.0,57.0,2.8,59);
end;

I am getting error

ORA-00904: "IN_FEATURE_IDS": invalid identifier

Could someone please help me understand the cause of it?

Thanks,
Alan

Edited by: AlanShar on Apr 30, 2013 5:50 AM