Extract CSV file format in Oracle SQL

How to extract CSV file format data in Oracle SQL Plus.

---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.getxmltype (ctx).EXTRACT('ROWSET/ROW'))) t1;

DBMS_XMLGEN.closecontext(ctx);
END;
/
PL/SQL procedure successfully completed.

SQL> PRINT cur
==================================