I have a procedure that takes in an array in postgres, this syntax works:
SELECT * from myMethod(array['test','test'], array[''], 554, 73430, 322234, 'shazam');
the array keyword is what I am referring to. this works in postgres but I cannot find the documentation to understand how this work in oracle. how do i pass in arrays to function call?
the error when running the above is:
ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended" *Cause: *Action: Error at Line: 4 Column: 38
Advertisement
Answer
If you have the type:
CREATE TYPE array IS TABLE OF VARCHAR2(200);
and create your function:
CREATE FUNCTION myMethod( p_words IN array, p_suffixes IN array, p_value1 IN INT, p_value2 IN INT, p_value3 IN INT, p_prefix IN VARCHAR2 ) RETURN array PIPELINED DETERMINISTIC IS value VARCHAR2(200); BEGIN FOR i IN 1 .. p_words.COUNT LOOP value := p_prefix || p_words(i); FOR j IN 1 .. p_suffixes.COUNT LOOP value := value || p_suffixes(j); END LOOP; PIPE ROW ( value ); END LOOP; END; /
Then you can do:
SELECT * from myMethod(array('test1','test2'), array('ab','cd'), 554, 73430, 322234, 'shazam');
In earlier Oracle versions, you may need to use:
SELECT * from TABLE( myMethod(array('test1','test2'), array('ab','cd'), 554, 73430, 322234, 'shazam') );
and it outputs:
| COLUMN_VALUE | | :-------------- | | shazamtest1abcd | | shazamtest2abcd |
db<>fiddle here