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

  (10 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

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

Indeed, thanks for sharing

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