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. Awesome!

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

  2. 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. 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. 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. 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. 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 ?

  7. 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 :)

  8. 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.

  9. 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.

  10. 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

  11. 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?

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

  13. 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.

  14. 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;

  15. 
    select regexp_replace(column_value,' *]*&gt;[^&gt;]*&gt;',';')
    from table(xmlsequence(cursor(select * from emp)));
    

    it works fine & thanks.
    very helpful

  16. Pingback: Gehariharan Blog
  17. 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.

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>