Problem with insert query usjng OCI(c++) in ORACLE

Question

Problem Statement:- I am inserting a record into Oracle if that record is already present(duplicate==>primary key is present) in database i want to update it with new one.

Currently to solve this while inserting the record if i get OCI_ERROR then i call

OCIErrorGet( (dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);

Then i check errbuf if the value of the errror buf is ORA-00001 ==> unique constraint violated if it is present then i update the value

Is there way to do the same thing except searching for record in the database if that record is already present update it I do not want to do this because i will have to write a code for that

Is there any specific error generated if the value is duplicated in ORACLE?

any suggestions?

Answer 1

There are 2 approaches to this problem, and the best one depends on details you didn't provide. One way would be to use a Repository pattern, introducing a software layer that tracks objects, and manages the update/insert issue by comparing the object you're giving it to its internal store of objects. The other (more procedural) method is just to query for an object with the given PK first, and if it exists, use an update, if not, do the insert.

Answer 2

Hi anish,

you could use the MERGE statement. Among other things, it allows a simple UPSERT (it actually allows the UPSERT of SETs of rows and not only a single row). Consider:

SQL> CREATE TABLE TEST (
  2     ID NUMBER,
  3     a VARCHAR2(10),
  4     b VARCHAR2(10),
  5     CONSTRAINT pk_test PRIMARY KEY (ID)
  6  );

Table created
SQL> MERGE INTO TEST t
  2  USING (SELECT 1 ID, 'a' a, 'b' b FROM dual) new_row
  3     ON (t.id = new_row.id)
  4  WHEN MATCHED THEN
  5     UPDATE SET t.a = new_row.a,
  6                t.b = new_row.b
  7  WHEN NOT MATCHED THEN
  8     INSERT (ID, a, b) VALUES (new_row.id, new_row.a, new_row.b);

Done
SQL> SELECT * FROM TEST;

        ID A          B
---------- ---------- ----------
         1 a          b
SQL> MERGE INTO TEST t
  2  USING (SELECT 1 ID, 'x' a, 'y' b FROM dual) new_row
  3     ON (t.id = new_row.id)
  4  WHEN MATCHED THEN
  5     UPDATE SET t.a = new_row.a,
  6                t.b = new_row.b
  7  WHEN NOT MATCHED THEN
  8     INSERT (ID, a, b) VALUES (new_row.id, new_row.a, new_row.b);

Done
SQL> SELECT * FROM TEST;

        ID A          B
---------- ---------- ----------
         1 x          y

i-e: You can insert and update using the same statement.

Cheers,

--
Vincent