Oracle INSERT, SELECT and NOT EXISTS

Question

I have a procedure, which is working fine. There are times when the same VALUES are passed into the procedure and generates a unique KEY violation.

I know this could be easily solved by using the usual solutions of either LOG ERRORS INTO clause, or to use the /*+ ignore_row_on_dupkey_index ... */

I saw this link and was trying to implement @OMG Ponies NOT EXISTS solution by seeing if the VALUES are already in the PRIMARY KEY but after hours of research and trying different methods I was unsuccessful

Avoid duplicates in INSERT INTO SELECT query in SQL Server

I'm very curious to see how this solution could be implemented in my situation.

Thanks in advance to all who respond and for your help, patience and expertise.

My working test CASE is below (not modified) is below. BTW im testing in live SQL in case anyone wants to use the same environment.

ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

create table schedule(
      seq_num NUMBER  GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
       schedule_id NUMBER(4),
       location_id number(4),
       base_date DATE,
       start_date DATE,
       end_date DATE,
          constraint schedule_pk primary key (schedule_id, location_id, base_date),
         CONSTRAINT start_min check (start_date=trunc(start_date,'MI')),   
       CONSTRAINT end_min check (end_date=trunc(end_date,'MI')),
 CONSTRAINT end_gt_start CHECK (end_date >= start_date)
      );
/


    CREATE TABLE locations AS
    SELECT level AS location_id,
       'Door ' || level AS location_name,

    CASE round(dbms_random.value(1,3)) 
            WHEN 1 THEN 'A' 
            WHEN 2 THEN 'T' 
            WHEN 3 THEN 'G' 
         END AS location_type

    FROM   dual
    CONNECT BY level <= 15;


     ALTER TABLE locations 
         ADD ( CONSTRAINT locations_pk
       PRIMARY KEY (location_id));


CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE
 (
  i_schedule_id IN PLS_INTEGER,
  i_base_date IN DATE,
  i_offset IN PLS_INTEGER DEFAULT 0, 
i_incr IN PLS_INTEGER DEFAULT 10,
  i_duration         IN PLS_INTEGER DEFAULT 5
)
 AS 
 
l_offset  interval day to second;
   l_incr interval day to second;
  l_duration interval day to second;

BEGIN

l_offset :=
NUMTODSINTERVAL(i_offset, 'SECOND') ;

l_incr :=
NUMTODSINTERVAL(i_incr, 'MINUTE') ;

l_duration :=
NUMTODSINTERVAL(i_duration, 'MINUTE') ;

        INSERT INTO schedule(
                schedule_id
              ,location_id
              ,base_date
              ,start_date
              ,end_date
          )
    SELECT   i_schedule_id
    ,        l.location_id
    ,        i_base_date
    ,      i_base_date + l_offset
                  + (l_incr * (ROWNUM - 1)) AS start_date
    ,      i_base_date + l_offset
                  + (l_incr * (ROWNUM - 1))
                + l_duration         AS end_date
    FROM      locations l;
END;
/


EXEC CREATE_SCHEDULE(1,TRUNC(SYSDATE))

Answer 1

Use a MERGE statement:

CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE(
  i_schedule_id IN PLS_INTEGER,
  i_base_date   IN DATE,
  i_offset      IN PLS_INTEGER DEFAULT 0, 
  i_incr        IN PLS_INTEGER DEFAULT 10,
  i_duration    IN PLS_INTEGER DEFAULT 5
)
AS 
  l_offset   interval day to second;
  l_incr     interval day to second;
  l_duration interval day to second;
BEGIN
  l_offset   := NUMTODSINTERVAL(i_offset, 'SECOND') ;
  l_incr     := NUMTODSINTERVAL(i_incr, 'MINUTE') ;
  l_duration := NUMTODSINTERVAL(i_duration, 'MINUTE') ;

  MERGE INTO schedule dst
  USING (
    SELECT   i_schedule_id AS schedule_id,
             l.location_id,
             i_base_date AS base_date,
             i_base_date + l_offset + (l_incr * (ROWNUM - 1))
               AS start_date,
             i_base_date + l_offset + (l_incr * (ROWNUM - 1)) + l_duration
               AS end_date
    FROM     locations l
  ) src
  ON (   src.schedule_id = dst.schedule_id
     AND src.location_id = dst.location_id
     AND src.base_date   = dst.base_date
  )
  WHEN NOT MATCHED THEN
    INSERT (
      schedule_id,
      location_id,
      base_date,
      start_date,
      end_date
    ) VALUES (
      src.schedule_id,
      src.location_id,
      src.base_date,
      src.start_date,
      src.end_date
    );
END;
/