1. The Forum has been upgraded to a modern and advanced system. Please read the announcement about this update.
  2. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Passing array to functions

Discussion in 'SQL PL/SQL' started by Marco, Nov 16, 2012.

  1. Marco

    Marco Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    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
     
  2. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    Hello there,


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

    Code (Text):

    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 (Text):

    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 (Text):

    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 likes this.
  3. Marco

    Marco Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Thank you very much, this is what I was looking for :)