- 1 year ago
spool sql log with the date on name
To spool SQL log with the date on the name, you can use SQL*Plus, a command-line interface for Oracle Database, and include the date in the spool file name using the %DATE%
variable in Windows.
Here's how you can do it:
- Open a text editor (e.g., Notepad) and create a SQL script file, let's call it
myscript.sql
, with the SQL commands you want to execute and log.
sql-- myscript.sql
SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SPOOL output_%DATE:/=%_%TIME::=%.log
-- Your SQL commands here
SELECT * FROM my_table;
SPOOL OFF
In this example, the SPOOL
command is used to log the output of the SQL script to a file named output_<date>_<time>.log
. The %DATE:/=%
and %TIME::=%
parts in the file name are used to remove any characters that are not allowed in Windows file names.
Save the
myscript.sql
file in a location of your choice.Open a Command Prompt (cmd) or PowerShell window.
Navigate to the directory where the
myscript.sql
file is located using thecd
command.Start SQL*Plus and connect to your Oracle database:
bashsqlplus username/password@hostname:port/servicename
Replace username
, password
, hostname
, port
, and servicename
with your actual database connection details.
- Execute the SQL script and log the output:
bash@myscript.sql
The script will execute the SQL commands and log the output to a file with the date and time in the file name, such as output_20230728_105055.log
(assuming the date is July 28, 2023, and the time is 10:50:55 AM).
Please note that the behavior of the %DATE%
variable in Windows can vary depending on the system settings and user configuration. If needed, you can customize the date format in the file name using batch script techniques or other scripting languages to ensure a consistent and desired file name format.