Conditionally calling sql scripts in sql plus

Question

I have two scripts which needs to be executed depending on whether a table exists or not in my database.

So I created a 3rd script as below which checks the condition and calls the respective script. [Because my installer cannot reach db and it can only call one script while installation]

declare
  cnt number;
begin
  select count(*) 
  into cnt
  from all_tables where table_name = 'VQ_REPORT_LAUNCHER';


if (cnt>0) then
  begin
    @VQ_Alter_Script.sql;
  end;
else
  begin
    @VQ_Create_Script.sql;
  end;
end if;

END;

I get the below error - ERROR at line 10: ORA-06550: line 10, column 1: PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

Note - When I execute my create/alter scripts directly from sql plus it works. Only when I try to execute them through a 3rd script using IF-ELSE , i get the above error in sql plus.

you cannot call sql scripts from PL/SQL without using a different approximation, like DBMS_SCHEDULER external API for SQL_SCRIPT job type — Roberto Hernandez
– Roberto Hernandez, Commented Sep 22, 2020 at 7:36
The script will be called from installer, We only have to check if the table exists and call another script accordingly. Cant that be done in .sql? — Nandu_Shilpa
– Nandu_Shilpa, Commented Sep 22, 2020 at 7:44

Alex Poole · Accepted Answer · 2020-09-22 08:04:07Z

You can use substitution variables to decide which script to run.

column script_name new_value script_name

select case count(*)
         when 0 then 'VQ_Create_Script.sql'
         else 'VQ_Alter_Script.sql'
       end as script_name
from all_tables
where table_name = 'VQ_REPORT_LAUNCHER';

@&script_name

or if only part of the name changes you could do:

column script_type new_value script_type

select case count(*) when 0 then 'Create' else 'Alter' end as script_type
from all_tables
where table_name = 'VQ_REPORT_LAUNCHER';

@VQ_&script_type._Script.sql

You can add settings like set termout off and set termout on around the query part to hide it if you want, and use set verify to decide whether to show the substitution happening.

Depending on which user you run this as, you might want to either check against user_tables rather than all_tables, or include the expected table owner as part of the filter, so you don't accidentally pick up a table with the same name in the wrong schema.

Collectives™ on Stack Overflow

Conditionally calling sql scripts in sql plus

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related