CODEWITHSUNDEEP
oracle-databasesqlplus
JavaSheriff
JavaSheriff

Reputation: 7665

oracle SQL plus how to end command in SQL file?

I have SQL file with few commands.
What it the correct way to end lines in the script?
Is it slash [/] semicolon [;] both?
Is there any diffarent between regular sqls and Stored procedure code?
Thank you

Upvotes: 14

Views: 41093

Answers (3)

madhavan
madhavan

Reputation: 11

Usually we can use ";" to end sql statement,
but for create functions, triggers, procedures you have to use "/" after ";" to end SQL statement.
"/" might not be required when you use some developers tool like, oracle SQL developer, toad etc, but it should be mandate when you execute your query directly in the linux machine.

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48111

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.

Upvotes: 33

Daniel Haviv
Daniel Haviv

Reputation: 1036

; is the way you should end your sql commands, same goes for PLSQL procedures:

select * from dual;

select sysdate from dual;

select table_name from user tables;

exec dbms_output.putline('Hello');

Upvotes: 2

Related Questions