Re: INSERT RETURNING INTO Clause

Thanks,
I now understand that this should work in Oracle 8.

BTW is there any performance reasons you recommended using Bind Variable instead %s. I created my statement with a sprintf.

Thanks,
Bill

Thomas Kyte wrote:

> A copy of this was sent to Bill Mann <bill.mann_at_worldtalk.com>
> (if that email address didn't require changing)
> On Mon, 21 Jun 1999 00:19:44 -0700, you wrote:
>
> >It's appears that DBI and DBD:ORacle does not support returning values
> >from a SQL INSERT.
> >
> >Can anyone confirm this please.
> >
> >Thanks,
> >Here is the SQL
> >"INSERT INTO %s VALUES (
> > ids.nextval, '%s', %s, '%s',
> > %s, %s, fs.currval||'%s', %s, NULL, NULL)
> > RETURNING imkey INTO ?",
> >
> >Here is the error from Oracle:
> >
> >DBD::Oracle::st execute failed: ORA-03108: oranet: ORACLE does not
> >support this interface version (DBD: oexec error) at vfsora.pl line 454,
> ><STDIN> chunk 2.
>
> its reporting this from oexec which is the v7 oci interface. v7 didn't know
> about 'returning' as yet (since it hadn't been invented). One method that will
> probably work would be:
>
> "BEGIN
> INSERT INTO %s VALUES (
> ids.nextval, '%s', %s, '%s',
> %s, %s, fs.currval||'%s', %s, NULL, NULL)
> RETURNING imkey INTO ?;
> END;",
>
> that is -- parse and execute a plsql block. the ? will be a plsql bind variable
> and it should work them.
>
> btw: you should replace as many of the %s above with ? as well. Everything but
> the tablename should be a bind variable....
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Fine Grained Access Control", added June 8'th
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Tue Jun 22 1999 - 00:14:30 CDT