I'm trying to run a script using sqlplus. My script is a simple delete statement. I execute it by putting the following in my ksh terminal:
sqlplus username/'password' @../sql/delete_societes.sql
../sql/delete_societes.sql is
DELETE FROM f2020.SOCIETES;
/
For some reason, it runs twice, causing the output "0 lines deteleted" to be printed twice and causing errors when I try to do an insert instead of a delete.
Make your script do either;
DELETE FROM f2020.SOCIETES
/
or
DELETE FROM f2020.SOCIETES;
without the slash.
/(slash)
Executes the most recently executed SQL command or PL/SQL block which is stored in the SQL buffer.
and in the example further down:
Enter a slash (/) to re-execute the command in the buffer
... which is exactly what you are seeing.
The semicolon (;) means that this is the end of the command. Press Return or click Execute. SQL*Plus processes the command and displays the results
Like many clients SQL*Plus treats the semicolon at the end of your SQL statement as a statement separator - it is not part of the statement itself (which causes some confusion for e.g. dynamic SQL and JDBC calls) - and when it sees it it executes the command. The executed statement stays in the command buffer; and if you list to see the current command buffer, it will not show that semicolon. When you issue a slash it executes the buffer again.
Things are slightly different for PL/SQL; there the PL/SQL block has to be terminated with a semicolon, which is part of the block, and appears in the buffer. You have to use a slash to execute a PL/SQL block.
An example where you can see the sqlplus buffer content for SQL and PLSQL.
me@XEPDB1> help run
RUN
---
Lists and executes the most recently executed SQL command or
PL/SQL block which is stored in the SQL buffer. The buffer has
no command history list and does not record SQL*Plus commands.
R[UN]
me@XEPDB1> help /
/ (slash)
---------
Executes the most recently executed SQL command or PL/SQL block
which is stored in the SQL buffer. Use slash (/) at the command
prompt or line number prompt in SQL*Plus command line. The buffer
has no command history and does not record SQL*Plus commands.
/
me@XEPDB1> clear buffer [1/651]
buffer cleared
me@XEPDB1> l
SP2-0223: No lines in SQL buffer.
me@XEPDB1> select * from dual
2 /
D
-
X
me@XEPDB1> l
1* select * from dual
me@XEPDB1> select * from dual;
D
-
X
me@XEPDB1> l
1* select * from dual
me@XEPDB1> /
D
-
X
me@XEPDB1> r
1* select * from dual
D
-
X
me@XEPDB1> begin null; end;
2 /
PL/SQL procedure successfully completed.
me@XEPDB1> l
1* begin null; end;
me@XEPDB1> /
PL/SQL procedure successfully completed.
me@XEPDB1> r
1* begin null; end;
PL/SQL procedure successfully completed.