Oracle Quoted CSV to Rows/Select from CSV

Had to share this awesome little tidbit. In 11g:
select extractvalue(column_value,'text()') vals from xmltable('"this","is","my","csv"');

vals
this
is
my
csv

Thanks Laurent Schneider for your original post of this here!

Parsing CSV with PL/SQL

I also outline a method to generate CSV very quickly from PL/SQL here

I needed to parse a very simple CSV but wanted to avoid having to do all of the normal looping and extending involved in populating varrays in pl/sql. So I came up with the below. A single query, taking in a csv variable and returning rows on the fly. If you run it, you’ll be prompted for a string. Enter in anything separating by commas and check the results.

WITH csv_data AS (SELECT '&your_csv_string' AS CSV ,'[^ |,]+' REGEX FROM DUAL)

SELECT regexp_replace(TRIM(REGEXP_SUBSTR(A.CSV, A.REGEX, 1, LEVEL)),'[^[:print:]]', '') STR
FROM csv_data a
CONNECT BY LEVEL <= REGEXP_COUNT(A.CSV, A.REGEX)
ORDER BY 1

The only caveat with this being that the csv is limited to a number of characters (a max im not going to research at this moment.)

A more proper way to do this would be a table function/procedure using the below:



DECLARE
TYPE MYDTTP IS TABLE OF VARCHAR2(2000) INDEX BY PLS_INTEGER;
MYDTTAB MYDTTP;
MYCSV VARCHAR2(24000);
VAL VARCHAR2(4000);
REGEXPAT VARCHAR2(100) := '[^,|'||CHR(10)||'|'||CHR(13)||']+';

BEGIN

MYCSV := 'adm_admit_type_tbl,
adm_app_dat
a_dtl,
adm_app_ plan_dtl,
adm_app_program_dtl,
ads,
adm_app_recruit_category_dtl,
adm_basis_admit_dtl,
adm_basis_admit_tbl,
adm_eop_data_dtl, ,
,
,
adm_mntr_app_college_attnd_dtl,
adm_mntr_app_college_crs_dtl,
adm_mntr_app_data_dtl,
adm_mntr_app_hs_cp_crs_dtl,,, ,,,,,';

DBMS_OUTPUT.PUT_LINE(REGEXP_COUNT(MYCSV, REGEXPAT));

FOR I IN 1 .. REGEXP_COUNT(MYCSV, REGEXPAT)
LOOP
VAL := TRIM(REGEXP_SUBSTR(MYCSV, REGEXPAT, 1, I));
BEGIN
MYDTTAB(I) := VAL;
EXCEPTION
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;
END LOOP;

DBMS_OUTPUT.PUT_LINE(MYDTTAB.COUNT());

FOR I IN 1 .. MYDTTAB.COUNT()
LOOP
CONTINUE WHEN MYDTTAB(I) IS NULL;
DBMS_OUTPUT.PUT_LINE(MYDTTAB(I));
END LOOP;
END;



Otherwise, have a good weekend.