---10G Solution. On SQL Plus session
alter session set nls_date_format='YYYY-MM-DD';
Session altered.
select regexp_replace(column_value,' *<[^>]*>[^>]*>’,';’)
from table(xmlsequence(cursor(select * from emp)));
=======================
-- Oracle 9i Solution. SQL Plus session.
-- Following sql fail to give EMPTY space for NULL values properly.
-- See PL/SQL solution to handle
SELECT XMLTRANSFORM
(COLUMN_VALUE,
XMLTYPE
('
)
) csv
FROM TABLE (XMLSEQUENCE (CURSOR (SELECT *
FROM emp)))
/
==============================
---- Oracle 9i PL/SQL solution
SQL> VAR cur refcursor
SQL> DECLARE
ctx DBMS_XMLGEN.ctxhandle;
BEGIN
ctx := DBMS_XMLGEN.newcontext ('select * from emp');
DBMS_XMLGEN.setnullhandling (ctx, DBMS_XMLGEN.empty_tag);
OPEN :cur FOR
SELECT (SELECT RTRIM(XMLAGG (XMLELEMENT (c,VALUE (t2),',').EXTRACT('//text()'))
FROM TABLE (XMLSEQUENCE (VALUE (t1).EXTRACT ('ROW/*'))) t2) csv
FROM TABLE(XMLSEQUENCE(DBMS_XMLGEN
DBMS_XMLGEN.closecontext(ctx);
END;
/
PL/SQL procedure successfully completed.
SQL> PRINT cur
==============================
