[REGEX] ORA-20001: comma-separated list invalid near
419478Apr 19 2004 — edited May 5 2004Hi
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;