To make a long story short, I started getting ORA-00001 primary key violations and I tracked down the issue to the fact that some of my INSERT INTO statements were running twice. I then discovered that the offending commands had a C-style comment afterwards:
WHENEVER SQLERROR EXIT FAILURE
SET ECHO OFF
SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
SET TIMING OFF
SET TIME OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET LINESIZE 120
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
[...]
INSERT INTO INF_FIELD (FIELD_ID, CATEGORY_ID, COLUMN_EXPRESSION, DISPLAY_NAME, SORT_ORDER) VALUES (17, 1, 'FOO.NAME', 'Name of the foo', 17);
/*This is a comment*/
It was then easily fixed by switching to this syntax:
--This is a comment
What's the exact reason why /*...*/ comments were making SQL*Plus run the statement twice?
/* This is a comment */
Just make sure you have a space after /* ,
So it is treated as a single/multi line comment. And not mean to execute the last stored PL/SQL or SQL
To put it in detail.
What ever SQL*Plus interprets after / is ignored and it blindly pushes it's cached block into the server. Except for /* followed by a new line or space.
SQL> SELECT * FROM DUAL;
D
-
X
SQL> /*t*/
D
-
X
SQL> /*
SQL> */
SQL> /
D
-
X
SQL> /*s
D
-
X
You must enter a space after the slash-asterisk(/*) beginning a comment.