Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

When to use a semicolon or a slash in PL and SQL?

BluShadowOct 18 2016 — edited Oct 18 2016

Comments

Kalpataru

Hi @"BPeaslandDBA",

Very nice document.

One doubt.

I do not understand the line.

The forward slash after a single SQL statement will cause that SQL command to execute twice.

How to test it ?

Why because when i want to test the following command in SQL * Plus it is showing error.

select sysdate from dual; /

                        *

ERROR at line 1:

ORA-00911: invalid character

How to test that the statement will execute twice ?

Gaz in Oz

Hi @"BPeaslandDBA",

Very nice document.

One doubt.

I do not understand the line.

The forward slash after a single SQL statement will cause that SQL command to execute twice.

How to test it ?

Why because when i want to test the following command in SQL * Plus it is showing error.

select sysdate from dual; /

                        *

ERROR at line 1:

ORA-00911: invalid character

How to test that the statement will execute twice ?

The forward slash is a special character in sqlplus and must appear on a line of it's own. It runs what ever is currently in the sqlplus buffer.

Easiest way to show that behavior is with a sql file.

$ host cat twice.sqlselect sysdate from dual;/SQL> @twiceSYSDATE-------------------2017-07-22 15:56:26SYSDATE-------------------2017-07-22 15:56:26SQL>
Kalpataru

The forward slash is a special character in sqlplus and must appear on a line of it's own. It runs what ever is currently in the sqlplus buffer.

Easiest way to show that behavior is with a sql file.

$ host cat twice.sqlselect sysdate from dual;/SQL> @twiceSYSDATE-------------------2017-07-22 15:56:26SYSDATE-------------------2017-07-22 15:56:26SQL>

Thanks @"Gaz in Oz"  for your explanation.

BPeaslandDBA

Hi @"BPeaslandDBA",

Very nice document.

One doubt.

I do not understand the line.

The forward slash after a single SQL statement will cause that SQL command to execute twice.

How to test it ?

Why because when i want to test the following command in SQL * Plus it is showing error.

select sysdate from dual; /

                        *

ERROR at line 1:

ORA-00911: invalid character

How to test that the statement will execute twice ?

@"Gaz in Oz" explained it nicely. The slash needs to be on its own line. You had it on the same line. So enter any SQL statement. Then enter a slash on the next line. In SQL*Plus, that statement will be executed twice.

Cheers,
Brian

Solomon Yakobson

I think you should add section explaining semi-colon & slash use when PL/SQL is used within SQL statement. Type declarations and function in with clause in 12c.

SY.

user3263929

Hi, thank you for your document.

One question : can't we generalize the use of the forward slash ? Like this :

----

select count(*) from api_log

/

----

(the semi-colon is omitted)

Thanks.

BluShadow

Hi, thank you for your document.

One question : can't we generalize the use of the forward slash ? Like this :

----

select count(*) from api_log

/

----

(the semi-colon is omitted)

Thanks.

See page 3 "When not to use a slash".  It's possible to use a slash in many of those circumstances instead of using the semicolon, but as the "/" is often specific to certain tools, such as SQL*Plus, it is better to consider the semicolon to be the statement terminator as best practice and only use a "/" where needed.

1 - 7

Post Details

Added on Oct 18 2016
7 comments
2,233 views