SPOOL

-----How to Pass UNIX Variable to SPOOL Command (Doc ID 1029440.6)
setenv只有csh才有不行啊

PROBLEM DESCRIPTION:
====================

You would like to put a file name in Unix and have SQL*Plus read that file name, 
instead of hardcoding it, because it will change.

You want to pass a Unix variable on the command line like:

   SQL*Plus -s <user>/<password>@test.sql $SPOOL

and have the $SPOOL value be passed into existing SQL.



SOLUTION DESCRIPTION:
=====================

This syntax will not work because everything after the word 'SQL*Plus' is taken 
as arguments passed in to SQL*Plus, and SQL*Plus does not expand 
(or even recognize) the shell variable.  

One way around this is to set the SPOOL variable just prior to running the 
script.  For example:

set the SPOOL variable with this line:

   setenv SPOOL /u02/usrname/spoolfile.


Then you create a file called testfile.sql that contained the lines:

   spool $SPOOL;
   select user from dual;
   select count(*) from dba_tables;


And you then type 'SQL*Plus -s <user>/<password> @testfile.sql'.
The $SPOOL variable will be expanded inside the file to generate the file 
/u02/usrname/spoolfile.lst for the session.  Once exported, shell variables can 
be referenced one level down from their defining shell.

-------generate a grant script:

spool generate_sql.sql
set long 9999999
set header off

SQL> select 'GRANT READ, SELECT on ' || owner || '.' || table_name || ' to <user_name>;' from dba_tables;   -- Replace <user_name> with the user you want to give permissions to.

spool off

---------------set 这些如果直接执行不行------------

SQL> set NEWPAGE 0
SQL> set SPACE 0
SQL> set LINESIZE 80
SQL> set PAGESIZE 0
SQL> set ECHO OFF
SQL> set FEEDBACK OFF
SQL> set HEADING OFF
SQL> spool report.txt
SQL> select sysdate from dual; 
10-JUN-24
SQL> spool off
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
[oracle@rac1 ~]$ cat report.txt
SQL> select sysdate from dual;
10-JUN-24                                                                       
SQL> spool off
 

goal: How To Create a flat file Without Showing Statement Or ''Spool Off''
fact: SQL*Plus

fix:

Create a script, e.g. report.sql, with the following contents:
set NEWPAGE 0
set SPACE 0
set LINESIZE 80
set PAGESIZE 0
set ECHO OFF
set FEEDBACK OFF
set HEADING OFF
spool report.txt
select sysdate from dual;     <--- your SQL statement here
spool off

Run the script from SQL*Plus:
SQL> @report.sql

-----spool 两个..

Case 1
------
In SQL*Plus, how do you spool data to a file with a
.TXT file extension?

When you issue the following line of code:

     SPOOL myfile

this automatically spools data to MYFILE.LST.
How do you spool to MYFILE.TXT?


Case 2
------
You are spooling to a filename that the user passes in as
a substitution variable.  How do you append a .TXT file
extension, as opposed to the default .LST, to the 
filename?

Sample script:

     SPOOL &&filename
     SELECT * FROM dept
     /
     SPOOL OFF

If you enter MYFILE for filename, MYFILE.LST is the default
spooled filename.  How do you spool to MYFILE.TXT?


Solution Description:
=====================

Case 1
------
Specify the .TXT extension after the filename:

     SPOOL myfile.txt


Case 2
------
If you are spooling to a substitution variable,
specify "..txt" after the substitution variable.

     SPOOL &&filename..txt
     SELECT * FROM dept
     /
     SPOOL OFF

If you enter MYFILE for filename, this stores data into the
MYFILE.TXT file.

Make sure to append 2 periods ("..") to the substitution variable.
If you only include 1 period and enter MYFILE for filename,
this stores data into the MYFILETXT.LST file.


 

-----------------------加上Oracle SID

How is "@" interpretted when used in spooled file name?
========================================================
When you spool output to file with "@" included in the file name, the "@" is 
replaced with ORACLE_SID value.  

-rw-r--r--. 1 oracle oinstall      500 Jun 10 13:30 TESTcdb1..txt
[oracle@rac1 ~]$ cat testfile1.sql
   spool  &&filename@..txt            ---------------这里不能两个.
   select user from dual;
   select count(*) from dba_tables;
[oracle@rac1 ~]$ cat TESTcdb1..txt

USER                                                                            
--------------------------------------------------------------------------------
SYS                                                                             

  COUNT(*)                                                                      
----------                                                                      
      2202                                                                      

SQL> exit

