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;
Awesome!
I need to figure out how to write these reg.expressions. Any good pointers to books/sites?
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
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
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
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;
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 ?
well, I have a problem with KING actually… looking into it !
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
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.
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.
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
@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
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?
Excellent solution. Very simple and effective way of using the existing function.
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
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.
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…
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 <
it works fine & thanks.
very helpful
Michael Moore
thx for the code hint
oops, what's that ?better I hope
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 , …
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.
cheers for this