db-ddlutils-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jun Li" <allanju...@gmail.com>
Subject Potential object name conflict on Oracle.
Date Mon, 24 Jul 2006 02:31:27 GMT
Due to the limitation of maximum object name length (30) on Oracle, the
script generated could have object name conflict.

This is fine:

CREATE SEQUENCE seq_City__CityID;
CREATE TABLE City_
(
    CityID NUMBER(18,0) NOT NULL ,
    Name NVARCHAR2(100) NOT NULL,
    PRIMARY KEY (CityID)
);
CREATE OR REPLACE TRIGGER trg_City__CityID BEFORE INSERT ON City_ FOR EACH
ROW WHEN (new.CityID IS NULL)
BEGIN SELECT seq_City__CityID.nextval INTO :new.CityID FROM dual; END;
/
INSERT INTO City_ (CITYID,NAME) SELECT CITYID,NAME FROM CITY;
DROP TABLE CITY CASCADE CONSTRAINTS;
CREATE SEQUENCE seq_City_CityID;
CREATE TABLE City
(
    CityID NUMBER(18,0) NOT NULL ,
    Name NVARCHAR2(100) NOT NULL,
    PRIMARY KEY (CityID)
);
CREATE OR REPLACE TRIGGER trg_City_CityID BEFORE INSERT ON City FOR EACH ROW
WHEN (new.CityID IS NULL)
BEGIN SELECT seq_City_CityID.nextval INTO :new.CityID FROM dual; END;
/
INSERT INTO City (CityID,Name) SELECT CityID,Name FROM City_;
DROP TRIGGER trg_City__CityID;
DROP SEQUENCE seq_City__CityID;
DROP TABLE City_ CASCADE CONSTRAINTS;

But this is a problem:

CREATE SEQUENCE seq_AlertContex_AlertContextID; (truncated to 30 char)
CREATE TABLE AlertContext_
(
    AlertContextID NUMBER(18,0) NOT NULL ,
    InternalName VARCHAR2(50),
    TableName VARCHAR2(50),
    PRIMARY KEY (AlertContextID)
);
CREATE OR REPLACE TRIGGER trg_AlertContex_AlertContextID BEFORE INSERT ON
AlertContext_ FOR EACH ROW WHEN (new.AlertContextID IS NULL)
BEGIN SELECT seq_AlertContex_AlertContextID.nextval INTO
:new.AlertContextIDFROM dual; END;
/
INSERT INTO AlertContext_ (ALERTCONTEXTID,INTERNALNAME,TABLENAME) SELECT
ALERTCONTEXTID,INTERNALNAME,TABLENAME FROM ALERTCONTEXT;
DROP TABLE ALERTCONTEXT CASCADE CONSTRAINTS;
CREATE SEQUENCE seq_AlertContex_AlertContextID; (cause conflict here)
CREATE TABLE AlertContext
(
    AlertContextID NUMBER(18,0) NOT NULL ,
    InternalName VARCHAR2(50),
    TableName VARCHAR2(50),
    PRIMARY KEY (AlertContextID)
);
CREATE OR REPLACE TRIGGER trg_AlertContex_AlertContextID BEFORE INSERT ON
AlertContext FOR EACH ROW WHEN (new.AlertContextID IS NULL)
BEGIN SELECT seq_AlertContex_AlertContextID.nextval INTO
:new.AlertContextIDFROM dual; END;
/
INSERT INTO AlertContext (AlertContextID,InternalName,TableName) SELECT
AlertContextID,InternalName,TableName FROM AlertContext_;
DROP TRIGGER trg_AlertContex_AlertContextID;
DROP SEQUENCE seq_AlertContex_AlertContextID;
DROP TABLE AlertContext_ CASCADE CONSTRAINTS;

--------------
Cheers,
Jun

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message