How do I run a script in SQLPlus whose pathname is passed in a string. Something like the following (fails):
SET SERVEROUTPUT ON;
DECLARE
path VARCHAR2(128);
BEGIN
path := '<runtime_path>' || 'test_script.sql';
dbms_output.put_line(path);
@ path;
END;
/
You can pass a parameter to SQLPlus but you cannot mix PL/SQL with SQLPlus commands. So your example will not fly. So you might need to wrap it with a shell script. But as I look at your PL/SQL routine you just try to add the path. Maybe this is possible though.
Call sqlplus like this
sqlplus user/password@database @genericscript.sql path
And then within genericscript.sql
SET SERVEROUTPUT ON
start &1.myscript.sql
quit
In my example the myscript.sql has this content
select 'hello welt' from dual;
So I got the following output from SQLPlus
[oracle@localhost sqp]$ sqlplus user/pw@db @genericscript.sql /home/oracle/sqp/
SQL*Plus: Release 11.2.0.2.0 Production on Tue May 14 22:53:15 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
'HELLOWELT
----------
hello welt
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Hope it helps. You can have a look at the SQLPLUS reference manual.
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_five.htm#autoId13
I prefer the PDF
&1. simply prepend the argument? I wonder about the extra .
. just delmits the substitution variable, so SQL*Plus knows that the 1 is the variable and the myscript.sql is a fixed string. Otherwise if you had &1myscript.sql it would be ambiguous whether everything after the & was a variable name. This makes more sense with a named, rather than positional, variable. (See also this recent answer :)
One option:
D:\>type run.sql
col p new_value path noprint
select '&1.' as p from dual
/
@&path
D:\>type run1.sql
select 'This is run1' from dual
/
D:\>sqlplus hr/hr@sandbox @run.sql d:\run1.sql
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 15 13:24:22 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
Elapsed: 00:00:00.03
'THISISRUN1'
------------
This is run1
Elapsed: 00:00:00.01
And you can inline scripts in anonymous blocks too, very convenient:
D:\>sqlplus hr/hr@sandbox @parent.sql d:\child.sql
SQL*Plus: Release 11.2.0.1.0 Production on Wed May 15 13:31:46 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
currently spooling to S:\spool\sandbox\20130515_1331_HR_33.log
this is child script called from parent's anonymous plsql block
child's name was passed using command line parameter
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
13:31:47 HR@sandbox> get parent
1 begin
2 @&1.
3* end;
13:31:48 4 .
13:31:48 HR@sandbox> get child
1 dbms_output.put_line('this is child script called from parent''s anonymous plsql block');
2* dbms_output.put_line('child''s name was passed using command line parameter');
13:31:50 HR@sandbox>
The way I do it on Windows is creating a BAT file containing:
sqlplus my_username/[email protected] %*
Then from the command prompt, to execute the SQL-contents of a file, I just type:
batfile.bat @myfile.sql
The only way to run an executable from PL/SQL is to dynamically create a scheduler program of type EXECUTABLE and schedule a job.
grepandawkif you want...@in front inside another sql script that has error reporting and other common setup. So, I want to actually select the paths for nested scripts.