Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

[REGEX] ORA-20001: comma-separated list invalid near

419478Apr 19 2004 — edited May 5 2004
Hi
I create a function in pl/sql to do some parsing and characters replacements
I also use DBMS_UTILITY.COMMA_TO_TABLE
I am getting the error ORA-20001: comma-separated list invalid near when in the parameters that I pass I have either a * or ?. Could someone help me
Here is my code
The m_param can be abc or abc|dfd|... or ab*|dc??|....
CREATE OR REPLACE FUNCTION parseParameters(p_params IN VARCHAR2, p_column IN VARCHAR2)
RETURN VARCHAR2
IS

table_param dbms_utility.uncl_array;
count_param NUMBER;

allParams VARCHAR2(256);
paramFromTable VARCHAR2(50);
finalParams VARCHAR2(256);
likeTrig BOOLEAN := FALSE;

BEGIN
allParams := p_params;
DBMS_OUTPUT.PUT_LINE(allParams);
owa_pattern.change(allParams,'|',',','g');
DBMS_OUTPUT.PUT_LINE(allParams);

dbms_utility.comma_to_table(allParams, count_param, table_param);

finalParams := '(';

for i in 1..count_param loop
IF i > 1 THEN
finalParams := finalParams || ' OR ';
END IF;

likeTrig := FALSE;
paramFromTable := table_param(i);
DBMS_OUTPUT.PUT_LINE(paramFromTable);

IF owa_pattern.change(paramFromTable,'*','%','g') > 0 THEN
likeTrig := TRUE;
END IF;

IF owa_pattern.change(paramFromTable,'?','_','g') > 0 THEN
likeTrig := TRUE;
END IF;

IF likeTrig THEN
finalParams := finalParams || ' ' || p_column || ' LIKE ' || paramFromTable;
ELSE
finalParams := finalParams || ' ' || p_column || ' = ' || paramFromTable;
END IF;
DBMS_OUTPUT.PUT_LINE(finalParams);
end loop;

finalParams := finalParams || ')';
DBMS_OUTPUT.PUT_LINE(finalParams);
RETURN finalParams;
END;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 2 2004
Added on Apr 19 2004
4 comments
5,138 views