Oracle SQL script spool &1

If i have 3 sql scripts and if i dump output in spool &1 in first sql script do i have to dump second sql script output in spool &2.. i am trying to getting all those output in another cshell script.. can some one e开发者_如何学Goxplain me how does spool &1 works

The &1 is a positional parameter. Using spool &1 means the output will go to a filename which you pass in as the first argument on the command line

If your csh script calls three SQL scripts and they all contain spools, they should each refer to &1, the second one does not use &2. (As long as the file name is the first argument in each case). And the 'counter' is reset to 1 for each SQL*Plus session. So if you have SQL scripts called query1.sql, query2.sql and query3.sql, your csh script might look something like:

#!/bin/csh
sqlplus -s / @query1 output_file_1
sqlplus -s / @query2 output_file_2
sqlplus -s / @query3 output_file_3

Each of the SQL scripts will contain spool &1, and the output will go to different files. You can then refer to the output files elsewhere in the same csh script of you want.

As Robert mentions in the documentation he quotes, if you want all the output to go to the same file, you'd need to pass the same file name to all three sqlplus commands and make the second and third spool commands have the APP[END] argument.

Asuming you have Version 10g or higher you have the following options:

SQL> spool name_of_file
SQL> spool name_of_file off
SQL> spool name_of_file out
SQL> spool name_of_file create
SQL> spool name_of_file append
SQL> spool name_of_file replace

Do not underestimate the power of oracles fine online documentation:

CRE[ATE]
Creates a new file with the name specified. 
REP[LACE]
Replaces the contents of an existing file. If the file does not exist, REPLACE creates the 
file. This is the default behavior.
APP[END]
Adds the contents of the buffer to the end of the file you specify.
OFF
Stops spooling.
OUT
Stops spooling and sends the file to your computer's standard (default) printer. This 
option is not available on some operating systems.
Enter SPOOL with no clauses to list the current spooling status