OraFAQ Forum: Client Tools » Spooling problem

Home » Other » Client Tools » Spooling problem (SQL*Plus)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Spooling problem [message #335772]

Wed, 23 July 2008 12:48

jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo

Junior Member

Hi there

I am having some problems trying to run a spool command.

I am trying to run the following .sql file:

set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool H:\Jaco\fxcash.csv
SELECT vfx.Agencia
       ||','
       ||vfx.Conta
       ||','
       ||vfx.Swift
       ||','
       ||vfx.Titular
       ||','
       ||vfx.Titularn
       ||','
       ||vfx.Data
       ||','
       ||vfx.ValorMe
       ||','
       ||vfx.Valormn
       ||','
       ||vfx.Historico
       ||','
       ||vfx.Documento
       ||','
       ||vfx.rEgistro
       ||','
       ||vfx.sequencia
       ||','
       ||vfx.FormAmn
       ||','
       ||vfx.BoleTo
FROM   vfx_std_temp_Cash_File_Sap vfx;
spool off

the result in SQL*Plus is as follows:

SQL> 
  1  set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
  2  spool H:\Jaco\fxcash.csv 
  3  SELECT vfx.Agencia
  4         ||','
  5         ||vfx.Conta
  6         ||','
  7         ||vfx.Swift
  8         ||','
  9         ||vfx.Titular
 10         ||','
 11         ||vfx.Titularn
 12         ||','
 13         ||vfx.Data
 14         ||','
 15         ||vfx.ValorMe
 16         ||','
 17         ||vfx.Valormn
 18         ||','
 19         ||vfx.Historico
 20         ||','
 21         ||vfx.Documento
 22         ||','
 23         ||vfx.rEgistro
 24         ||','
 25         ||vfx.sequencia
 26         ||','
 27         ||vfx.FormAmn
 28         ||','
 29         ||vfx.BoleTo
 30  FROM   vfx_std_temp_Cash_File_Sap vfx;
 31* spool off
 32  ;
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
    *
ERROR at line 1:
ORA-00922: missing or invalid option


SQL>

I have tried running just the select statement included in the spool file, and that is working fine - see below:

SQL> SELECT vfx.Agencia
  2         ||','
  3         ||vfx.Conta
  4         ||','
  5         ||vfx.Swift
  6         ||','
  7         ||vfx.Titular
  8         ||','
  9         ||vfx.Titularn
 10         ||','
 11         ||vfx.Data
 12         ||','
 13         ||vfx.ValorMe
 14         ||','
 15         ||vfx.Valormn
 16         ||','
 17         ||vfx.Historico
 18         ||','
 19         ||vfx.Documento
 20         ||','
 21         ||vfx.rEgistro
 22         ||','
 23         ||vfx.sequencia
 24         ||','
 25         ||vfx.FormAmn
 26         ||','
 27         ||vfx.BoleTo
 28  FROM   vfx_std_temp_Cash_File_Sap vfx;

VFX.AGENCIA||','||VFX.CONTA||','||VFX.SWIFT||','||VFX.TITULAR||','||VFX.TITULARN
--------------------------------------------------------------------------------
1,11520000000,USD,1,60701190052759,14-APR-04,5000000,14429500,LIQUIDACAO DO CONT
RATO NR.    ,04/738          ,11009,2,LDL,2517

1,11520000000,USD,1,60701190052759,14-APR-04,5000000,14429500,LIQUIDACAO DO CONT
RATO NR.    ,04/739          ,11013,2,LDL,2518

1,11520000000,USD,1,1701201000189,14-APR-04,1000000,2890000,LIQUIDACAO DO CONTRA
TO NR.    ,04/740          ,11017,2,LDL,2508

1,11520000000,USD,1,401340,14-APR-04,1000000,2883000,LIQUIDACAO DO CONTRATO NR.
   ,04/741          ,11021,2,LDL,2511

etc...

38 rows selected.

SQL>

Any ideas on why it doesn't work once I put it into a spool file?

Report message to a moderator

Re: Spooling problem [message #335774 is a reply to message #335772]

Wed, 23 July 2008 12:59

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

set and spool are spl*plus commands, not sql, so you will have to issue a START or @ command of the .sql file, not a get.

[Updated on: Wed, 23 July 2008 13:01]

Report message to a moderator

Re: Spooling problem [message #335775 is a reply to message #335772]

Wed, 23 July 2008 13:02

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You didn't post what is really in your script file.
There is no line 32 ";" in the script you posted, how is it possible it is in the spool?

Did you post the whole file?

Regards
Michel

Report message to a moderator

Re: Spooling problem [message #335776 is a reply to message #335774]

Wed, 23 July 2008 13:03

jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo

Junior Member

Thanks joy_division

That solved the problem.

Report message to a moderator

Previous Topic:	trimspool and linesize query
Next Topic:	Oracle changes to reflect in EA

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 10:18:37 CDT 2024