Passing array to PL/SQL function

AlanShar

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

JustinCave

I don't think you're showing us everything.

The anonymous block you posted, for example, cannot possibly compile. You're declaring a local variable named MyArray. You're then referencing a variable IN_FEATURE_IDS in your query. There is no way that could work unless IN_FEATURE_IDS is somehow defined somewhere else. I suspect that this block isn't actually running.

The anonymous block is declaring a collection of type NUM_ARR1. Your procedure is accepting a collection of type selected_pkg.NUM_ARRAY. Those are two different collection types. If you want to use a collection in SQL, it needs to be defined in SQL not in a PL/SQL package. If NUM_ARR1 is defined in SQL, that would explain why those collections can be used in SQL while collections of type selected_pkg.NUM_ARRAY cannot. But that wouldn't explain the error you say you're getting in your procedure which is referring to a problem resolving the identifier not that the identifier is of the wrong type.

Justin

Solomon Yakobson

TABLE operator is SQL, not PL/SQL operator and only works for collections of SQL type. You can't use it against PL/SQL declares types. Create type in SQL, not in package:

CREATE OR REPLACE TYPE NUM_ARRAY IS TABLE OF NUMBER
/
create or replace 
PACKAGE selected_pkg IS
FUNCTION get_selected_kml(
in_layer IN NUMBER,
in_id IN NUMBER,
in_feature_ids IN NUM_ARRAY,
in_lx IN NUMBER,
in_ly IN NUMBER,
in_ux IN NUMBER,
in_uy IN NUMBER
)
RETURN CLOB;

END selected_pkg;

And in working example you posted what is NUM_ARR type? I has to be SQL type, otherwise it wouldn't work.

SY.

AlanShar

Thanks Justin, Sorry I missed a line in the anonymous block, Please find the code below:

--Running function query from passing array for IDs
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;

Thanks,
Alan

APC

Please top'n'tail your source code with {noformat}

{noformat} tags.  They make it so much easier to read your code.

Cheers, APC                                                                                                                                                                                                                                                                                

William Robertson

It still refers to something called 'in_feature_ids' that is not declared anywhere. My guess is this would have been an IN parameter passed into some function, and you have copied and pasted it into a test block but missed the argument list.

There is also a bracket missing at the end of your FOR cursor (before 'loop'), and an unexplained ">" character before 'sdo_filter'.

Please use

 tags for code and {quote} or '>' for quotations.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Avinash1234

Hi Alan,

If you are calling this function thr another PLSQL subblock, then please check if you r able to add ref cursor as in parameter. It is same as passing array.

http://docs.oracle.com/cd/E11882_01/win.112/e23174/featRefCursor.htm

Thanks,
Avinash.