SPOOLING TO A DYNAMICALLY GENERATED SPOOL FILE NAME
QUESTIONS
How to create a spool file with the date appended to file name?
Is it possible to change the spool file name dynamically?
Is it possible to change the spool file name dynamically?
REQUEST
I want to produce a spool file with a name generated during runtime.
SOLUTION
Create a substitution variable (NEW_VALUE SPOOL_FILE) and store the query result in it
SQL> COLUMN SPOOL_FILE NEW_VALUE SPOOL_FILE NOPRINT
SQL> SELECT UPPER(INSTANCE_NAME) || '_' || HOST_NAME || '_' || TO_CHAR(SYSDATE,'DDMONYYYY_HH24MISS') || '.txt' SPOOL_FILE FROM V$INSTANCE;
SQL> PROMPT &SPOOL_FILE
DBPILOT_dbpilot.net_19JAN2018_140848.txt
SQL> SPOOL &SPOOL_FILE
SQL> SELECT SYSDATE FROM DUAL;
SQL> SPOOL OFF
Confirm that the file is created and contains the query output
SQL> !ls
DBPILOT_dbpilot.net_19JAN2018_140848.txt
SQL> !cat DBPILOT_dbpilot.net_19JAN2018_140848.txt
SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
------------------
19-JAN-18
SQL> SPOOL OFF
REFERENCES
Oracle SQL*Plus Substitution Variables
How to store database columns as variables in SQL*Plus
Version : 14:12 19.01.2018
Database : Oracle database 10G – 12cR2
Database : Oracle database 10G – 12cR2
Tags In
Related Posts
1 Comment
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Certificates
Categories
- Linux (8)
- Iptables (2)
- Memory Usage (1)
- Postfix (1)
- Yum (4)
- Networker (2)
- Oracle (63)
- Account (2)
- AWR (2)
- Cipher Suites (1)
- Database Errors (5)
- Database Performance (9)
- Datapump (3)
- Enterprise Manager (24)
- Enterprise Manager Metrics (4)
- Environments (4)
- High CPU Usage (2)
- High IO Load (4)
- Initialization Parameters (5)
- job_queue_processes (1)
- Processes (3)
- NLS (1)
- Objects (1)
- Objects DDL (2)
- OJVM/PSU/DRU Patches (1)
- PL/SQL (5)
- UTL_FILE (4)
- Prerequisites (1)
- Profile (1)
- Queries (2)
- Redologs (1)
- Resource Limits (1)
- Segment Shrink (1)
- Session (6)
- Spfile (3)
- SQL PLUS (1)
- Spooling (1)
- Standard Queries (7)
- Statistics (2)
- Temporary Tablespace (2)
Tags
/etc/sudoers
Account
Agent
Agent 12c Deinstall
Agent 13c Deployment
Automated Maintenance Tasks
AWR
BLOCKING SESSION
Database Auditing
Database Directory
Database Performance
Datapump
DBMS_SYSTEM
Enterprise Manager
Enterprise Manager Metrics
Failed Logins (Historical) Metric
Failed Logins Metric
Gather Statistics Job
GET_ENV
High IO Load
Idle Sessions
Initialization Parameters
LOCKED(TIMED)
OEM
OEM 12.1.0.4
OEM 13.1.0.0.0
OMS
Processes
Repository
Retrieve Object DDL
ROW LOCK
Scheduler
Schema Statistics
Scripts
Sessions
spfile
SQL_ID
Standard Query
TRANSACTION
User Grants
UTL_FILE
WAITING SESSION
Window Group
Window Name
Yum
It helped me a lot in resolving the reinstallation of installed agent home.
Thank you somuch.