Re: oracle table to flat file?

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

Regards
HJR Received on Tue Aug 03 2004 - 18:34:52 CDT