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.

You have two types, one in areva_interface, one in string_manip - so they're not the same type (even if they have identical structure). — Jeffrey Kemp
– Jeffrey Kemp, Commented Jun 11, 2010 at 1:58

Tony Andrews · Accepted Answer · 2010-06-11 08:50:08Z

6

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

v_CompParams STRING_MANIP.a_array;

edited Jun 11, 2010 at 8:50

answered Jun 10, 2010 at 16:53

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Return an Oracle Associative Array from a function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related