PL/SQL

February 24, 2007

CSV to XMLTYPE to TABLE

Filed under: example, EXTRACTVALUE, pl/sql code, XMLSEQUENCE, XMLTYPE — Michael Moore @ 10:22 pm

Take a CSV string and use it as SELECT criteria.

DECLARE
mystr             VARCHAR2 ( 2000 );
mycomma_list      VARCHAR2 ( 1000 ) := '1000,999,0980,49494';

BEGIN
mystr := '<list><x>' || REPLACE ( mycomma_list, ',', '</x><x>' ) || '</x></list>';
 
FOR cur2 IN
  ( SELECT portal_name
  FROM tx_portal
  WHERE portal_key IN (
        SELECT TO_NUMBER ( EXTRACTVALUE ( COLUMN_VALUE, '/x' ) )
        FROM TABLE ( SELECT XMLSEQUENCE ( EXTRACT ( XMLTYPE.createxml ( mystr )
        , '/list//x' ) )
       FROM DUAL ) ) )
LOOP
DBMS_OUTPUT.put_line ( 'Got ' || cur2.portal_name );
END LOOP;
END;

Create a free website or blog at WordPress.com.