Why do C-style comments make insert statement run twice?

269 views Asked by At

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?

1

There are 1 answers

2
Maheswaran Ravisankar On BEST ANSWER
/* 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

From Document:

You must enter a space after the slash-asterisk(/*) beginning a comment.