PL/SQL Stored proc that uses a comma separated parameter to drive a dynamic LIKE clause?

Vote count: 0

Is there a fairly simple way to take an input parameter containing a comma seperated list of prefixes and return a cursor based on a select statement that uses these?

i.e. (Pseudocode)

PROCEDURE get_by_prefix(p_list_of_prefixes IN varchar2, r_csr OUT SYS_REFCURSOR)
IS
BEGIN
OPEN r_csr FOR
SELECT * FROM my_table where some_column LIKE (the_individual_fields_from p_list_of_prefixes ||'%')
END

I've tried various combinations, and now have two problems - coercing the input into a suitable table (I think it needs to go into a table type rather than a VARCHAR2_TABLE), and secondly getting the like clause to be effectively a SELECT from an internal 'pseudotable'...

asked 7 secs ago

PaulJWilliams

Let's block ads! (Why?)

PL/SQL Stored proc that uses a comma separated parameter to drive a dynamic LIKE clause?