My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
  Changes
  Cause
  Solution
  References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.