Laurent Schneider

Put your code in <code> and </code> tags

26 Responses to “csv format with select *”

1. Manish Says:
   May 3rd, 2007 at 14:45

   Awesome!

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

2. Glenn Says:
   May 3rd, 2007 at 14:51

   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 Says:
   May 3rd, 2007 at 14:59

   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 Says:
   May 3rd, 2007 at 15:04

   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 Says:
   May 3rd, 2007 at 15:25

   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 Says:
   May 3rd, 2007 at 15:34

   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. Laurent Schneider Says:
   May 3rd, 2007 at 15:51

   well, I have a problem with KING actually… looking into it !

8. Jeff Smith Says:
   May 3rd, 2007 at 16:28

   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 Says:
   May 3rd, 2007 at 17:46

   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 Says:
    May 3rd, 2007 at 19:07

    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 Says:
    May 3rd, 2007 at 21:23

    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

12. alastair Says:
    May 3rd, 2007 at 22:23

    @Manish
    Great book by Jeffrey Friedl:
    http://www.amazon.com/Mastering-Regular-Expressions-Jeffrey-Friedl/dp/0596528124/ref=pd_bbs_sr_1/002-3113404-6276813?ie=UTF8&s=books&qid=1178227302&sr=8-1

13. Michael Moore Says:
    May 4th, 2007 at 00:32

    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 Says:
    May 4th, 2007 at 07:04

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

15. Laurent Schneider Says:
    May 4th, 2007 at 08:14

    Paul, you can find a 9i solution from Michaels on http://forums.oracle.com/forums/thread.jspa?forumID=75&threadID=503433#1821051

    does not help King much

16. Eriks Says:
    May 4th, 2007 at 09:05

    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.

17. Tyler Says:
    May 4th, 2007 at 17:27

    I’m a HUGE fan of RegexCoach:
    http://weitz.de/regex-coach/
    It shows you the text your expression matches in real time.
    RegexBuddy (www.regexbuddy.com) also looks interesting, though it’s not free…

18. Pythian Group Blog » Log Buffer #43: a Carnival of the Vanities for DBAs Says:
    May 4th, 2007 at 19:07

    […] Turning to Oracle, Laurent Schneider has a useful trick for csv format with select * using regular expressions. […]

19. Michael Moore Says:
    May 4th, 2007 at 22:44

    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 Says:
    May 5th, 2007 at 05:20

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

    it works fine & thanks.
    very helpful

21. Sokrates Says:
    May 5th, 2007 at 21:31

    Michael Moore

    thx for the code hint

    oops, what's that ?

    better I hope

22. Laurent Schneider » Blog Archive » csv with XML revisited… Says:
    May 7th, 2007 at 12:33

    […] Special thanks to Tom for pointing and Michaels for fixing the missing manager of King in my previous post : csv format with select […]

23. Laurent Schneider Says:
    May 7th, 2007 at 12:38

    check my rewritten post at
    http://laurentschneider.com/wordpress/2007/05/csv-with-xml-revisited.html
    and sorry for the wordpress annoyances with back quotes , single quotes , …

24. Gehariharan Blog Says:
    June 1st, 2007 at 07:23

    Simple But Powerful Solution

    Generating CSV files from tables is a task which we do many a times

25. csv format with select * « Murtuja’s Weblog Says:
    September 26th, 2007 at 05:37

    […] csv format with select * Hi, Here you have both - XML extraction to generate CSV: http://laurentschneider.com/wordpress/2007/05/csv-format-with-select.html Regards, […]

26. Sachin Says:
    February 29th, 2008 at 02:05

    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

Use <code> and </code> to post code

Name

Mail (will not be published)

Website

Spam protection: Sum of Eight + Nine ?