How to call the filename of running SQL script?

Question

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?

Easily? No, as far as I can tell. See this OraFAQ Forum discussion: orafaq.com/forum/t/62944/0 — Littlefoot
– Littlefoot, Commented Dec 30, 2020 at 19:25
According to that, the information is stored in a package using Module parameter. He just used a function to convert it & get the filename only. But I get “SQL*Plus” or “Toad (version)” instead; not sure why I don't get the current file directory. — Felipe Vidal
– Felipe Vidal, Commented Dec 30, 2020 at 19:59
From my point of view, the simplest way is to hard-code script name into the script. — Littlefoot
– Littlefoot, Commented Dec 30, 2020 at 20:31

Connor McDonald · Accepted Answer · 2020-12-31 01:18:10Z

2

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

answered Dec 31, 2020 at 1:18

Connor McDonald

11.8k1 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Felipe Vidal Over a year ago

Thanks! It seems I can work with this, at least using SQLPlus. In Toad it never changed "Toad (version)", but it's better anyway.

Felipe Vidal Over a year ago

Just a heads up, this worked. & with the correct formatting this is gold. Thanks!

Cary Millsap Over a year ago

On my VirtualBox VM instance running Oracle Database 19.3, there's no @ symbol after the nesting level in the output. I wonder whether that's purely a software version issue, or if there's something else more interesting going on.

Cary Millsap Over a year ago

And the answer is: SQL*Plus sets module using the format %02d@ %s, whereas SQLcl uses the format %02d %s (no @ symbol).

Dmitry Demin · Accepted Answer · 2020-12-31 05:05:10Z

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

Thanks. I don't think in a controlled environment in wihch, for example, a DBA Team this would work though. Usually instructions for groups like that are "run SQL", so if I came with different code, I would probably get into troubles making it work.

Dmitry Demin · Accepted Answer · 2021-01-03 09:47:25Z

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 [email protected]

    .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
---

Collectives™ on Stack Overflow

How to call the filename of running SQL script?

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related