how to return a dynamic result set in Oracle function

Question

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 the error "ORA-00933: SQL command not properly ended". Can someone please help me debug this? Thank you.

CREATE OR REPLACE TYPE KEY_VALUE_TYPE is object (k varchar2(4000), v varchar2(4000));
CREATE OR REPLACE TYPE KEY_VALUE_TABLE is table of key_value_type;
CREATE OR REPLACE FUNCTION StrTokenizer
        (string IN VARCHAR2, delimiter IN VARCHAR2)
RETURN key_value_table AS
    v_ret key_value_table;
BEGIN
    SELECT
        CAST( 
            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)
    INTO
        v_ret
    FROM dual;
    return v_ret;
END;

select * from strtokenizer('a,b,c',',')
ORA-00933: SQL command not properly ended

Edit:

1. select * from table(strtokenizer('a,b,c',',')) gives "ORA-30732: table contains no user-visible columns".
2. ORA-30732 is gone when I logged on as the function owner, but the function cannot replace ':string'||':delimiter' with the passed-in values. How can I correct this? And how can I run a function when I am not logged on as its owner? Thank you.

Coates

I don't have an Oracle instance handy, but did you create the function and/or supporting objects while logged in as SYS? – Exorbitance

@OMG Ponies: Oh, I logged on as one user and created the function for another schema. This solved ORA-30732. Thank you. – Coates

for (2) bind variables aren't evaluated inside single quotes, they're treated like any other string, and you don't need the : before the variable name in this case; and by granting execute permissions to the user, and optionally adding a synonym. – Tentation

Answer 1

Assuming the function compiles, try:

SELECT * 
  FROM TABLE(strtokenizer('a,b,c',','));

Reference:

• Returning Rows Through a Table Function in Oracle

The function needs to be corrected - use:

CREATE OR REPLACE FUNCTION StrTokenizer (string IN VARCHAR2, 
                                         delimiter IN VARCHAR2)
RETURN key_value_table AS v_ret key_value_table
BEGIN
  SELECT CAST(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)
    INTO v_ret
    FROM DUAL;

  RETURN v_ret;

END;

Exorbitance

It gives me "ORA-30732: table contains no user-visible columns" – Coates

@Martin: I don't have an Oracle instance handy, but did you create the function and/or supporting objects while logged in as SYS? – Exorbitance