I'm trying to run a series of script files but I would like for each script file to print its filename, for reporting.
So, the intent is to call each SQL file from a general script:
@SQL_File_1;
@SQL_File_2;
@SQL_File_n;
But I need each SQL to print it's results, so I need each to print:
DBMS_OUTPUT.PUT_LINE({Filename} || ' updated ' || {Number of records});
How to retrieve the filename? Can it be easily done?
If you are running SQL*Plus, then it can be done as per the comment but only if you've set APPINFO, eg
SQL> select module from v$session where sid = sys_context('USERENV','SID');
MODULE
----------------------------------------------------------------
SQL*Plus
SQL> host cat x:\temp\myfile.sql
select module from v$session where sid = sys_context('USERENV','SID');
SQL> @x:\temp\myfile.sql
MODULE
----------------------------------------------------------------
SQL*Plus
SQL> SET APPINFO ON
SQL> @x:\temp\myfile.sql
MODULE
----------------------------------------------------------------
01@ x:\temp\myfile.sql
That will also work with SQLcl.
I haven't used TOAD for a while but I think it also supports appinfo
The utility executes all sql files in the specified directory and complements the calls to sql files with prompt commands to display the sql file name.
For example input file
C:\upwork\powershell-oracle_git\sql
31.12.2020 11:42 28 SQL_FILE1.sql
31.12.2020 11:42 28 SQL_FILE2.sql
31.12.2020 11:43 28 SQL_FILE3.sql
31.12.2020 11:43 28 SQL_FILE4.sql
31.12.2020 11:43 28 SQL_FILE5.sql
For example output log
SET session NLS_LANG: AMERICAN_AMERICA.CL8MSWIN1251
===========================================================================================
Script start time : 2020-12-31 11:50:11
Found SQL file C:\upwork\powershell-oracle_git\sql\SQL_FILE1.sql
Found SQL file C:\upwork\powershell-oracle_git\sql\SQL_FILE2.sql
Found SQL file C:\upwork\powershell-oracle_git\sql\SQL_FILE3.sql
Found SQL file C:\upwork\powershell-oracle_git\sql\SQL_FILE4.sql
Found SQL file C:\upwork\powershell-oracle_git\sql\SQL_FILE5.sql
-------------------------------------------------------------------------------------------
For example output sqlplus short log, if set termout OFF
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 31 11:48:58 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
Session altered.
Elapsed: 00:00:00.00
SQL>
Session altered.
Elapsed: 00:00:00.00
SQL> SQL> SQL> Start script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE1.sql
SQL> SQL> Stop script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE1.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE2.sql
SQL> SQL> Stop script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE2.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE3.sql
SQL> SQL> Stop script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE3.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE4.sql
SQL> SQL> Stop script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE4.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE5.sql
SQL> SQL> Stop script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE5.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
For example sqlplus output full log, if set termout ON
SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 31 11:50:12 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
Session altered.
Elapsed: 00:00:00.00
SQL>
Session altered.
Elapsed: 00:00:00.00
SQL> SQL> SQL> Start script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE1.sql
SQL> SQL> select 1, sysdate from dual;
1 SYSDATE
---------- -------------------
1 31.12.2020 11:50:09
1 row selected.
Elapsed: 00:00:00.00
SQL> Stop script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE1.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE2.sql
SQL> SQL> select 2, sysdate from dual;
2 SYSDATE
---------- -------------------
2 31.12.2020 11:50:09
1 row selected.
Elapsed: 00:00:00.00
SQL> Stop script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE2.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE3.sql
SQL> SQL> select 3, sysdate from dual;
3 SYSDATE
---------- -------------------
3 31.12.2020 11:50:09
1 row selected.
Elapsed: 00:00:00.00
SQL> Stop script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE3.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE4.sql
SQL> SQL> select 4, sysdate from dual;
4 SYSDATE
---------- -------------------
4 31.12.2020 11:50:09
1 row selected.
Elapsed: 00:00:00.00
SQL> Stop script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE4.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> Start script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE5.sql
SQL> SQL> select 5, sysdate from dual;
5 SYSDATE
---------- -------------------
5 31.12.2020 11:50:09
1 row selected.
Elapsed: 00:00:00.00
SQL> Stop script: @C:\upwork\powershell-oracle_git\sql\SQL_FILE5.sql
SQL> SQL> > --------------------------------------------------------------------------------------------------------
SQL> SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
This powershell script adds sqlplus command PROMPT sql file names to sql file before call sql script.
<# .SYNOPSIS
This script adds PROMPT sql file names to sql file before call sql script.
Author: Dmitry Demin dmitrydemin1973@gmail.com
.DESCRIPTION
This script adds PROMPT sql file names to sql file before call sql script.
.PARAMETER sql_file_input
Specify the input sql script.
.PARAMETER sql_file_output
Specify the output sql script.
.PARAMETER log_file
Specify the log file.
.EXAMPLE
This script adds PROMPT sql file names to sql file before call sql script.
.\add_prompt_file_names.ps1 -sql_file_input .\sql\start.sql -sql_file_output .\sql\start_prompt.sql -log_file log_file.log
#>
param(
[string]$sql_file_input="C:\upwork\powershell-oracle_git\sql\start.sql",
[string]$sql_file_output="C:\upwork\powershell-oracle_git\sql\start_prompt.sql",
[string]$log_file="log_add_prompt.log"
)
$upper_line = "PROMPT ""Start script: "
$bottom_line = "PROMPT ""---------------------------------------------------------------------------------------------------------"""
$date_time_start = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
Write-host "Script start time : $date_time_start "
try
{
echo "Script start time : $date_time_start ">>$log_file
}
catch {
Write-Host "Log File $log_file. Other type of error was found:"
Write-Host "Exception type is $($_.Exception.GetType().Name)"
exit
}
echo "===========================================================================================" | tee-object -Append -filepath $log_file
echo "Input sql file: $sql_file_input" | tee-object -Append -filepath $log_file
echo "Output sql file: $sql_file_output" | tee-object -Append -filepath $log_file
$data_file = Get-Content $sql_file_input
$null | Set-Content -Path $sql_file_output
foreach ($line_file in $data_file)
{
if ($line_file.TrimStart().StartsWith("@"))
{
$start_prompt_line= $upper_line + $line_file.TrimStart().replace("@","") + """"
Out-File -filepath $sql_file_output -append -inputobject $start_prompt_line -encoding default
Out-File -filepath $sql_file_output -append -inputobject $line_file.TrimStart() -encoding default
Out-File -filepath $sql_file_output -append -inputobject $bottom_line -encoding default
}
else
{
Out-File -filepath $sql_file_output -append -inputobject $line_file -encoding default
}
}
For example
C:\upwork\powershell-oracle_git>powershell .\add_prompt_file_names.ps1 -sql_file_input ./sql/start.sql -sql_file_output ./sql/start_output.sql
Script start time : 2021-01-03 16:32:41
============================================================================
Input sql file: ./sql/start.sql
Output sql file: ./sql/start_output.sql
Input file ./sql/start.sql
REM
REM
REM
---
rem start 1
@SQL_FILE1.sql
rem start 2
@SQL_FILE2.sql
rem start 3
@SQL_FILE3.sql
rem start 4
@SQL_FILE4.sql
rem start 5
@SQL_FILE5.sql
REM
REM
---
Output sql file: ./sql/start_output.sql
REM
REM
REM
---
rem start 1
PROMPT "Start script: SQL_FILE1.sql"
@SQL_FILE1.sql
PROMPT "---------------------------------------------------------------------------------------------------------"
rem start 2
PROMPT "Start script: SQL_FILE2.sql"
@SQL_FILE2.sql
PROMPT "---------------------------------------------------------------------------------------------------------"
rem start 3
PROMPT "Start script: SQL_FILE3.sql"
@SQL_FILE3.sql
PROMPT "---------------------------------------------------------------------------------------------------------"
rem start 4
PROMPT "Start script: SQL_FILE4.sql"
@SQL_FILE4.sql
PROMPT "---------------------------------------------------------------------------------------------------------"
rem start 5
PROMPT "Start script: SQL_FILE5.sql"
@SQL_FILE5.sql
PROMPT "---------------------------------------------------------------------------------------------------------"
REM
REM
---