Ctrl-C Fails to Interrupt UPDATE Statement Using SQL*Plus 10g (Doc ID 554350.1)

Last updated on MARCH 08, 2017

Applies to:

SQL*Plus - Version: 10.1 to 10.2.0.3
This problem can occur on any platform.
Generic UNIX platforms.

Symptoms

When run a script with UPDATE command using a 9i SQL*Plus executable and press Ctrl-C
<ENTER>
when the script prompts for input variable values, the script execution is
interrupted and Oracle updates zero rows. This is the expected behavior.

Now, when run the same script using a 10g SQL*Plus executable, the script does not stop after
pressing Ctrl-C  <ENTER> and proceeds to update all the rows on the table.

In checking V$SQL, the command is seen there without the WHERE clause. Oracle ignores that there
is a WHERE clause, and hence updates all records in the table.

Here is a sample script, call it test.sql, based on EMP demo table:

drop table EMP2;
create table EMP2 as select * from EMP;

UPDATE EMP2 set empno=1
WHERE job='&&v_job_name' and ename='&&v_emp_name';

To reproduce problem, run this test.sql script using SQL*Plus version 10g: 

SQL> @test.sql
drop table EMP2
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.

Enter value for v_job_name:          <------ Pressed CNTL-C and <ENTER> here.
old 2: where job='&&v_job_name' and ename='&&v_emp_name'
new 2:

14 rows updated.

SQL>

Notice that the UPDATE statement did not get cancelled and all records in table were updated.
The same problem does not happen using SQL*Plus 9i. With SQL*Plus 9i, zero rows get updated.

This problem reproduces on HP, Linux, and Sun Solaris; hence, problem is UNIX generic.
It does not reproduce on Windows.

Changes

Upgraded to Oracle Server 10g.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms