Retrieving identity of most recent insert in Oracle DB 12c

2021-6-21 anglehua

I'd like to have returned to me (via cx_oracle in python) the value of the Identity that's created for a row that I'm inserting. I think I can figure out the python bit on my own, if someone could please state how to modify my SQL statement to get the ID of the newly-created row.

I have a table that's created with something like the following:

CREATE TABLE hypervisor
  (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY (
    START WITH 1 NOCACHE ORDER ) NOT NULL ,
    name       VARCHAR2 (50)
  )
  LOGGING ;
ALTER TABLE hypervisor ADD CONSTRAINT hypervisor_PK PRIMARY KEY ( id ) ;

And I have SQL that's similar to the following:

insert into hypervisor ( name ) values ('my hypervisor')

Is there an easy way to obtain the id of the newly inserted row? I'm happy to modify my SQL statement to have it returned, if that's possible.

Most of the google hits on this issue were for version 11 and below, which don't support automatically-generated identity columns so hopefully someone here can help out.


Taking what user2502422 said above and adding the python bit:

newest_id_wrapper = cursor.var(cx_Oracle.STRING)
sql_params = { "newest_id_sql_param" : newest_id_wrapper }
sql = "insert into hypervisor ( name ) values ('my hypervisor') " + \             
      "returning id into :python_var"
cursor.execute(sql, sql_params)
newest_id=newest_id_wrapper.getvalue()


This example taken from learncodeshare.net has helped me grasp the correct syntax.

cur = con.cursor()

new_id = cur.var(cx_Oracle.NUMBER)

statement = 'insert into cx_people(name, age, notes) values (:1, :2, :3) returning id into :4'
cur.execute(statement, ('Sandy', 31, 'I like horses', new_id))

sandy_id = new_id.getvalue()

pet_statement = 'insert into cx_pets (name, owner, type) values (:1, :2, :3)'
cur.execute(pet_statement, ('Big Red', sandy_id, 'horse'))

con.commit()

It's only slightly different from ragerdl's answer, but different enough to be added here I believe! Notice the absence of sql_params = { "newest_id_sql_param" : newest_id_wrapper }



采集自互联网,如有侵权请联系本人

Powered by emlog 京ICP备15036472号-3 sitemap