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.
You could use ORA_ROWSCN to get the last scn for each block on the table as an *approximate* means of seeing when things happened, or the flashback query syntax to get transaction information, but you are limited to the undo_retention settings on your database. Hope this helps.
So, in Oracle database table rows are not in ordered untill and unless you don't have a column by which you will say order by <that_column>. If you wish to get the last updated row, just enable auditing for table and query (timestamp column of dba_audit_trail view) and forget all rest thing.
and rownum = 1; will get the "last" record. It is the ONLY way.
The system can automatically generate values for the identity column using a sequence generator. See Sequence Generator section. A value for an identity column is generated during an INSERT, UPSERT, or UPDATE statement. An identity column can be defined either as GENERATED ALWAYS or GENERATED BY DEFAULT.
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()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With