Exporting SQL output to text file 1

[skapsak]

Without programming, can I create a .buf file to run a routine to do a select statement and export it to text or spreadsheet format file for further use?&nbsp;&nbsp;I tried using spool but am doing something wrong.<br><br>thanks

 

[MikeLacey]

What DB? Oracle? Access?<br><br>Mike <p>Mike Lacey<br><a href=mailto:Mike_Lacey@Cargill.Com>Mike_Lacey@Cargill.Com</a><br><a href=

http://www.cargill.com/>

Cargill's Corporate Web Site</a><br>

 

[skapsak]

Mike,<br><br>Our MIS department uses an Oracle database.

 

[carp]

Spooling normally works fine.&nbsp;&nbsp;If you will post your script it will make it much easier to help you.

 

[skapsak]

This is what is contained in the buffer.&nbsp;&nbsp;I just run it from the SQL&gt; prompt and <br>i get the following error:<br><br>ERROR at line 5:ORA-00933: SQL command not properly ended<br><br>select costcode,fundcode,program,objectcode,project,<br>earnings,retirement,retirecredit,fica,insurance,health,ltdisab,<br>downdate from ops$vdhperts.payroll where costcode='532' <br>order by costcode, fundcode<br>spool c:\temp\output.txt<br>/

 

[carp]

OK, try this:<br><br>spool c:\temp\output.txt<br>SELECT .......(the rest of your query here);<br>spool off<br>

 

[skapsak]

I tried the following and still got an error:<br><br>SQL&gt; run<br>&nbsp;&nbsp;1&nbsp;&nbsp;spool c:\temp\output.txt<br>&nbsp;&nbsp;2&nbsp;&nbsp;select costcode,fundcode,program,objectcode,project,<br>&nbsp;&nbsp;3&nbsp;&nbsp;earnings,retirement,retirecredit,fica,insurance,health,ltdisab,<br>&nbsp;&nbsp;4&nbsp;&nbsp;downdate from ops$vdhperts.payroll where costcode='532'<br>&nbsp;&nbsp;5&nbsp;&nbsp;order by costcode, fundcode<br>&nbsp;&nbsp;6* spool off<br>spool c:\temp\output.txt<br>*<br>ERROR at line 1:<br>ORA-00900: invalid SQL statement

 

[carp]

You need to add a semicolon after your query.

 

[skapsak]

I tried that and it still gave me an error.&nbsp;&nbsp;Is &quot;Spool&quot; a command you can use from the buffer or does it have to be within the context of a SQL file?&nbsp;&nbsp;<br><br>Any other ways you can think of to get the data and send it out to a text file from the SQL&gt; prompt?

 

[carp]

skapsak -<br>You put your finger right on the problem!&nbsp;&nbsp;I missed the part about this being in your buffer!<br>SPOOL is a SQL*Plus command, so it never goes into your command line buffer - that's reserved for SQL commands.<br>So there's two ways to skin this cat (Mike - that's an Americanism for solving the problem):<br><br>1.&nbsp;&nbsp;From your command line:<br><br>SQL&gt; spool c:\temp\myfile.txt<br>SQL&gt; /<br>SQL&gt; spool off<br><br>This will turn on spooling, run the SQL in your buffer, then turn spooling off.<br><br>OR<br><br>Put the spooling commands along with your SQL in a file and run the file as a script:<br><br>spool c:\temp\myfile.txt<br>SELECT the_rest_of_your_query_here;<br>spool off<br><br>SQL&gt; @my_script_file<br><br>Sorry for being so slow on the uptake here!

 

[skapsak]

I had just figured that out before I got notification that you guys figured it out.&nbsp;&nbsp;Thanks for your help.&nbsp;&nbsp;This is a great service for all of us who are novices.<br><br>Thanks

 

[skapsak]

thanks for your help...