Take care of a slash in a SQL script

Since long time i have almost been writing useless posts only. Now, i guess my blog doesn’t even look like an Oracle blog. So thought about posting something related to Oracle ;)

Day before yesterday a colleague at my workplace asked that she was running an SQL script (which contained a simple DBMS_MVIEW.REFRESH() statement to refresh an MVIEW), it ran successfully but after completion re-ran the last command run in the session. I was also puzzled and checked the SQL script but it contained simple DBMS_MVIEW.REFRESH() statement. Next try revealed that the script actually had a / (slash) in the second line (with no semi-colon at the end of the first line). Something like this (I used dbms_stats instead of dbms_mview):

[sql]exec dbms_stats.gather_table_stats(user,‘EMP’) /

[/sql]

Now this thing, when run in SQL* Plus session can be confusing:

[sql]SCOTT@TESTING > SCOTT@TESTING >delete emp1; delete emp1 * ERROR at line 1: ORA-00942: table or view does not exist

SCOTT@TESTING >@c:\test

PL/SQL procedure successfully completed.

delete emp1 * ERROR at line 1: ORA-00942: table or view does not exist

SCOTT@TESTING > [/sql]

There is no semicolon at the end of the first statement but it executes without that also. So the slash in the 2nd line simply re-executes the last SQL, as expected :) . But it does get confusing !

Comments

Comment by Surachart Opun on 2009-06-14 19:09:02 +0530

Thank You… for your idea. That’s good .

-> slash in a SQL script make so bad or sqlplus 😉

SQL> delete emp1;
delete emp1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> exec dbms_stats.gather_table_stats(user,’A’);

PL/SQL procedure successfully completed.

SQL> list
1* delete emp1
SQL>
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(user,’A’);
3 end;
4 /

PL/SQL procedure successfully completed.

SQL> list
1 begin
2 dbms_stats.gather_table_stats(user,’A’);
3* end;