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):
exec dbms_stats.gather_table_stats(user,'EMP') /
Now this thing, when run in SQL* Plus session can be confusing:
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 >
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 !
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;