Re: DDL statements in a trigger?

Thank you for the tip. It really worked!

In article <8ta7nq$67p$1_at_nnrp1.deja.com>,   daning_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 27 2000 - 03:05:30 CDT