1. 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 :)