Friday, October 22, 2010

how to return a dynamic result set in Oracle function

Programmer 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


Find the answer here

No comments:

Post a Comment

LinkWithin

Related Posts with Thumbnails