Oracle Sqlplus calling sql script repeatedly in a loop

Question

Is it possible to call a script inside a loop via sqlplus?

Suppose we have two files simple_sample.sql and main.sql:

|simple_sample.sql|
   PROMPT HELLO &1

-------------------
|main.sql|
   begin
     for loop_parameter in 1..3 loop
       #magic_call#  @simple_sample.sql  loop_parameter
     end loop;
   end;
   /

What can be used instead of #magic_call# to obtain this output:

sqlplus user/password@schema @main.sql

HELLO 1
HELLO 2
HELLO 3

Won't your own answer here be helpful ? stackoverflow.com/a/30391533/7998591. Maybe you can run an execute immediate from the contents of the file in a loop? — Kaushik Nayak
– Kaushik Nayak, Commented Jul 24, 2018 at 5:38
@KaushikNayak, that trick can't help me in this case, because I need to execute an sqlplus script, which generally could containt non-SQL instructions. For instance, PROMPT will cause a syntax exception in execute immedite. — diziaq
– diziaq, Commented Jul 24, 2018 at 6:26
Ok, So why not a Shell script or use other programming languages ? I don't think there's much we could achieve from SQL* plus or PL/SQL for such user-interactive executions. It is understandable given the fact that pure database codes are primarily meant to run non-interactively to extract data from database. — Kaushik Nayak
– Kaushik Nayak, Commented Jul 24, 2018 at 6:45
I agree with @KaushikNayak. You need to write a shell script to handle the looping (instead of main.sql) and pass parameters to that SQL*Plus script. — APC
– APC, Commented Jul 24, 2018 at 6:59
@KaushikNayak,@APC, thank you for valuable advices. Actually, I'm fine with any answer and if in this case it's no way in sqlplus, then let it be. It was just interesting to find out if there's a possibility in sqlplus itself. — diziaq
– diziaq, Commented Jul 24, 2018 at 7:50

Dr Y Wit · Accepted Answer · 2018-07-25 01:56:05Z

===PL/SQL===

1.1 You can call another script from PL/SQL but it will be inlined in the code and must be correct PL/SQL snippet. So if we modify original scripts

simple_sample1.sql

dbms_output.put_line(&1);

main.sql

begin
  for loop_parameter in 1..3 loop
    @simple_sample1.sql loop_parameter
  end loop;
end;
/

Then result is following

SQL> @main
old   3: dbms_output.put_line(&1);
new   3: dbms_output.put_line(loop_parameter);
1
2
3

PL/SQL procedure successfully completed.

Of course, inlined code may look almost like standalone block if you wrap it with "begin" and "end".

begin
  dbms_output.put_line(&1);
end;

1.2 If you want to run arbitrary script from PL/SQL you can run sqlplus from PL/SQL code using DBMS_SCHEDULER... but it's a bit weird, isn't it? I hope it's needless to say that script will be executed in another session created by DBMS_SCHEDULER.

===SQL===

2. You can inline code into SQL as well, but, again, query must compile after inlining.

hello.sql
'HELLO' || ' ' ||

SQL> select
  2  @hello.sql
  3  rownum
  4  from dual
  5  connect by rownum <= 3;
HELLO 1
HELLO 2
HELLO 3

SQL>
SQL> select
  2  #START hello.sql
  3  rownum
  4  from dual
  5  connect by rownum <= 3;
HELLO 1
HELLO 2
HELLO 3

===SPOOL===

3. Finally, you can generate in a loop what you need, spool it and execute it. You can use either SQL or PL/SQL to generate the script. Example below shows SQL approach.

main_spool.sql

set echo off;
set pagesize 0;

spool tmp.sql
select
'@simple_sample.sql' || ' ' || rownum x
from dual
connect by rownum <= 3;
spool off
@tmp.sql

SQL> @main_spool
@simple_sample.sql 1
@simple_sample.sql 2
@simple_sample.sql 3

HELLO 1
HELLO 2
HELLO 3

Thank you for your efforts. I find your answer extremely wholesome.

Collectives™ on Stack Overflow

Oracle Sqlplus calling sql script repeatedly in a loop

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related