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;
May 3rd, 2007 at 14:45
Awesome!
I need to figure out how to write these reg.expressions. Any good pointers to books/sites?
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
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
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
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;
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 ?
May 3rd, 2007 at 15:51
well, I have a problem with KING actually… looking into it !
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
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.
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.
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
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
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?
May 4th, 2007 at 07:04
Excellent solution. Very simple and effective way of using the existing function.
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
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.
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…
May 4th, 2007 at 19:07
[…] Turning to Oracle, Laurent Schneider has a useful trick for csv format with select * using regular expressions. […]
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 <
May 5th, 2007 at 05:20
it works fine & thanks.
very helpful
May 5th, 2007 at 21:31
Michael Moore
thx for the code hint
oops, what's that ?better I hope
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 […]
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 , …
June 1st, 2007 at 07:23
Simple But Powerful Solution
Generating CSV files from tables is a task which we do many a times
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, […]
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.