Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> 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