Re: DDL statements in a trigger?

From: <daning_at_my-deja.com>
Date: Thu, 26 Oct 2000 21:29:35 GMT
Message-ID: <8ta7nq$67p$1@nnrp1.deja.com>

if the version is 8.1.5 or high, try autonomous transaction.

In article <8t1jac$ul$1_at_nnrp1.deja.com>,   jonterje_at_my-deja.com wrote:
> 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.

>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Oct 26 2000 - 16:29:35 CDT