oracle - 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?

Answer 1

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:empmyfile.sql
select module from v$session where sid = sys_context('USERENV','SID');
SQL> @x:empmyfile.sql

MODULE
----------------------------------------------------------------
SQL*Plus

SQL> SET APPINFO ON
SQL> @x:empmyfile.sql

MODULE
----------------------------------------------------------------
01@ x:empmyfile.sql

That will also work with SQLcl.

I haven't used TOAD for a while but I think it also supports appinfo