CODEWITHSUNDEEP
sqloracle-databasecmdsqlplus
Nandu_Shilpa
Nandu_Shilpa

Reputation: 13

Conditionally calling sql scripts in sql plus

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.

Upvotes: 1

Views: 2599

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

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.

Upvotes: 2

Related Questions