|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
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
I create another script, inc.sql, that includes this script twice...
... 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
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.
More on Oracle
This is an on Oracle article. The most current articles of this series can be found here.