Return an Oracle Associative Array from a function

Question

Does anybody know if it is possible to return an associative array as the result of an Oracle function, if so do you have any examples?

I have an Oracle package which contains an associative array declaration as defined below:

TYPE EVENTPARAM IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;  

This is then used in a stored procedure outside the package as follows:

v_CompParams areva_interface.eventparam;

The intention is to store an associative array of strings in the variable v_CompParams, returned from a Parse function in another package. The definition for which is as follows:

PACKAGE STRING_MANIP  
IS 

    TYPE a_array IS TABLE OF NUMBER 
        INDEX BY BINARY_INTEGER; 

    FUNCTION Parse (v_string VARCHAR2, v_delim VARCHAR2) RETURN a_array; 
    FUNCTION RowCount(colln IN a_array) RETURN NUMBER;

END;

The code which implements this is:

v_CompParams := STRING_MANIP.PARSE(v_CompID,v_Delim);  

Unfortunately it doesn't work, I get the error 'PLS-00382: expression is of wrong type'. I foolishly assumed, that since a_array derives from the same source Oracle type as the variable v_CompParams, that there would be no problem casting between them. Any help much appreciated.

Kind Regards

Paul J.

Answer 1

To be of the very "same" type, v_CompParams must be defined as:

v_CompParams STRING_MANIP.a_array;