-rw-r--r--. 1 oracle oinstall       88 Jun 10 13:31 testfile1.sql
-rw-r--r--. 1 oracle oinstall      496 Jun 10 13:31 T11cdb1.txt
[oracle@rac1 ~]$ cat T11cdb1.txt

USER                                                                            
--------------------------------------------------------------------------------
SYS                                                                             

  COUNT(*)                                                                      
----------                                                                      
      2202                                                                      

SQL> -----这里为什么没有exit 因为我用ctrl D退出的
[oracle@rac1 ~]$ cat  testfile1.sql
   spool  &&filename@.txt
   select user from dual;
   select count(*) from dba_tables;
 

----How can you generate a spool file format c:\temp\Overnight_13FEB06.log?

SOLUTION

Sample script:

COLUMN SPOOL_DATE NEW_VALUE FILE_DATE
COLUMN SPOOL_PREFIX NEW_VALUE FILE_PREFIX
COLUMN SPOOL_SUFFIX NEW_VALUE FILE_SUFFIX
SELECT
TO_CHAR(SYSDATE,'YYYYMMDD') SPOOL_DATE,
'c:\temp\Overnight_' SPOOL_PREFIX,
'.log' SPOOL_SUFFIX
FROM
DUAL
/
SPOOL &FILE_PREFIX.&FILE_DATE.&FILE_SUFFIX
/
report
/
SPOOL OFF

[oracle@rac1 ~]$ cat  spooldate.sql
COLUMN SPOOL_DATE NEW_VALUE FILE_DATE
COLUMN SPOOL_PREFIX NEW_VALUE FILE_PREFIX
COLUMN SPOOL_SUFFIX NEW_VALUE FILE_SUFFIX
SELECT
TO_CHAR(SYSDATE,'YYYYMMDD') SPOOL_DATE,
'/tmp/Overnight_' SPOOL_PREFIX,
'.log' SPOOL_SUFFIX
FROM
DUAL
/
SPOOL &FILE_PREFIX.&FILE_DATE.&FILE_SUFFIX
/
report
/
SPOOL OFF
[oracle@rac1 ~]$ 

------How to change spool file name dynamically ?

Each time query executes, the same file should not be overwritten.
It would be helpful in situations where an output of a table is 
monitored at periodic intervals to find the difference.


Solution Description
--------------------
COLUMN command with NEW_VALUE can be used to accomplish this.
Have the following three lines before the query -

column col1 new_value filename; 
select to_char(sysdate,'DDMONYYHH24MI') col1 from dual; 
spool &&filename..txt 

select .... ; -- original query 
spool off; 


Spool file name will contain the date and time so that they are not
overwritten.



(NOTE: In the example above, user details / company name / address / email / telephone number represent a fictitious sample (based upon made up data used in the Oracle Demos).  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.)

------------------  script to automatically label each spool filename with a timestamp of the date and time of the script execution.

 

Background 

If you must execute a single SQL*Plus script several times (for example because you run a report every week or when running a script in batch mode regularly), and the scriptspools the output to a file, you may want to give a unique name to the spool file for each execution.

Script

The following SQL*Plus script illustrates the timestamp method:
 

column timecol new_value timestamp
select to_char(sysdate,'.MMDDYY_HHMISS') timecol
from sys.dual
/
spool output_&&timestamp
select sysdate from sys.dual
/
spool off

If this script is executed at 12:34:12 AM on October 17, 1999, the output would be spooled to an output file with the name 'output.101799_123412'. You can modify the date format mask ('.MMDDYY_HHMISS') and/or the constant filename portion ('output_') of the example to create different spool filename formats.

Depending on the operating system the spool file will have the suffix .LIS or .LST appended to it's name by default. To replace the default ending with your own, for example '.txt', change the script like suggested below.
 

column timecol new_value timestamp
column spool_extension new_value suffix
select to_char(sysdate,'_MMDDYY_HHMISS') timecol,
'.txt' spool_extension
from sys.dual
/
spool output_&&timestamp&&suffix
select sysdate from sys.dual
/
spool off

You can take this even a step further and give the user of your script control over the file extension of the spool file. For example on UNIX make files can process files of a designated extension. On Microsoft Windows on the other hand most file extensions are associated with certain defined actions when you for example 'Open' or 'Edit' a file.

Replace the literal '.txt' with a variable to let the user of your script decide on an file extension that is useful for them.
 

select to_char(sysdate,'_MMDDYY_HHMISS') timecol,
'.'||&file_ending spool_extension

Please note, this last option is not useful for batch files.

------------------

How to generate sql/select statement from a select statement?


