Re: DDL statements in a trigger?

From: <jonterje_at_my-deja.com>
Date: Mon, 23 Oct 2000 14:52:01 GMT
Message-ID: <8t1jac$ul$1@nnrp1.deja.com>

In article <8t187j$ora$1_at_nnrp1.deja.com>, sybrandb_at_my-deja.com wrote:
> In article <8t17l7$oka$1_at_nnrp1.deja.com>,
> jonterje_at_link.no wrote:
> > Hello all,
> >
> > My company uses a third party application development tool that only
> > allows us to do simple selects, inserts, updates and deletes against
an
> > Oracle 8.1.5 database. (I know this doesn't sound very impressive,
but
> > there are other very good reasons for using this tool.) From this
tool
> > we need to create sequences "on-the-fly" for the applications we are
> > developing. I have tried to come up with a solution to this, given
the
> > limitations we develop under. I had an idea that an insert into a
table
> > could fire a trigger that creates the sequence we need, but Oracle
will
> > not let me put DDL statements inside the trigger:
> >
> > CREATE TABLE Counters(
> > sequenceName varchar2(30) not null);
> >
> > create or replace trigger Counters_trig
> > before insert on Counters
> > for each row
> > begin
> > create sequence :new.sequenceName;
> > end;
> >
> > insert into counters values ('test_cnt');
> >
> > select test_cnt.nextval from dual;
> >
> > Obviously, this does not work. Does anyone know a workaround? Or is
> > there an entierly different solution?
> >
> > --
> > Jon-Terje Lilleby

> DDL can be processed by means of execute immediate in 8.1.5 and beyond
> and dbms_sql in 8.0.6 and before.
> All well documented.

> Hth,

> --
> Sybrand Bakker, Oracle DBA

Thanks, but if i try to do an:
execute immediate 'create sequence foo'; from within the trigger I still get an ora 4092 "Cannot COMMIT in a trigger" from the execute immediate statement.

I believe this is because the 'create sequence ...' does an implicit commit. Any way to get around it?

--
Jon-Terje Lilleby


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon Oct 23 2000 - 09:52:01 CDT