Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: oracle table to flat file?

Re: oracle table to flat file?

From: Joel Garry <joel-garry_at_home.com>
Date: 4 Aug 2004 11:44:54 -0700
Message-ID: <91884734.0408041044.5f8ec205@posting.google.com>


"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<411020f1$0$25458$afc38c87_at_news.optusnet.com.au>...
> "Chris" <cs123._no_spam__at_telstra.com> wrote in message
> news:zfTPc.31685$K53.7730_at_news-server.bigpond.net.au...
>
> > > > set colsep ','
> > > > select * from emp;
> > > >
> > >
> > > Nice try, but the output's quite different, and there's a danger with
> colsep
> > > that you'll embedd trailing spaces into your data.
> > >
> > > Regards
> > > HJR
> > >
> > >
> > How so?
>
>
>
> Well, I'm not sure the topic is worth this much discussion, but you could do
> this little test to see what I mean:
>
> SQL> set colsep ','
> SQL> select empno,ename,sal from scott.emp;
>
> EMPNO,ENAME , SAL
> ----------,----------,----------
> 7369,SMITH , 800
> 7499,ALLEN , 1600
> 7521,WARD , 1250
> 7566,JONES , 2975
> 7654,MARTIN , 1250
> 7698,BLAKE , 2850
> 7782,CLARK , 2450
> 7788,SCOTT , 3000
> 7839,KING , 5000
> 7844,TURNER , 1500
> 7876,ADAMS , 1100
> 7900,JAMES , 950
> 7902,FORD , 3000
> 7934,MILLER , 1300
>
> 14 rows selected.
>
> Compare that with:
> SQL> set colsep " "
> SQL> select empno ||','||ename||','||sal from scott.emp;
>
> EMPNO||','||ENAME||','||SAL
> ----------------------------------------------------------------------------
> ------------
> 7369,SMITH,800
> 7499,ALLEN,1600
> 7521,WARD,1250
> 7566,JONES,2975
> 7654,MARTIN,1250
> 7698,BLAKE,2850
> 7782,CLARK,2450
> 7788,SCOTT,3000
> 7839,KING,5000
> 7844,TURNER,1500
> 7876,ADAMS,1100
> 7900,JAMES,950
> 7902,FORD,3000
> 7934,MILLER,1300
>
> 14 rows selected.
>
> You might note that the ENAME field, as an example, is padded with trailing
> spaces in the first example, and not in the second. A padding which makes
> its way into the final spool file, too. And the SAL field is padded with
> *leading* spaces in the first example, and not in the second.
>
> And yes, as Joel points out, if you have data such as "45, Acacia Avenue",
> then the comma in the data is going to screw things up. That can be coped
> with using "my" method (you just construct a subtler, cleverer select
> string), but using the colsep method, it's gonna screw things up without
> recourse.
>
> For quick and dirty, colsep wins. I wasn't trying to knock it or you. But
> for complete, subtle control over how one's data is exported, you can do
> better.

Don't take it personal, Chris, but I was trying to knock colsep. Any quick and dirty solution is going to have to cope with ampersands and commas and anything you can set colsep to, at least, and 8-bit characters and who-knows-what at worst. So you wind up eating dirt. Even old solid tools like exp can be befuddled by the odd stuff - I had a scare once when an import stripped the bit off a string that was actually being used as a numeric counter by a strange app.

So I do another silly thing, and concatenate in separators that are _extremely_ unlikely to be in _my_ data, like @@@@@, and use awk and sed to get it in whatever format I want. Try putting some bizarro data in an Excel spreadsheet and export it to csv file and load it with sql*loader and then go the other way to get handy with the gotchas involved.

And of course it is silly, because I actually do have some data that has @@@@@ in it, and didn't even notice it until I wrote this. It wasn't something that would have shot me in the foot until I did this on 1000 tables, but there you go. "Howard's way" is better, and the topic of Q&D is worth discussion.

jg

--
@home.com is bogus.
Committed to protecting your privacy: 
http://www.odci.gov/cia/notices.html#priv
Received on Wed Aug 04 2004 - 13:44:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US