René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

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:
phw.sql
dbms_output.put_line('Hello world');
I create another script, inc.sql, that includes this script twice...
inc.sql
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:
print.sql
dbms_output.put_line('&1');
And then, I create another script that includes print.sql twice:
inc2.sql
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:
print2.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:
inc3.sql
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.

More on Oracle

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