CODEWITHSUNDEEP
sqloracle-databaseplsqlsyntax-errorsqlplus
Prasanna Narayanan
Prasanna Narayanan

Reputation: 439

Script Error in Oracle SQL

I have a sql file which has the following statments:

BEGIN
if (&&masterKey = 1) then
shutdown immediate;
startup restrict;
end if;
END;
/

In a different SQL file (defineVariables.sql) I have declared the variable masterKey.

DEFINE masterKey = 0;

and imported that sql here using

@defineVariables.sql

While I execute the script I get the following error. I am not sure if its because I use the shutdown statement? Can someone please help me with this query?

Error Message:

SQL> BEGIN
  2  if (&&masterKey = 1) then
  3  shutdown immediate;
  4  startup restrict;
  5  end if;
  6  END;
  7  /
old   2: if (&&masterKey = 1) then
new   2: if (0 = 1) then
shutdown immediate;
         *
ERROR at line 3:
ORA-06550: line 3, column 10:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the
following:
:= . ( @ % ;
ORA-06550: line 5, column 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior

Upvotes: 0

Views: 672

Answers (1)

Rene
Rene

Reputation: 10541

You cannot do this in PLSQL since "shutdown immediate" is not a PLSQL or SQL command but a SQLplus command. One way of achieving a conditional execution of scripts is described in the answer to this question: SQLplus decode to execute scripts

Basically, depending on the value of your masterkey you select one of two script names and subsequently execute the script with that name.

Based on the code from previous example.

  sql>  variable flag varchar2(7);
  sql>  exec :flag := '&&masterKey';
  sql>  column our_script new_value script noprint;
  sql>  select decode(:flag, '1', 
                   'c:\sqlplus\shutdown_script.sql', 
                   'c:\sqlplus\do_not_shutdown.sql'
                   ) our_script
    from dual;

Execution 

sql> @&script;

shutdown_script would be

prompt shutting down
shutdown immediate;

do_not_shutdown script would be

prompt Not shutting down

Upvotes: 2

Related Questions