How To Return A Dynamic Result Set In Oracle Function

I found a string tokenizer query on the net and packaged it into the below function, which return the dynamic set of tokens. The function compiles successfully but somehow I get th

Solution 1:

Assuming the function compiles, try:

SELECT*FROMTABLE(strtokenizer('a,b,c',','));

Reference:

• Returning Rows Through a Table Function in Oracle

The function needs to be corrected - use:

CREATE OR REPLACE FUNCTIONStrTokenizer (string IN VARCHAR2, 
                                         delimiter IN VARCHAR2)
RETURNkey_value_tableASv_retkey_value_tableBEGINSELECTCAST(MULTISET(SELECT LEVEL k, 
                              SUBSTR(STRING_TO_TOKENIZE, DECODE(LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1), 
                              INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL) - DECODE( LEVEL, 1, 1, INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL-1)+1)) v
                         FROM (SELECT string || delimiter AS STRING_TO_TOKENIZE , 
                                      delimiter AS DELIMITER
                                 FROM DUAL)
                   CONNECT BY INSTR(STRING_TO_TOKENIZE, DELIMITER, 1, LEVEL)>0 
                     ORDER BY level ASC) AS key_value_table)
    INTOv_retFROMDUAL;

  RETURN v_ret;

END;