Re: INSERT w/ RETURNING clause using DBD::Oracle

Please pardon my ignorance as I'm just starting out with Oracle, but why would I need to disable the trigger? And how would I go about doing this?

Also, I suspect that this solution wouldn't hold-up with multiple users?? e.g., what if User2 inserted into the same table after User1 did a SELECT on the sequence but before User1's INSERT statement?

Is there another option I could explore? Maybe an approach whereby I could run a SELECT query on some internal Oracle table?

Thanks...

• Paul

In article <935183916.27773.0.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postmaster_at_sybrandb.nospam.demon.nl> wrote:
> The easiest method is the following
> (PL/SQL example)
> select <sequencename>.nextval into insid
> from dual;
> insert into test values etc.
>
> This will of course force you to disable the trigger.
> You can't capture the rowid without reselecting the row.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> <paul_at_infone.com> wrote in message news:7pkb3j$9lh$1_at_nnrp1.deja.com...
> >
> >
> > Hello all,
> >
> > Working environment: DBD::Oracle module for Perl DBI on an Oracle8
> > server running on a Linux machine.
> >
> > I am trying to figure out a way to return the row id after an INSERT
> > command.
> >
> > Ideally, I would like the returned value to be from a
self-incrementing
> > ID field we've created (using a sequence/trigger combination). If
this
> > cannot be done, then perhaps we could have the internal oracle ROWID
> > returned? from which I'm assuming I could figure out which row and
> > which table?
> >
> > I was able to get the following to work through SQLPlus:
> >
> > variable insID number;
> > insert into TEST (NAME) values ('John') RETURNING ID into :insID;
> > print :insID;
> >
> > but have had no luck trying to do it through Perl/DBD::Oracle. I
have
> > no particular affinity for using bind variables -- any method that
you
> > could suggest would be appreciated. I'm hoping there is a simpler
and
> > more elegant method that I'm ignorant of. Also, I would require a
> > solution which won't fall apart in a multi-user environment.
> >
> > Thanks in advance.
> >
> > - Paul Singh
> >
> >
> > Sent via Deja.com http://www.deja.com/
> > Share what you know. Learn what you don't.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Aug 23 1999 - 08:57:12 CDT