Passing array to functions

in Oracle Database; Hi everybody, I want to pass array to function but whitout anonymous block so I have function declared as below: ...
+ Post Reply + Post New Topic
Results 1 to 3 of 3
  1. #1
    Marco is offline Junior Member
    Points: 310, Level: 6
    Join Date
    21 Sep 2012
    Posts
    9
    Points
    310

    Passing array to functions

    Hi everybody,

    I want to pass array to function but whitout anonymous block so I have function declared as below:

    CREATE OR REPLACE
    FUNCTION fun_with_array (test_data in ARRAY_OF_VARCHAR)
    RETURN INTEGER
    /.../

    ARRAY_OF_VARCHAR is declared as below:
    CREATE OR REPLACE TYPE ARRAY_OF_VARCHAR AS VARRAY (100) OF VARCHAR2(255);

    All I want to do is :

    select fun_with_array ('1, 2, 3') from dual;

    but I dont know how construct array in this case, I know how to do it with anonymous block:



    DECLARE
    test_data ARRAY_OF_VARCHAR := ARRAY_OF_VARCHAR('1', '2', '3');
    res integer;
    BEGIN
    SELECT fun_with_array(test_data) into res from dual;
    end;


    and it works fine but my goal is to do it witout anonymous block.


    Thanks for your help
    Marco
    Last edited by Marco; 11-16-2012 at 10:32 AM.


  2. #2
    dariyoosh's Avatar
    dariyoosh is offline Senior Member
    Points: 1,830, Level: 25
    Join Date
    11 Aug 2009
    Posts
    118
    Points
    1,830

    Re: Passing array to functions

    Hello there,


    I don't see any problem of using a constructor in the same way. Here is how I proceed.

    Code :
    SQL> CREATE OR REPLACE TYPE ARRAY_OF_VARCHAR IS VARRAY(100) OF VARCHAR2(255);
      2  /
     
    Type created.
     
    SQL> SELECT array_of_varchar('a', 'b', 'c', 'd') FROM DUAL;
     
    ARRAY_OF_VARCHAR('A','B','C','D')
    -------------------------------------------------------------------
    ARRAY_OF_VARCHAR('a', 'b', 'c', 'd')
    Which shows that you can use the constructor once you have defined your varray as a built-in type (that is, schema level)

    As an example I define the following function which simply returns the number of elements in a varray of the above defined type
    Code :
    CREATE OR REPLACE FUNCTION myfun(param_varrayVarchar IN array_of_varchar)
    RETURN PLS_INTEGER
    IS
    BEGIN
         RETURN param_varrayVarchar.COUNT;
    END myfun;
    /
     
    Function created.
     
    SQL>

    And finally here is how I can call this function by using the constructor of the varray in order to convert the effective
    function parameter to the correct type.
    Code :
    SQL> SELECT myfun(array_of_varchar('a', 'b', 'c', 'd')) FROM DUAL;
     
    MYFUN(ARRAY_OF_VARCHAR('A','B','C','D'))
    ----------------------------------------
    				       4
     
    SQL>



    Regards,
    Dariyoosh
    Marco says Thanks.

  3. #3
    Marco is offline Junior Member
    Points: 310, Level: 6
    Join Date
    21 Sep 2012
    Posts
    9
    Points
    310

    Re: Passing array to functions

    Thank you very much, this is what I was looking for

Other Solutions
  1. Add contents of Associative array
    By arunshankar.c in forum SQL PL/SQL
    Replies: 1
    Last Post: 08-30-2012, 02:54 PM
  2. Replies: 2
    Last Post: 04-11-2011, 05:30 PM
  3. Passing JSP encrypted parameters to frmservlet
    By stat.udayanga in forum Oracle Forms and Reports
    Replies: 0
    Last Post: 10-27-2010, 09:56 AM
  4. Parameter passing in where clause
    By Tariq Bashir Malhi in forum SQL PL/SQL
    Replies: 3
    Last Post: 01-29-2010, 08:11 AM
  5. passing parameter using fnd_request
    By vamsioracle in forum Oracle Apps Technical
    Replies: 6
    Last Post: 04-29-2009, 11:04 AM