When do I need to use a semicolon vs a slash in Oracle SQL?

by

I know this is an old thread, but I just stumbled upon it and I feel this has not been explained completely.

There is a huge difference in SQL*Plus between the meaning of a / and a ; because they work differently.

The ; ends a SQL statement, whereas the / executes whatever is in the current “buffer”. So when you use a ; and a / the statement is actually executed twice.

You can easily see that using a / after running a statement:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:37:20 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> drop table foo;

Table dropped.

SQL> /
drop table foo
           *
ERROR at line 1:
ORA-00942: table or view does not exist

In this case one actually notices the error.

But assuming there is a SQL script like this:

drop table foo;
/

And this is run from within SQL*Plus then this will be very confusing:

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:38:05 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> @drop

Table dropped.

drop table foo
           *
ERROR at line 1:
ORA-00942: table or view does not exist

The / is mainly required in order to run statements that have embedded ; like CREATE PROCEDURE,CREATE FUNCTION,CREATE PACKAGE statements and for any BEGIN...END blocks.

More Related Contents:

  1. How do I spool to a CSV formatted file using SQLPLUS?
  2. How do I ignore ampersands in a SQL script running from SQL Plus?
  3. Escaping ampersand character in SQL string
  4. How to get Oracle create table statement in SQL*Plus
  5. How to kill a running SELECT statement
  6. sqlplus statement from command line
  7. How to insert a string which contains an “&”
  8. How to create id with AUTO_INCREMENT on Oracle?
  9. How ROWNUM works in pagination query?
  10. Get list of all tables in Oracle?
  11. Removing duplicate rows from table in Oracle
  12. SQL Error: ORA-00933: SQL command not properly ended
  13. CASE vs. DECODE
  14. DATEDIFF function in Oracle [duplicate]
  15. WHERE IN condition not accepting String value
  16. What is the difference between Views and Materialized Views in Oracle?
  17. Pivoting of data using two columns
  18. Check if current date is between two dates Oracle SQL
  19. ORA-00907: missing right parenthesis
  20. How do I specify date literal when writing SQL query from SQL Server that is linked to Oracle?
  21. Unordered results in SQL
  22. Foreign Key Creation issue in Oracle
  23. create custom function for date difference excluding weekends and holidays in oracle sql
  24. What is it exactly a BLOB in a DBMS context
  25. Is there a version control system for database structure changes?
  26. Oracle: Updating a table column using ROWNUM in conjunction with ORDER BY clause
  27. Return number from Oracle Select statement after parsing date
  28. Oracle DB: How can I write query ignoring case?
  29. ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)
  30. Oracle Convert TIMESTAMP with Timezone to DATE

Leave a Comment