CODEWITHSUNDEEP
phpsqloracle-database
Konz Mama
Konz Mama

Reputation: 975

Doing upsert in ORACLE DB and doesnt work

I am doing an insert condition in oracle that when the record based on job and subjob doesnt exists, it shall insert otherwise, if it exists then it should update the rest of the value.

this is my procedure,

CREATE OR REPLACE PROCEDURE WELTESADMIN.SP_JOB_INS
(
    JOB_V VARCHAR2,
    SUBJOB_V VARCHAR2,
    STARTDATE_V DATE,
    ENDDATE_V DATE,
    JOBWEIGHT_V NUMBER
)
AS BEGIN INSERT INTO PROJECT_SPAN (JOB, SUBJOB, STARTDATE, ENDDATE, WEIGHT) VALUES (JOB_V, SUBJOB_V, STARTDATE_V, ENDDATE_V, JOBWEIGHT_V);
EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
UPDATE PROJECT_SPAN SET STARTDATE = STARTDATE_V, ENDDATE = ENDDATE_V, WEIGHT = JOBWEIGHT_V WHERE JOB = JOB_V AND SUBJOB = SUBJOB_V;
END;
/

and this is from PHP Call,

$insertJobSpanSql = "BEGIN SP_JOB_INS(:JOB, :SUBJOB, :SDATE, :EDATE, :WT); END;";
            $insertJobSpanParse = oci_parse($conn, $insertJobSpanSql);
            oci_bind_by_name($insertJobSpanParse, ":JOB", $jobValue);
            oci_bind_by_name($insertJobSpanParse, ":SUBJOB", $subJobValue);
            oci_bind_by_name($insertJobSpanParse, ":SDATE", $startDateValue);
            oci_bind_by_name($insertJobSpanParse, ":EDATE", $endDateValue);
            oci_bind_by_name($insertJobSpanParse, ":WT", $jobWeightValue);
            $insertJobSpanRes = oci_execute($insertJobSpanParse);

            if ($insertJobSpanRes){
                oci_commit($conn);
            } else {
                oci_rollback($conn);
            }

problem is it keeps inserting new row with the same job and subjob value. it should be an update to the new value.

Upvotes: 0

Views: 224

Answers (2)

APC
APC

Reputation: 146239

Oracle raises DUP_VAL_ON_INDEX when a DDL statement violates a primary key or unique key constraint. So for your code to work you need to define a primary key on (job, subjob) or else build a unique index on that pair.

Oracle provides an easier way of implementing upserts, in the form of the MERGE statement: 

CREATE OR REPLACE PROCEDURE WELTESADMIN.SP_JOB_INS
(
    JOB_V VARCHAR2,
    SUBJOB_V VARCHAR2,
    STARTDATE_V DATE,
    ENDDATE_V DATE,
    JOBWEIGHT_V NUMBER
)
AS BEGIN 
    merge into project_span ps
        using ( select job_v, subjob_v, startdate_v, enddate_v, jobweight_v from dual ) q
        on (ps.job = q.job_v
            and ps.subjob = q.subjob_v)
    when not matched then 
        insert (job, subjob, startdate, enddate, weight) 
        values (q.job_v, q.subjob_v, q.startdate_v, q.enddate_v, q.jobweight_v);
    when matched then
        update 
        set startdate = q.startdate_v
            , enddate = q.enddate_v
            , weight = q.jobweight_v 
           ;
END;

The MERGE statement is in the documentation. Find out more.

Upvotes: 1

Dmitriy
Dmitriy

Reputation: 5565

First of all, I recommend to use MERGE in such cases:

CREATE OR REPLACE PROCEDURE WELTESADMIN.SP_JOB_INS
(
    JOB_V VARCHAR2,
    SUBJOB_V VARCHAR2,
    STARTDATE_V DATE,
    ENDDATE_V DATE,
    JOBWEIGHT_V NUMBER
) AS 
BEGIN 
  merge into PROJECT_SPAN ps
  using (select JOB_V, SUBJOB_V, STARTDATE_V, ENDDATE_V, JOBWEIGHT_V 
           from dual) new_val
     on (ps.SUBJOB = new_val.SUBJOB_V and ps.JOB = new_val.JOB_V)
   when matched then update
    set STARTDATE = new_val.STARTDATE_V, 
        ENDDATE = new_val.ENDDATE_V, 
        WEIGHT = new_val.JOBWEIGHT_V
   when not matched then insert (JOB, SUBJOB, STARTDATE, ENDDATE, WEIGHT)
 values (new_val.JOB_V, new_val.SUBJOB_V, new_val.STARTDATE_V, 
         new_val.ENDDATE_V, new_val.JOBWEIGHT_V );
END;
/

If it still not updating values, use package DBMS_OUTPUT or logging into a table to make sure, that new and old JOB and SUBJOB really the same.

Upvotes: 2

Related Questions