[Oracle] Insert avec récupération valeur de la cle

Bonjours,
Es-t'il possible est surtout comment faire un INSERT qui puissent me retourner la clé primaire (si le champs n'est pas inserér (cause duplication de clé par exemple))
MERCI

Oui c'est possible en utilisant la clause return :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

SQL> create table test_dvp (a number, b varchar2(30)); Table created.   SQL> variable v_a number SQL> variable v_b varchar2(30) SQL> insert into test_dvp (a,b) values (1, 'DVP') return a, b into :v_a, :v_b; 1 row created.   SQL> select :v_a, :v_b from dual; :V_A :V_B --------- -------------------------------- 1 DVP

---

Il me semble que la question etait, suite a une erreur ORA-0001 par example, comment recuperer la valeur de cle en erreur.

La primitive LOG ERRORS est prevue pour cet usage:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

CREATE SEQUENCE my_seq / CREATE TABLE my_table1 (id1 INTEGER ,col1 VARCHAR2(4000) ,PRIMARY KEY (id1)) / CREATE TABLE my_table2 (ora_err_number$ VARCHAR2(100) ,ora_err_mesg$ VARCHAR2(2000) ,ora_err_rowid$ ROWID ,ora_err_optyp$ VARCHAR2(100) ,ora_err_tag$ VARCHAR2(100) ,id1 INTEGER ,col1 VARCHAR2(4000)) /   INSERT INTO my_table1 VALUES (my_seq.nextval,'Line1'); INSERT INTO my_table1 VALUES (my_seq.currval,'Line2') LOG ERRORS INTO my_table2;   COL id1 FOR 99999 COL col1 FOR A50 SELECT id1,col1 FROM my_table1 / SELECT id1,col1 FROM my_table2 / DROP TABLE my_table1 / DROP TABLE my_table2 / DROP SEQUENCE my_seq /

---

Citation:

---

Sequence created.


Table created.


Table created.


1 row created.

INSERT INTO my_table1 VALUES (my_seq.currval,'Line2') LOG ERRORS INTO my_table
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.SYS_C009413) violated



ID1 COL1
------ --------------------------------------------------
1 Line1

1 row selected.


ID1 COL1
------ --------------------------------------------------
1 Line2

1 row selected.


Table dropped.


Table dropped.


Sequence dropped.

---