3

While executing scripts in SQLPlus I've encountered a problem:

script.sql contains the following lines

@some_pkg.pks
@some_pkg.pkb

drop package some_pkg;
/

After calling

> sqlplus user/password@dbname @script.sql

the following messages are in console:

Package created.
Package body created.
Package dropped.

drop package some_pkg;
*
ERROR at line 1:
ORA-04043: object SOME_PKG does not exist

Please, explain what's happening here. Looks like the package is being dropped twice. Is it possible to avoid the error?

Kara
  • 6,115
  • 16
  • 50
  • 57
  • What is the content of some_pkg.pks and some_pkg.pkb? –  Jan 29 '15 at 08:23
  • 4
    That's because `/` runs the last statement again. Short answer: don't use `/` after a `drop` statement See: http://stackoverflow.com/a/10207695/330315 –  Jan 29 '15 at 08:25

2 Answers2

10

The rules of SQLplus command execution basically are:

  • Execute the current text when you encounter a semi-colon. Thus if a line doesn't end with a semi-colon, the current text continues to be collected.
  • If you encounter DECLARE or BEGIN, collect all the text and do not execute on semi-colons
  • If you encounter a slash (/), execute the collected text.

So what happens in your cases is, that both the semi-colon and the slash execute the DROP statements.

To fix it, remove the slash.

You only need the slash if you have a block of PL/SQL, which always with an END statement. Use semicolons for everything else.

Note: the above rules are simplified. It's more complex in practice.

Codo
  • 75,595
  • 17
  • 168
  • 206
  • 1
    Or remove the semi-colon... the slash on its own will still then execute the drop command in the buffer. (But be consistent in how you do this in your code.) – Alex Poole Jan 29 '15 at 08:36
  • 2
    Yes, I agree. But as a simple rule for writing consistent code and helping your eyes spot mistakes, I recommend the use the slash for PL/SQL blocks only and semi-colons otherwise. – Codo Jan 29 '15 at 08:39
1

Some examples will help to understand rules:

  1. Below code will be executed once
begin
  dbms_output.put_line('executed');
end;
/
  1. Below code will not execute (missing semicolon)
begin
  dbms_output.put_line('executed')
end
/
  1. Below code will be executed twice
begin
  dbms_output.put_line('executed');
end;
/
/
  1. Below code will be executed once
select 1 from dual
/
  1. Below code will be executed once
select 1 from dual;
  1. Below code will be executed twice
select 1 from dual;
/
rKow
  • 41
  • 5