csv format with select *

One more trick with xml.

I want to get a semi-column separated format without having to specify the columns


alter session set nls_date_format=’YYYY-MM-DD’;

Session altered.

select regexp_replace(column_value,’ *<[^>]*>[^>]*>’,’;’)
from table(xmlsequence(cursor(select * from emp)));

;7369;SMITH;CLERK;7902;1980-12-17;800;20;
;7499;ALLEN;SALESMAN;7698;1981-02-20;1600;300;30;
;7521;WARD;SALESMAN;7698;1981-02-22;1250;500;30;
;7566;JONES;MANAGER;7839;1981-04-02;2975;20;
;7654;MARTIN;SALESMAN;7698;1981-09-28;1250;1400;30;
;7698;BLAKE;MANAGER;7839;1981-05-01;2850;30;
;7782;CLARK;MANAGER;7839;1981-06-09;2450;10;
;7788;SCOTT;ANALYST;7566;1987-04-19;3000;20;
;7839;KING;PRESIDENT;1981-11-17;5000;10;
;7844;TURNER;SALESMAN;7698;1981-09-08;1500;0;30;
;7876;ADAMS;CLERK;7788;1987-05-23;1100;20;
;7900;JAMES;CLERK;7698;1981-12-03;950;30;
;7902;FORD;ANALYST;7566;1981-12-03;3000;20;
;7934;MILLER;CLERK;7782;1982-01-23;1300;10;

28 thoughts on “csv format with select *”

  1. Manish

    Awesome!

    I need to figure out how to write these reg.expressions. Any good pointers to books/sites?

  2. Glenn

    Very nice.. I guess I’ll to practice my RegEx again.

    Manish – I can tell you what NOT to do: Do not sit next to a RegEx expert. I did and now can’t remember mcuh about it. Anytime I needed to do something complex, I just asked for help (time was always lacking).

    Oracle docs should be enough. I don’t think you will need a book on this. Google RegEx syntax and/or examples should provide enough material.

    Good Luck

  3. Christian

    Hi,
    awesome!
    But I think there is a typo / cut’n paste bug in the regexp, it should probably be:
    regexp_replace(xml.column_value,’ *]*>[^>]*>’,’;’)

    Cheers
    Christian

  4. Christian

    Hi again,
    sorry, I noticed that somehow the stating pointed brackets got removed, as well as the ticks changed to the wrong type when submitting the comment.

    Cheers
    Christian

  5. Tom

    The row for King, who has a null MGR kind of breaks the format. Shouldn’t his line be

    ;7839;KING;PRESIDENT;;1981-11-17;5000;10;

  6. Sokrates

    create table temp(i number, v varchar2(100));

    insert into temp values(1, ‘hallo there’);

    select regexp_replace(column_value,’ *]*>[^>]*>’,’;’)
    from table(xmlsequence(cursor(select * from temp)));

    ;1;hallo there;

    REM nice

    insert into temp values(2, ‘oops, what”s that ?’);

    select regexp_replace(column_value,’ *]*>[^>]*>’,’;’)
    from table(xmlsequence(cursor(select * from temp)));

    ;1;hallo there;
    ;2;oops, what's that ?;

    REM oops, what’s that ?

  8. Jeff Smith

    A better trick with no fear of typos is ‘SELECT * FROM table’ in Toad and choose the ASCII delimited export format and choose your character of choice 🙂

  9. Paul Moore

    Would it be possible to use an XSL stylesheet to do the formatting, rather than a regexp_replace? It might avoid stuff like the “'” and empty element problems. Also it would be 9i compatible.

    Of course, it’s taking a huge sledgehammer to crack a nut, but hey…

    Maybe I’ll grab an XSLT book and see if I can work out how.

  10. Michael Moore

    Very nice! I just love slick solutions. It probably goes without saying that people should test the performance before using in a production application. I have found the performance of regexp to be very slow.

  11. Eriks

    Hmm

    SQL> SELECT COUNT (*)
    2 FROM (SELECT regexp_replace (column_value, ‘*]*>[^>]*>’, ‘;’)
    3 FROM TABLE (XMLSEQUENCE (CURSOR (SELECT *
    4 FROM all_objects))));
    FROM (SELECT regexp_replace (column_value, ‘*]*>[^>]*>’, ‘;’)
    *
    ERROR at line 2:
    ORA-03113: end-of-file on communication channel

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
    PL/SQL Release 10.2.0.1.0 – Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
    NLSRTL Version 10.2.0.1.0 – Production

  13. Michael Moore

    Eric,
    SELECT COUNT ( * )
    FROM ( SELECT REGEXP_REPLACE ( COLUMN_VALUE, ‘ *]*>[^>]*>’, ‘;’ )
    FROM TABLE ( XMLSEQUENCE ( CURSOR ( SELECT *
    FROM all_objects ) ) ) );
    this worked for me.
    Does your work if you remove the outer select?

  14. Hariharan Ragunathan

    Excellent solution. Very simple and effective way of using the existing function.

  16. Eriks

    That’s weird. It works OK for me without count(*), i.e. I get perfect results, but dies when I do select count(*). But who would need a count(*) anyway 🙂 Nice solution, thanks.

  18. Pingback: Pythian Group Blog » Log Buffer #43: a Carnival of the Vanities for DBAs

  19. Michael Moore

    Ok, I discovered something. If you are posting an example you can use <CODE> </CODE> . this will prevent single-quotes from turning into graves.
    As for the example, it will have a problem if a character string contains the delimiter and also, if a character string contains characters like less-than and greater-than and ampersand etc, XMLSEQUENCE will convert them to their symbolic form i.e < will convert to &lt;

  20. csf


    select regexp_replace(column_value,' *]*>[^>]*>',';')
    from table(xmlsequence(cursor(select * from emp)));

    it works fine & thanks.
    very helpful

  22. Pingback: Laurent Schneider » Blog Archive » csv with XML revisited…

  24. Pingback: Gehariharan Blog

  25. Pingback: csv format with select * « Murtuja’s Weblog

  26. Sachin

    My experience with Oracle XML has been that it tries to build the entire XML in memory before it spits out the result. I used to run into out of memory errors because of that. Can you confirm if that is the case over here too?

    I liked this csv gen script.

  28. Pingback: Log Buffer #43: A Carnival of the Vanities for DBAs

Comments are closed.