Procedure CSV parameters as a SQL table in your code

In some cases you might need to have a set of data come into your procedure as a CSV. You then may need to use that data in a query in your procedure.

The below example demonstrates a method to do just that: accept a CSV parameter, parse it, and then use it in a SQL statement (query the CSV items as a table) in a procedure.


CREATE OR REPLACE PROCEDURE MULTI_VAL_PARAM_EXMPLE
(
MULTI_VAL_PARAM IN OUT VARCHAR2
,OUT_REF_CUR IN OUT SYS_REFCURSOR
) AS

-- SYS.KU$_VCNT is a system delivered type granted to PUBLIC - you may want to create/use your own.
-- If you need a nested table with multiple fields, you'll definitely need to define your own.
TT_PLSQL_TBL SYS.KU$_VCNT := SYS.KU$_VCNT();


-- sys_refcursor to be used internally before passing back out.
DATA_TO_SEND_BACK SYS_REFCURSOR;

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
-- This is an internal procedure to our main procedure but you may want to create this as an
-- external procedure to be able to use it in other procedures. This string splitter is just an
-- example. You may have a better one that you can substitute if you choose.
PROCEDURE RTRN_NESTED_TBL_FROM_CSV
(
V_CSV IN VARCHAR2
,OUT_NESTED_TBL IN OUT SYS.KU$_VCNT
) IS

-- regexp pattern to find anything seperated by a comma.
REGEX_PATTERN VARCHAR2(100) := '[^,]+';

BEGIN
-- Loop thru the csv string N times, getting the (I)th value in the
-- string each loop and adding that to your pl/sql table. The number of times to loop is
-- determined by a regexp_count function call
IF TRIM(V_CSV) IS NOT NULL THEN
FOR I IN 1 .. REGEXP_COUNT(V_CSV
,REGEX_PATTERN)
LOOP
OUT_NESTED_TBL.EXTEND;
-- Add that value to your pl/sql table
OUT_NESTED_TBL(I) := TRIM(REGEXP_SUBSTR(MULTI_VAL_PARAM
,REGEX_PATTERN
,1
,I));

END LOOP;
END IF;

END RTRN_NESTED_TBL_FROM_CSV;
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

BEGIN

RTRN_NESTED_TBL_FROM_CSV(MULTI_VAL_PARAM
,TT_PLSQL_TBL);

-- now that you've parsed your values coming in and placed em' in
-- your pl/sql nested table, you can do something with 'em. In the case below,
-- we're using Oracle's TABLE operator to tell Oracle that we'd like to use the nested table
--as a sql table. In any other case, you might want to loop thru the above table
-- and do something with each value.
-- dbms_output.put_line(select * from table(TT_PLSQL_TBL););
OPEN DATA_TO_SEND_BACK FOR
--your query here!
SELECT A.OBJECT_NAME
,A.OBJECT_TYPE
,A.CREATED
FROM SYS.USER_OBJECTS A
WHERE A.OBJECT_NAME IN (SELECT * FROM TABLE(TT_PLSQL_TBL));


-- Example using another method of querying the nested table using Oracle's "MEMBER OF" feature
/*SELECT A.OBJECT_NAME
,A.OBJECT_TYPE
,A.CREATED
FROM SYS.USER_OBJECTS A
WHERE A.OBJECT_NAME MEMBER OF TT_PLSQL_TBL;*/

OUT_REF_CUR := DATA_TO_SEND_BACK;

END MULTI_VAL_PARAM_EXMPLE;