You are viewing a plain text version of this content. The canonical link for it is here.

Posted to ddlutils-user@db.apache.org by Jun Li <al...@gmail.com> on 2006/07/24 04:31:27 UTC

Potential object name conflict on Oracle.

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

Re: Potential object name conflict on Oracle.

Posted by Jun Li <al...@gmail.com>.

Any progress on this issue?

On 7/24/06, Jun Li <al...@gmail.com> wrote:
>
>  1. Do we need sequence and trigger for those temp tables?
> 2. When truncate the object name, keep the two underlines characters
> rather than others.
>
>
> On 7/24/06, Thomas Dudziak <to...@gmail.com> wrote:
> >
> > On 7/23/06, Jun Li <al...@gmail.com> wrote:
> >
> > > Due to the limitation of maximum object name length (30) on Oracle,
> > the
> > > script generated could have object name conflict.
> >
> > That is true, but there is nothing I can do about it. The problem is
> > that the name generation algorithm must create reproducable names, so
> > unique identifiers cannot be used.
> > A better way to handle this will be abailable once sequences/triggers
> > are directly supported by DdlUtils because you then can name them
> > yourself.
> >
> > Tom
> >
>
>
>
> --
> --------------
> Cheers,
>
> Jun
>



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

Re: Potential object name conflict on Oracle.

Posted by Jun Li <al...@gmail.com>.

1. Do we need sequence and trigger for those temp tables?
2. When truncate the object name, keep the two underlines characters rather
than others.


On 7/24/06, Thomas Dudziak <to...@gmail.com> wrote:
>
> On 7/23/06, Jun Li <al...@gmail.com> wrote:
>
> > Due to the limitation of maximum object name length (30) on Oracle, the
> > script generated could have object name conflict.
>
> That is true, but there is nothing I can do about it. The problem is
> that the name generation algorithm must create reproducable names, so
> unique identifiers cannot be used.
> A better way to handle this will be abailable once sequences/triggers
> are directly supported by DdlUtils because you then can name them
> yourself.
>
> Tom
>



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

Re: Potential object name conflict on Oracle.

Posted by Thomas Dudziak <to...@gmail.com>.

On 7/23/06, Jun Li <al...@gmail.com> wrote:

> Due to the limitation of maximum object name length (30) on Oracle, the
> script generated could have object name conflict.

That is true, but there is nothing I can do about it. The problem is
that the name generation algorithm must create reproducable names, so
unique identifiers cannot be used.
A better way to handle this will be abailable once sequences/triggers
are directly supported by DdlUtils because you then can name them
yourself.

Tom