Re: [cx-oracle-users] Followup: cx_oracle, returning insert id

[Anthony T. <ant...@gm...>]

You require a string like the one in the example. You cannot pass the
table name and columns to update as bind variables, if that is what
you are trying to get at. :-)

On 4/3/06, kellie hobbs <kel...@ya...> wrote:
> Hello,
>
> Thanks for your help so far. Now can you tell me how to pass the table
> name and columns to be updated to the execute statement?
>
> Thanks again.
>
> Kellie Hobbs
>
> --- Anthony Tuininga <ant...@gm...> wrote:
>
> > Sure. Its quite straightforward but not covered by the DB API.
> >
> > # this creates the bind variable for use by Oracle
> > idVar =3D cursor.var(cx_Oracle.NUMBER)
> >
> > # execute the statement exactly as you normally would,
> > # binding the variable you just created above
> > cursor.execute("""
> >             insert into SomeTable (
> >               SomeOtherColumn
> >             ) values (
> >                :someValue
> >             ) returning Id
> >               into :id""",
> >             id =3D idVar,
> >             someValue =3D "SomeValue")
> >
> > # get the value after the statement is executed
> > id =3D idVar.getvalue()
> >
> > Hope that explains things.
> >
> > On 3/22/06, kellie hobbs <kel...@ya...> wrote:
> > > Hello,
> > >
> > > I am trying to get back the id of a record after inserting it into
> > the
> > > table. My Oracle database has a trigger that automatically creates
> > the
> > > id on insert. The PL/SQL statement that works for me is:
> > > SQL> declare id int;
> > >   2  begin
> > >   3  insert into table (field) values ('foo') returning tableid
> > into
> > > id;
> > >   4  dbms_output.put_line(id);
> > >   5  end;
> > >   6  /
> > >
> > > How can I run this through cx_oracle and get the id stored in a
> > > variable so I can use it?
> > >
> > > Many thanks.
> > >
> > > Kellie Hobbs, UC Berkeley
> > >