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

From: fump <fumi_at_tpts5.seed.net.tw>
Date: 22 Aug 1999 10:00:20 GMT
Message-ID: <7pohnk$t95$1@news.seed.net.tw>

Sybrand Bakker <postmaster_at_sybrandb.demon.nl> wrote in message news:935183916.27773.0.pluto.d4ee154e_at_news.demon.nl...
> 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

You can get the ROWID though returning clause.

SQL> variable r varchar2(20);
SQL> desc t1;

 Name                            Null?    Type
 ------------------------------- -------- ----
 A                               NOT NULL NUMBER
 B                               NOT NULL NUMBER
 C                                        NUMBER

SQL> insert into t1 values (0, 0, 0) returning rowid into :r;

1 row created.

SQL> print r

AAAApVAACAAAALEAAA

SQL> select rowid from t1 where a=0 and b=0 and c=0;

ROWID

AAAApVAACAAAALEAAA

Moreover, you can get the column values affected by triggers though returning clause.

> <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.
>

Paul,
I think your problem is how to use bind variables/parameters in Perl. I am not familiar with Perl, and can not help you. Received on Sun Aug 22 1999 - 05:00:20 CDT