Search code examples
oracle-databasesqlplussqlcl

SQL command to print .SQL filename

Is there any SQL command to print the name of the .SQL file that is currently being executed in the sqlplus prompt?

There's is a Unix way to do it. But I am looking for a valid SQL command that displays the current filename.

Solution

  • There's not a proper command to do this but great ( and obvious ) ER for sqlcl

    There's a couple ways to achieve this today. The first is to add code into every script to use dbms_application_info and set the module/action to the name of the process and script. This will work with any tool as it's putting the info into the db for retrieval later via sql/plsql. It could also be handy as this is captured into things like ASH/AWR/v$session.

    There's another sqlcl specific way which is the script command to get access to the context running and print it. Here's an example of both options>>

    SQL> !cat k.sql
--- generic 
exec DBMS_APPLICATION_INFO.SET_MODULE('InstallScript','k.sql');


select   sys_context('USERENV', 'ACTION'), sys_context('USERENV', 'MODULE') from dual;

-- sqlcl specific.....
script
ctx.write(ctx.getLastUrl() + "\n");
/

SQL> @k.sql

PL/SQL procedure successfully completed.

   SYS_CONTEXT('USERENV','ACTION')    SYS_CONTEXT('USERENV','MODULE')
__________________________________ __________________________________
k.sql                              InstallScript


file:/Users/klrice/k.sql
SQL>