Reserved words, double quotes, triggers and Oracle

From: Aleksey Burdakov <burdakov_at_vesco.ru>
Date: 28 Sep 2001 04:27:17 -0700
Message-ID: <9292180b.0109280327.5e61e8f9@posting.google.com>

First, a short story from a real life:
We decided to move our DBs to a different DBMS (particular from MS SQL Server and from Centura/Gupta SQLBase to Oracle 8.1.7), and used ERWin 3.5.2 for that purpose. We made reengineering of our DBs, converted to Oracle, and were lucky that we almost acheived our aim. But there Oracle came into the stage: it appeared that we used some reserved words as our column names (particular, DATE with the DATE type). Recently we discovered, that we can use double quotes to create tables with the reserved words and SELECT/UPDATE/INSERT/DELETE from these tables.
But there was another problem: we used ERWin-generated triggers in order to support referential integrity (CASCADE UPDATES, RESTRICTIONS, etc), and Oracle refused to generate triggers on the tables with the reserved words ...
So we came to a final problem: we had to rewrite our applications in order to move to Oracle ...

The question:
Everybody knows that Oracle extends the list of its reserved words from version to version, and it may appear in the future that someone will have used a reserved word in her application/DB, making the move to a higher Oracle version uneasy and costly. There was a proposal to use double quotes to solve the problem but it appeared to be an unreliable solution.

Is there any solution for that problem ?

Best Regards,

Aleksey Burdakov

P.S. Nor SQLBase nither MS SQLServer has such a problem. You can use any word to name a table or a column !
P.P.S. There is a description of the trigger problem below:

---

CREATE TABLE "C" (
"B" CHAR(18) NOT NULL,
"C" CHAR(18) NOT NULL
)

Execution Successful

CREATE TABLE "B" (
"B" CHAR(18) NOT NULL,
"DATE" DATE NULL

)

Execution Successful

create trigger tI_C after INSERT on C for each row -- ERwin Builtin Fri Sep 28 15:21:39 2001 -- INSERT trigger on C
declare numrows INTEGER;
begin

    /* ERwin Builtin Fri Sep 28 15:21:39 2001 */     /* B R/1 C ON CHILD INSERT RESTRICT */     select count(*) into numrows

      from B
      where
        /* %JoinFKPK(:%New,B," = "," and") */
        :new.B = B.B;
    if (
      /* %NotnullFK(:%New," is not null and") */
      
      numrows = 0

    )
    then

      raise_application_error(
        -20002,
        'Cannot INSERT C because B does not exist.'
      );

    end if;

• ERwin Builtin Fri Sep 28 15:21:39 2001 end;

ORA-06552: PL/SQL: Compilation unit analysis terminated ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

Execution Failed! Received on Fri Sep 28 2001 - 06:27:17 CDT