Home » SQL & PL/SQL » Client Tools » Spooling problem (SQL*Plus)
Spooling problem [message #335772] Wed, 23 July 2008 12:48 Go to next message
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?
Re: Spooling problem [message #335774 is a reply to message #335772] Wed, 23 July 2008 12:59 Go to previous messageGo to next message
joy_division
Messages: 4641
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
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
icon14.gif  Re: Spooling problem [message #335776 is a reply to message #335774] Wed, 23 July 2008 13:03 Go to previous message
jacovdh
Messages: 10
Registered: June 2008
Location: Sao Paulo
Junior Member

Thanks joy_division

That solved the problem.

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


Current Time: Wed Dec 07 10:32:00 CST 2016

Total time taken to generate the page: 0.09772 seconds