March 15, 2008: On including files in SQL*Plus

In SQL*Plus, a script can be executed (or «included») using @ or @@. So, I might define a script that simply prints me hello world. I name that script phw.sql:

dbms_output.put_line('Hello world');

I create another script, inc.sql, that includes this script twice...

begin

  @@phw
  @@phw

end;
/

... and execute this script:

SQL> set serveroutput on size 100000 format wrapped
SQL> @D:\path\to\inc.sql
Hello world
Hello world

PL/SQL procedure successfully completed.

As expected, it prints Hello world twice.

I'd like to print any string, not just Hello world. So I create another script, print.sql, that uses the special &1:

dbms_output.put_line('&1');

And then, I create another script that includes print.sql twice:

begin
  
  @@print 'first line'
  @@print 'second line'

end;
/

SQL> set verify off
SQL> @D:\path\to\inc2.sql

second line
second line

Strangly, both lines print second line. It seems that this is the case because the substitution variables are substited when the buffer is run, not when it is entered. So, the &1 gets replaced with the last value for it, which happens to be second line

In order to achieve what I want, I have to use some magic. I create inc3.sql:

.
set sqlterminator off
set termout       off
spool             c:\temp\print.spooled
prompt    dbms_output.put_line('&1');
spool             off
set termout       on
set sqlterminator on
input
@@c:\temp\print.spooled

And the script that calls it:

begin
  
  @@print2 'first line'
  @@print2 'second line'

end;
/

Using the script:

SQL> @D:\path\to\inc3.sql

first line
second line

How does that work? First, I use the . (dot) to stop entering lines to the buffer. Then I turn off the sqlterminator. This is necessary because there are semicolons (;) in the scripts, and I don't want them to «believe» that they'd have to execute an SQL statement (so to speak). For cleanness, I also turn off termout because I don't want this script to print anything to my terminal. Then I use a spooled file (c:\temp\print.spooled) that will contain one single line, namely the one caused by the prompt command. spool is closed, termout and sqlterminator reset. input causes to start adding lines to the buffer again. Finally, I add the spooled file to the buffer and return to the script that called me.

This is an on Oracle article. The most current articles of this series can be found here.