This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,861 Users
  • 2,269,649 Discussions
  • 7,916,821 Comments

Discussions

Passing array to PL/SQL function

AlanShar
AlanShar Member Posts: 10
edited Apr 30, 2013 8:56AM in 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

Answers

  • JustinCave
    JustinCave Consultant Rochester Hills, MIMember Posts: 30,293 Gold Crown
    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
    Solomon Yakobson Member Posts: 20,213 Red Diamond
    edited Apr 30, 2013 7:18AM
    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
    AlanShar Member Posts: 10
    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
    APC Member Posts: 11,316 Bronze Crown
    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
    William Robertson London, UKMember Posts: 9,579 Bronze Crown
    edited Apr 30, 2013 7:29AM
    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
    Avinash1234 Member Posts: 16
    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.
This discussion has been closed.