Oracle: dynamically select script

Question

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;
/

Why are you running this within Oracle? Why not use a scheduler if it has to be a script? If it doesn't have to be a script place it in a package/procedure and schedule that within the database. In answer to your question you _can_ read it into a clob and use dbms_sql to execute... it seems like massive overkill though for what should be a simple procedure or a cron call. — Ben, May 14 '13 at 19:56
@Ben, this is a dev environment. We have hundreds of one line DDL scripts from an outside group that build a schema for validation testing of another software component. — ThomasMcLeod, May 14 '13 at 20:03
I still don't know why you need to do it in Oracle. If you've got hundreds then put them all in a single shell script and run that; you can execute them all with a single line with judicious use of `grep` and `awk` if you want... — Ben, May 14 '13 at 20:06
@Ben, currently the list of scripts are embedded with `@` 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. — ThomasMcLeod, May 14 '13 at 20:14
I still don't get what you want. How is the path/script name passed in? Where does the PL/SQL block fit in? Where is the list of scripts kept? Are you running one or many at once, and how do you want to deal with errors and failures? — Alex Poole, May 14 '13 at 20:35
@Alex, those are all good questions, and this is an ongoing development effort, so I tried to keep my question reasonably narrow. I have several hundred DDL script in multiple directories. But since there're DDL, it's necessary to execute them in correct order. So the long list is nested in another script, in the correct order. Hence, for this question I was simply looking for a way to substitute pathnames in the outer script. That's all. — ThomasMcLeod, May 14 '13 at 21:03
They're already in a script, as a list of `@script.sql` entries, but they don't have the paths to the scripts - so you effectively need to identify the correct directory for every acript and prepend it? That sounds more like a shell script question than an Oracle one; maybe you need to transform that before presenting it to SQL*Plus? Maybe a cut-down sample would help (me). — Alex Poole, May 14 '13 at 21:44
To those who would vote to close: The question itself is perfectly appropriate taken as is. Some of the comments are unnecessary to understand the question and leading to confusion. — ThomasMcLeod, May 15 '13 at 12:18

hol · Accepted Answer · 2013-05-14T20:57:24.830

2

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

http://docs.oracle.com/cd/E11882_01/server.112/e16604.pdf

edited May 14 '13 at 20:57

answered May 14 '13 at 20:30

hol

8,255
5
33
59

This doesn't technically answer the question... and if there are hundreds I suspect the OP might want to do it automagically, as per the comment _"So, I want to actually select the paths for nested scripts."_ – Ben May 14 '13 at 20:36
@Ben You are right, and I was checking out if there is any chance to mix SQLPLUS commands with PL/SQL but there is not. So I will clarify this point. – hol May 14 '13 at 20:46
Hmm, does `&1.` simply prepend the argument? I wonder about the extra `.` – ThomasMcLeod May 14 '13 at 21:16
1

@ThomasMcLeod - the `.` 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](http://stackoverflow.com/a/16516909/266304) :) – Alex Poole May 14 '13 at 21:38
his example will fly. see my answer below – Kirill Leontev May 15 '13 at 05:30
@be here now: Have a look again. Your example is different. The original script does create a pl/sql variable that only exists server sided and cannot be used in SQLplus. You are free to convince be otherwise. – hol May 15 '13 at 08:30

Kirill Leontev · Answer 2 · 2013-05-15T05:32:43.200

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>

score 0 · Answer 3 · answered May 14 '13 at 20:17

0

The way I do it on Windows is creating a BAT file containing:

sqlplus my_username/my_password@tns.test.company.com %*

Then from the command prompt, to execute the SQL-contents of a file, I just type:

batfile.bat @myfile.sql

answered May 14 '13 at 20:17

Ronnis

12,593
2
32
52

The OP is using Linux... but the same would work in a shell script. It also doesn't technically answer the question... and if there are hundreds I suspect the OP might want to do it automagically, as per the comment "So, I want to actually select the paths for nested scripts." – Ben just now edit – Ben May 14 '13 at 20:35
I just saw the Linux comment after I posted, but decided to leave my answer so someone else could steal the idea and give a better answer :) – Ronnis May 15 '13 at 06:53

score -1 · Answer 4 · answered May 15 '13 at 07:25

-1

The only way to run an executable from PL/SQL is to dynamically create a scheduler program of type EXECUTABLE and schedule a job.

answered May 15 '13 at 07:25

haki

9,389
15
62
110

Oracle: dynamically select script

4 Answers4