Return an Oracle Associative Array from a function
- by Paul Johnson
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.
