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

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.
Received on Fri Aug 20 1999 - 16:18:11 CDT