Re: Oracle embedding TABS in output?

"Kevin Changet" <changet_at_mindspring.com> wrote in message news:a5fdd0ee.0307161505.6e5188e0_at_posting.google.com...
> When calling sqlplus from a Unix script, I get embedded tabs in my
> output.
> <SCRIPT/>
>
> SQLSTRING="SET FEEDBACK OFF\n SET PAGESIZE 0\n SET COLSEP \'\'\n SET
> WRAP OFF\n SET TRIMOUT OFF\n SET LINESIZE 35\n"
>

You might want to add SET PAGESIZE 0 (so no column heading) ,SET LINESIZE 300 (so the long line will be ok). Those are option for SQL*Plus, the docs for it is easy to find. You can make pretty good report within SQL*Plus, you can even set specific format and differents names for your columns like

COLUMN the_name_of_col_in_sql HEADING "New name" FORMAT A20

But the point is not to use raw output of SQL*Plus, but use the SPOOL option instead.

 SPOOL /foo/foo.txt

your query goes here;

SPOOL OFF and the file /foo/foo.txt contain the results of the query formatted with the options that you have SET before...

Good luck.

For the rest i think you should use the SPOOL command before performing your query. You can also format your

> SQL="SELECT LPAD(CUST_NUM,5,'0'), RPAD(CUST_DESCRIPTION,30,' ') from
> CUSTOMERS order by 1,2;"
>
> SQLSTRING="$SQLSTRING $SQL"
>
> echo $SQLSTRING | sqlplus -s $DB_LOGON >> $OUTPUT_FILE
>
> </SCRIPT>
>
> A) The output comes up like the following (the periods represent
> spaces, the ~ tabs):
>
> 00001MAX'S.WONDERFULL.WORLD........
> 00002ATTN:~.WHAT.IS.THIS...........
> 00003TIME..TO........FIND..........
>
> B) When I change the "SET COLSEP |\n" in SQLSTRING, I get the
> following:
>
> 00001|MAX'S.WONDERFULL.WORLD........
> 00002|ATTN:...WHAT.IS.THIS..........
> 00003|TIME..TO........FIND..........
>
> C) When I remove the "SET COLSEP \'\'\n" from SQLSTRING, I get the
> following:
>
> 00001 MAX'S.WONDERFULL.WORLD........
> 00002 ATTN:...WHAT.IS.THIS..........
> 00003 TIME..TO........FIND..........
>
> D) When I remove the "SET COLSEP \'\'\n" from SQLSTRING AND change the
> SQL to
> SQL="SELECT LPAD(CUST_NUM,5,'0')||RPAD(CUST_DESCRIPTION,30,' ') from
> CUSTOMERS order by CUST_NUM, CUST_DESCRIPTION;" I still get the
> following:
>
> 00001MAX'S.WONDERFULL.WORLD........
> 00002ATTN:~.WHAT.IS.THIS...........
> 00003TIME..TO........FIND..........
>
> Things to note:
> 1) When I rerun the job, the TAB character consistently gets placed in
> the same spot.
> 2) Sometimes it replaces two spaces, sometimes three.
> 3) I'd rather not do post processing as it's more resource consuming.
> 4) Using SQL*Plus: Release 9.2.0.1.0
> 5) I don't think that the DB contains the TAB in the description. If
> it did, wouldn't it show up in B or C?
> 6) I'm trying to recreate a fixed length flatfile.
> 7) Lastly, it's not always just after a ":" , sometimes it is just the
> middle of 5(or 6, or 7,...) spaces.
>
> Has anybody seen this issue before? Can anybody recreate this? Any
> suggestions of a workaround (other than post processing)?
>
> Thanks for any help or suggestions.
>
> Kevin Changet
Received on Thu Jul 17 2003 - 04:30:30 CEST