Solution Description
--------------------
This sample will show how to generate a select statement from
a select statement. For example, how to dynamically create a
sql script which contains a set of select statements based on 
column values from the EMP table. Specifically, how to create
a set of select statements based on each deptno value in EMP 
and job='CLERK'?

How to create this script?
==========================
[Sample script based on demo table EMP]

1. The following is an example of how you can dynamically create select
   statements based on column values in table. For example, how to generate
   select statements based on each deptno value and job='CLERK' in EMP table.   

Use the following code to create a SQL script (This includes comments 
for better documentation):

*****************************************

-- Given demo table EMP.
-- The following select statement will generate a sql file with
   select statements based on each department number (deptno) and
   job = 'CLERK'

set heading off
set feedback off
set echo off
spool temp.sql

select distinct 'select empno, ename, job from emp where deptno='||
deptno
||' and job = '||''''||
JOB
||''''||';' query_statement
from emp
where job = 'CLERK'
/
spool off

*****************************************

Result
------
Executing the above script will generate temp.sql file.

The temp.sql file contains: 
select empno, ename, job from emp where deptno=10 and job = 'CLERK';
select empno, ename, job from emp where deptno=20 and job = 'CLERK';
select empno, ename, job from emp where deptno=30 and job = 'CLERK';


You can now execute temp.sql to run the dynamically generated select statements.

------------------spool  excel format

Use SQL*Plus SPOOL command to generate an .xlsx output file.  When attempting to open this output file in MS Excel, it fails with the following error message:

Excel cannot open the file 'spooled_output.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.
  [OK]

STEPS:           

The issue can be reproduced at will when executing the following sample script in SQL*Plus:

set sqlblanklines off
set feedback off
set verify off
set heading off
set linesize 800
set pagesize 400
set echo off
set TERMOUT off
set colsep ,
set newpage none
spool /tmp/spooled_output.xlsx
select * from emp;
spool off

Then, attempt to open /tmp/spooled_output.xlsx in MS Excel.
It will fail with the reported error above.

CHANGES

Generating .xlsx output files.

CAUSE

This is a MS Excel issue, not SQL*Plus.  The SQL*Plus spool file is just a plain text file. The file extension is irrelevant. 
 

SOLUTION

Do not use .xlsx as a file extension when using SPOOL command in SQL*Plus. 

As a Workaround:
      Use .xls or .csv file extension.

Also, starting in SQL*Plus 12.2.0.1.0, there is a new "SET MARKUP CSV" which produces output in csv format.

For Example:

SQL> set markup csv on
SQL> select * from emp;

"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
7369,"<NAME1>","CLERK",7902,"17-DEC-80",800,,20
7499,"<NAME2>","SALESMAN",7698,"20-FEB-81",1600,300,30
7521,"<NAME3>","SALESMAN",7698,"22-FEB-81",1250,500,30
7566,"<NAME4>","MANAGER",7839,"02-APR-81",2975,,20
7654,"<NAME5>","SALESMAN",7698,"28-SEP-81",1250,1400,30
7698,"<NAME6>","MANAGER",7839,"01-MAY-81",2850,,30
7782,"<NAME7>","MANAGER",7839,"09-JUN-81",2450,,10
7788,"<NAME8>","ANALYST",7566,"19-APR-87",3000,,20
7839,"<NAME9>","PRESIDENT",,"17-NOV-81",5000,,10
7844,"<NAME10>","SALESMAN",7698,"08-SEP-81",1500,0,30
7876,"<NAME11>","CLERK",7788,"23-MAY-87",1100,,20
7900,"<NAME12>","CLERK",7698,"03-DEC-81",950,,30
7902,"<NAME13>","ANALYST",7566,"03-DEC-81",3000,,20
7934,"<NAME14>","CLERK",7782,"23-JAN-82",1300,,10

---------------2 spool output (Excel .csv files)

How to generate a delimiter in spool output (Excel .csv files)?

SOLUTION

1) In order to generate a delimited file output, you need to concatenate columns using the desired delimiter i.e. comma:

Example:
 

select empno|| ','||ename||'&'||mgr from X;

2) Other option is using:
 

SQL> set colsep ','
SQL> spool <DIRECTORY>/testexcel.csv
SQL> select * from emp;

3) Change some of the default SQL*Plus parameters, that will be garbage for Excel:
 

feedback=off
newpage=none
termout=off
heading=off

4) If some columns are empty, be aware to include the delimiter, too:
 

nvl(to_char(col2),',')

NOTE: If the data contain comma's as well and needs to be preserved then, the csv data needs to be encapsulated within double quotes. Example:
 

select '"' || name || '"' || ',' || '"' || department || '"' from mytable;