Skip to Main Content
  • Questions

  • How to call external sql script from within a PL/SQL block

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Peter.

Asked: October 12, 2004 - 4:27 pm UTC

Last updated: November 16, 2022 - 11:12 am UTC

Version: 9.2.0.5

Viewed 10K+ times! This question is

You Asked

Hi Tom,

This is probably super simple once you show me how, but I haven't been able to find the answer in the documentation.

How do I call an external SQL script from within PL/SQL?

I know I can embed the actual SQL text inside the PL/SQL block, but I would rather keep the SQLs in a separate file to be called externally when I need to outside the PL/SQL code.

E.g.

BEGIN
-- Check for some condition.
-- if condition true then
-- execute foo.sql
END;
/

In SQL*PLUS, we execute external scripts using one @, e.g. @foo.sql.

And within a script, we execute other scripts with two @, e.g. @@bar.sql.

Not sure how to do the same within a PL/SQL block.

Thanks,
-Peter

and Tom said...

You cannot.

PLSQL is a language compiled into the database, running on the server, without access to YOUR file system (it is running on another machine, elsewhere in the network)

SQLPLUS is a command line scripting tool, that runs on YOUR client, has access only to YOUR file system.


You can 'achieve' this by having a 'null.sql' that does nothing in sqlplus

Consider:

-----------------------------------------------------------
host echo prompt I do nothing > null.sql
host echo prompt I do something > foo.sql

variable script varchar2(50)
column script new_val SCRIPT_TO_RUN

begin
if (1=1)
then
:script := 'foo.sql';
else
:script := 'null.sql';
end if;
end;
/
select :script script from dual;
@&SCRIPT_TO_RUN


begin
if (1=0)
then
:script := 'foo.sql';
else
:script := 'null.sql';
end if;
end;
/
select :script script from dual;
@&SCRIPT_TO_RUN

-------------------------------------------------------------

when that is run:

ops$tkyte@ORA9IR2> host echo prompt I do nothing > null.sql

ops$tkyte@ORA9IR2> host echo prompt I do something > foo.sql

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable script varchar2(50)
ops$tkyte@ORA9IR2> column script new_val SCRIPT_TO_RUN
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
2 if (1=1)
3 then
4 :script := 'foo.sql';
5 else
6 :script := 'null.sql';
7 end if;
8 end;
9 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select :script script from dual;

SCRIPT
-------------------------------------------------------------------------------
foo.sql

ops$tkyte@ORA9IR2> @&SCRIPT_TO_RUN
ops$tkyte@ORA9IR2> prompt I do something
I do something
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
2 if (1=0)
3 then
4 :script := 'foo.sql';
5 else
6 :script := 'null.sql';
7 end if;
8 end;
9 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select :script script from dual;

SCRIPT
-------------------------------------------------------------------------------
null.sql

ops$tkyte@ORA9IR2> @&SCRIPT_TO_RUN
ops$tkyte@ORA9IR2> prompt I do nothing
I do nothing
ops$tkyte@ORA9IR2>



it'll either run null.sql or foo.sql



Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Nice example.

A reader, October 13, 2004 - 10:14 am UTC


Great example!!!

Peter Tran, October 13, 2004 - 10:55 am UTC

As always, thanks for making it clear with an example.

Regards,
-Peter

Perfect

A reader, November 08, 2004 - 11:42 am UTC

A simple example that shows exactly how to do what you wanted

Great !!!!!

A reader, April 17, 2005 - 1:36 am UTC


Very good example

Hossein Alaei Bavil, May 04, 2005 - 6:34 am UTC


Excellent-- Just what I wanted to know!

A reader, July 07, 2005 - 8:12 pm UTC

thanks

Thanks!!

Hans Suvaal, October 05, 2005 - 5:03 am UTC


good work

mmdeo, March 09, 2006 - 12:21 am UTC

nice example

Oracle sql Call another sql files from if

Karoly, August 26, 2022 - 11:37 am UTC

Better mode:

col script_to_run new_value script_to_run

define call_function = 'y'
select case when upper(trim('$call_function')) in ('I','Y') then 'function.sql'
else 'skip.sql' end script_to_run
from dual;
prompt $script_to_run begin
@$script_to_run
prompt $script_to_run end

Connor McDonald
August 30, 2022 - 1:30 am UTC

Nice stuff.

Also conditional compilation is a candidate if you are running just PLSQL blocks

Best advice on how to move sql scripts with variable substitution

alexandre guerra, November 15, 2022 - 4:32 pm UTC

There's since v12, a way to use oracle scheduler to call a sql script

https://oracle-base.com/articles/12c/scheduler-enhancements-12cr1
EXTERNAL_SCRIPT
SQL_SCRIPT

https://community.oracle.com/tech/developers/discussion/3578459/executing-an-sql-script-from-a-stored-procedure/p2

Chris Saxon
November 16, 2022 - 11:12 am UTC

Indeed, thanks for sharing
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library