0

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
Improve this question
6
  • 5
    shutdown immediate is not a plsql command. It's a sqlplus command. Commented Sep 26, 2016 at 11:32
  • 1
    I don't think you can use both shut down and startup in a same plsql block. From your code,once the execution will shutdown immediate, the whole DB will closed. Statements after that won;t execute at all. Commented Sep 26, 2016 at 11:34
  • @XING I had no issues to run the script without the BEGIN and END block and without the if condition. So maybe the issue is as Rene pointed is incorrect syntax. But is there any way to choose to execute these statments based on a flag? Commented Sep 26, 2016 at 13:37
  • I guess Rene gave you the answer with flag..Check out his solution. Also as mentioned earlier when you executed individually, its treated as sqplus command statement and would had excuted but in block it failed Commented Sep 26, 2016 at 13:38
  • PL/SQL executes entirely inside the database, so there is no way it can shut itself down and then start itself up. That'll be one reason why PL/SQL has no shutdown command. Commented Sep 26, 2016 at 13:43

1 Answer 1

Reset to default
2

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
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thank you for your answer, however it is not clear for me. How do I select the script name? I am not understanding the decode example either.
Personally I've never scripted database shutdowns. So I'm not sure if you are solving the right problem.
It is for a rollback script in archiving process where we try to drop some users we created for the process. Most of the scenarios we do not expect any user to be active hence the masterKey will be zero and we do not need to shutdown and restart in restricted mode. However if the operator wants to enforce that, we allow him to set the flag masterkey to 1 so the db is shutdown and all users are dropped without any problem.
Another approach would be to kill that users sessions. stackoverflow.com/questions/15664924/…
@Xing the new script edit you have provided works great. Thank you for the updated script.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.