Update Statement Failing When Reserved Words Are Used as Database Column Name (Doc ID 1384046.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.2.0.5 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

Procedure or any PL/SQL object failing with error 'ORA-01747', 'ORA-06512', 'ORA-04088', when trying to update the table using reserved words for columns on Oracle 10g 10.2.0.5. 

Code Block to Demonstrate the Behavior: 



drop table TEST_FILE;

create table TEST_FILE("FILE" VARCHAR2(3000), OPTYPE VARCHAR2(2000));

CREATE OR REPLACE PROCEDURE file_test_prc(p_input VARCHAR2) IS
 l_table_rec TEST_FILE%ROWTYPE;
 l_rowid     ROWID;
BEGIN
 BEGIN
   SELECT ROWID INTO l_rowid FROM TEST_FILE WHERE "FILE" = p_input;
   SELECT "FILE",OPTYPE  into l_table_rec FROM TEST_FILE WHERE ROWID = l_rowid;
 EXCEPTION
   WHEN no_data_found THEN
     l_rowid              := NULL;
     l_table_rec."FILE" := p_input;
 END;
 IF l_rowid IS NULL THEN
   --
   L_TABLE_REC.optype:='Inserted';
   INSERT INTO TEST_FILE VALUES l_table_rec;
   dbms_output.put_line('Inserted');
 ELSE
   --
   L_TABLE_REC.optype:=L_TABLE_REC.optype||';'||'Updated';
   UPDATE TEST_FILE SET ROW = l_table_rec WHERE ROWID = l_rowid;
   dbms_output.put_line('Updated');
 END IF;
END;
/

delete test_file;

-- Test insert
begin
 file_test_prc('tESTING iNSERT;');
end;  
/

select * from test_file;

-- Test Update
begin
 file_test_prc('tESTING iNSERT;');
end;  
/

select * from test_file;

Error Message: 

SQL> @insertblock.sql
begin
*
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification
ORA-06512: at "REFRESH.FILE_TEST_PRC", line 21
ORA-06512: at line 2

Changes

Same objects are working in Oracle 11g 11.1 and 11.2 based environments.

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