CODEWITHSUNDEEP
arraysfor-loopplsqlscriptingplsqldeveloper
Albert Rafalski
Albert Rafalski

Reputation: 19

How to run sql script in plsql by @'path?

I would like to run two scripts test1.sql and test2.sql one after the other which look like that:

test_1: select * from hr fetch first 10 rows only;

test_2: select * from sys fetch first 10 rows only;

in PL/SQL code.

In the table PROCEDURE_NAMES there are two columns: pr_index and script names. It looks like that:

column_name data_type nullable

pr_index integer no script_name varchar2(100) yes

Data:

pr_index | script_name

1 | test1.sql

2 | test2.sql

I've tried like that:

declare
    v_path varchar2(15) := 'C:\UserData\HP';
    v_name_proc varchar2(100);
    v_path_and_name_proc := varchar2(100);
begin
    for i in 1..2 loop
    select script_name
    into v_name_proc
    from procedure_names
    where pr_index = i;

    v_path_and_name := v_path || v_name_proc;  
    @v_path_and_name;

   end loop;
end;

but I've got error ORA-06550 in 13 line

PLS-00103: pls-00103 encountered the symbol @ when expecting one of the following: ( begin case declare end exit for goto if loop mod null pragma rise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array )

I have tried change 13 line to:

@ || v_path_and_name;

exec @v_path_and_name;

execute immediate @v_path_and_name;

but it doesn't work :(

How should I change it? I need to run both scripts one after the other.

Upvotes: 0

Views: 49

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18705

pl/sql cannot run client side scripts. It's executed on the server. You can however execute an anonymous block to set a variable and reuse that variable in sqlplus. Make it a 2 step process. (1) Generate a generic script that points to your script (2) run that generic script.

18c>create table t_script (script VARCHAR2(100));

Table T_SCRIPT created.

18c>insert into t_script(script) values ('script1.sql');

1 row inserted.

script1.sql contains

select 'path1' as path,'script1' as script from dual;

first script:

variable l_path varchar2(100);
begin
:l_path := 'path1';
end;
/
set head off
spool tmp_script.sql
select '@'||:l_path||'\'||script from t_script;
spool off

Then run the generated script:

18c>@tmp_script.sql

path1 script1

afaik you cannot do this in a single process - others might know how to do that.

Upvotes: 0

Related Questions