When Do I Need to Use a Semicolon VS a Slash in Oracle Sql

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

It's a matter of preference, but I prefer to see scripts that consistently use the slash - this way all "units" of work (creating a PL/SQL object, running a PL/SQL anonymous block, and executing a DML statement) can be picked out more easily by eye.

Also, if you eventually move to something like Ant for deployment it will simplify the definition of targets to have a consistent statement delimiter.

What does symbol / do at end of sql scripts?

From the documentation:

Executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.

The buffer has no command history and does not record SQL*Plus commands.

Why I do not need semicolon after some of Oracle SQL commands?

Your front-end drives the requirement, not the commands themselves. (If you check out the documentation, you won't find them in the syntax diagram for commands.) I'll focus on SQL*Plus below; SQL*Developer is likely similar, though one of your examples demonstrates at least one difference.

Many commands to be serviced by the database can span multiple lines, so sqlplus uses the semicolon as an indicator as to when you're done entering your SQL statement, at which point it gets sent to the database (sans terminating semicolon). That's the case with the DML (SELECT), DDL (CREATE TABLE), & TCL (SAVEPOINT, ROLLBACK) examples. You could also use a line with nothing but a slash to do the same, or use a line with nothing but "." to terminate the statement but not send it to the database.

In contrast, some commands are directed at the front-end itself instead of the database, such as you sqlplus DESC (DESCRIBE) example. In those cases the front-end is smart enough to recognize and process them without explicit termination with a semicolon, often because the front-end recognizes only single-line commands. (I'm not counting ending the line with "-" to indicate it carries over onto the next line.)

PL/SQL is another ball of wax: statements within it end with semicolons, so if sqlplus recognizes that you started a plsql block, then the normal semicolon behavior is suppressed, and you must use "/" or "." to finish the block.

AN EDIT ADDED YEARS LATER PER REQUEST FROM Laxmi Agarwal :

It's good to keep in mind that there's a definite line between the front-end you use to compose/edit/submit SQL commands, and the backend database that processes your commands. You write & submit a command from the front-end; that front-end handles the contact with the backend & reads the response; and finally the front-end presents the results, be it data from a query or a status like "Table altered".

A bit reminiscent of how the "vi" text editor operates, sqlplus has different modes it can be in. When you start sqlplus, it's in "command" mode, in which you enter sqlplus commands (this is different from SQL !). Recognized commands include COLUMN, SET, and DESCRIBE, plus several others. One special command in this mode is a / on its own line, which means "send the text in the buffer to the database for execution"; more on that below. Semicolons play almost no role in this mode; in most cases you may end a command with one, but it's silently ignored.

The 2nd mode that sqlplus can be in is "pl/sql" mode. You enter this mode with a BEGIN or DECLARE keyword, and sqlplus will then happily start reading whatever you type into its buffer. Semicolons get read as a normal part of whatever you type, because they're part of the language. You can exit this mode with a . on its own line, which will drop you back into command mode. For convenience, you can instead enter a / which is a shortcut to 1) drop back into command mode and 2) execute the buffer.

The 3rd mode is "sql" mode. You enter this mode by starting any SQL statement: SELECT, CREATE, ALTER, etc. As with pl/sql mode, what you type gets loaded into the buffer; and as before, you can drop back into command mode with a ., or use the same / shortcut as above to immediately execute the buffer.

There's an additional shortcut in sql mode. A semicolon ending a line does 4 steps in one shot: append the current line to buffer, strip the final semicolon from the buffer, enter command mode, and execute the buffer. This a super-convenient, and possible only because a semicolon is not part of SQL (in contrast to PL/SQL, in which a semicolon separates statements).

Disclaimer: I no longer have immediate access to SQL*Plus or SQL*Developer to test what I wrote above, so I'm going from memory. This information was (is?) true for SQL*Plus, yet probably also applicable to SQL*Developer - at least for the most part.

Semicolon and Single Quote Issue in SQL Insert Statements

Espace the semicolon:

SET DEF OFF;
Insert into PROD_DESC(PROD_NO, PROD_DESC)
Values('XYZ', 'test semicolon test\;');

Using Semicolons in Oracle SQL Statements

It's not the semicolon. Rerunning the same query meant that the rows were already cached, so you got them back much faster.

Oracle - How to use & without being asked about the value?

Execute

SQL> set define off

before running your code.

---

SQL> select '&test' from dual;

'&TES
-----
&test

SQL>

---

If you want to "declare" it, then use var:

SQL> var test varchar2(200);
SQL> exec :test := 'some value';

PL/SQL procedure successfully completed.

SQL> print test

TEST
----------------------------------------------------------------------------------------------------
some value

SQL>

---

In dynamic SQL: I won't lock anyone, but - I'll change my password.

SQL> connect scott/tiger
Connected.
SQL> var test varchar2(200);
SQL> exec :test := 'lion';

PL/SQL procedure successfully completed.

SQL> print test

TEST
----------------------------------------------------------------------------------------------------
lion

SQL> begin
  2    execute immediate 'alter user scott identified by ' || :test;
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> connect scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect scott/lion
Connected.
SQL>

When or Why to use a SET DEFINE OFF in Oracle Database

By default, SQL Plus treats '&' as a special character that begins a substitution string. This can cause problems when running scripts that happen to include '&' for other reasons:

SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');
Enter value for spencers: 
old   1: insert into customers (customer_name) values ('Marks & Spencers Ltd')
new   1: insert into customers (customer_name) values ('Marks  Ltd')

1 row created.

SQL> select customer_name from customers;

CUSTOMER_NAME
------------------------------
Marks  Ltd

If you know your script includes (or may include) data containing '&' characters, and you do not want the substitution behaviour as above, then use set define off to switch off the behaviour while running the script:

SQL> set define off
SQL> insert into customers (customer_name) values ('Marks & Spencers Ltd');

1 row created.

SQL> select customer_name from customers;

CUSTOMER_NAME
------------------------------
Marks & Spencers Ltd

You might want to add set define on at the end of the script to restore the default behaviour.

Display count of several tables in an Oracle database via SQL*Plus

set serveroutput on is a SQL*Plus command. It cannot appear inside a PL/SQL block. it has to be a separate command (and is only meaningful in the SQL*Plus client or a client that implements a subset of SQL*Plus's set commands). That would need to be done before your PL/SQL block executes.

The syntax for execute immediate would be

execute immediate 'select count(*) from ' || rec.table_name
   into ctr;

oracle SQL plus how to end command in SQL file?

For normal SQL statements, either a / on a line by itself, or a ; at the end of the command, will work fine.

For statements that include PL/SQL code, such as CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, CREATE TYPE, or anonymous blocks (DECLARE/BEGIN/END), a ; will not execute the command. Since PL/SQL uses semicolons as line terminators, its use as a command terminator must be suppressed in these statements. So in these cases, you must use / to execute the command.

In my experience, people prefer to use the semicolon when possible and use the slash only when required.

Note that for SQLPlus client commands -- such as SET or EXECUTE -- no command terminator is necessary at all, although people often end them with a semicolon out of habit.

---

Related Topics