Home » SQL & PL/SQL » SQL & PL/SQL » Oracle INSERT, SELECT, NOT EXISTS primary key (19c)
Oracle INSERT, SELECT, NOT EXISTS primary key [message #684789] Sat, 21 August 2021 09:08 Go to next message
Unclefool
Messages: 18
Registered: August 2021
Junior Member
I have a procedure, which is working fine. There are times when QA passes in the same VALUES and it generates a unique KEY violation.

Can this procedure be modified to do and INSERT by select with a NOT EXISTS on the columns that make up the primary key. I suspect I may need a SELECT within a SELECT FROM DUAL?

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

My working test CASE is below. BTW in 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, timestamp '2021-08-29 00:00:00')

Re: Oracle INSERT, SELECT, NOT EXISTS primary key [message #684790 is a reply to message #684789] Sat, 21 August 2021 09:44 Go to previous messageGo to next message
John Watson
Messages: 8640
Registered: January 2010
Location: Global Village
Senior Member
I do not understand your description of the problem or your proposed soloution. However, if you are getting "ORA-00001 unique constraint violated", the usual solutions are either to enable error logging with a
LOG ERRORS INTO
clause, or to use the
/*+ ignore_row_on_dupkey_index ... */
hint.

Re: Oracle INSERT, SELECT, NOT EXISTS primary key [message #684791 is a reply to message #684790] Sat, 21 August 2021 12:56 Go to previous messageGo to next message
Unclefool
Messages: 18
Registered: August 2021
Junior Member
I know that hint was available but I was trying to use a SQL solution to help better myself. I saw a MERGE but I was trying to play with the not EXISTS command. I thought that may have been applicable in this situation by comparing the columns in the PRIMARY key via an inline view. I was unsuccessful and couldn't get the procedure to create and wanted to know where I went wrong. Thanks for responding
Re: Oracle INSERT, SELECT, NOT EXISTS primary key [message #684796 is a reply to message #684791] Sun, 22 August 2021 14:21 Go to previous message
Andrey_R
Messages: 380
Registered: January 2012
Location: Israel
Senior Member

Unclefool wrote on Sat, 21 August 2021 20:56
I know that hint was available but I was trying to use a SQL solution to help better myself. I saw a MERGE but I was trying to play with the not EXISTS command. I thought that may have been applicable in this situation by comparing the columns in the PRIMARY key via an inline view. I was unsuccessful and couldn't get the procedure to create and wanted to know where I went wrong. Thanks for responding


This seems to work for me:

SQL>
SQL> CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE
  2   (
  3    i_schedule_id IN PLS_INTEGER,
  4    i_base_date IN DATE,
  5    i_offset IN PLS_INTEGER DEFAULT 0,
  6  i_incr IN PLS_INTEGER DEFAULT 10,
  7    i_duration         IN PLS_INTEGER DEFAULT 5
  8  )
  9   AS
 10
 11  l_offset  interval day to second;
 12     l_incr interval day to second;
 13    l_duration interval day to second;
 14
 15  BEGIN
 16
 17  l_offset :=
 18  NUMTODSINTERVAL(i_offset, 'SECOND') ;
 19
 20  l_incr :=
 21  NUMTODSINTERVAL(i_incr, 'MINUTE') ;
 22
 23  l_duration :=
 24  NUMTODSINTERVAL(i_duration, 'MINUTE') ;
 25
 26          INSERT INTO schedule(
 27                  schedule_id
 28                ,location_id
 29                ,base_date
 30                ,start_date
 31                ,end_date
 32            )
 33  SELECT   i_schedule_id
 34      ,        l.location_id
 35      ,        i_base_date
 36      ,      i_base_date + l_offset
 37                    + (l_incr * (ROWNUM - 1)) AS start_date
 38      ,      i_base_date + l_offset
 39                    + (l_incr * (ROWNUM - 1))
 40                  + l_duration         AS end_date
 41      FROM      locations l;
 42  EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL;
 43  END;
 44  /

Procedure created.

Elapsed: 00:00:00.12
SQL> EXEC CREATE_SCHEDULE(1, timestamp '2021-08-29 00:00:00')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> EXEC CREATE_SCHEDULE(1, timestamp '2021-08-29 00:00:00')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> EXEC CREATE_SCHEDULE(1, timestamp '2021-08-29 00:00:00')

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> EXEC CREATE_SCHEDULE(1, timestamp '2021-08-29 00:00:00')

PL/SQL procedure successfully completed.
Or this:

SQL> CREATE OR REPLACE PROCEDURE CREATE_SCHEDULE
  2   (
  3    i_schedule_id IN PLS_INTEGER,
  4    i_base_date IN DATE,
  5    i_offset IN PLS_INTEGER DEFAULT 0,
  6  i_incr IN PLS_INTEGER DEFAULT 10,
  7    i_duration         IN PLS_INTEGER DEFAULT 5
  8  )
  9   AS
 10
 11  l_offset  interval day to second;
 12     l_incr interval day to second;
 13    l_duration interval day to second;
 14
 15  BEGIN
 16
 17  l_offset :=
 18  NUMTODSINTERVAL(i_offset, 'SECOND') ;
 19
 20  l_incr :=
 21  NUMTODSINTERVAL(i_incr, 'MINUTE') ;
 22
 23  l_duration :=
 24  NUMTODSINTERVAL(i_duration, 'MINUTE') ;
 25
 26          INSERT INTO schedule(
 27                  schedule_id
 28                ,location_id
 29                ,base_date
 30                ,start_date
 31                ,end_date
 32            )
 33  SELECT   i_schedule_id
 34      ,        l.location_id
 35      ,        i_base_date
 36      ,      i_base_date + l_offset
 37                    + (l_incr * (ROWNUM - 1)) AS start_date
 38      ,      i_base_date + l_offset
 39                    + (l_incr * (ROWNUM - 1))
 40                  + l_duration         AS end_date
 41      FROM      locations l
 42  where not exists (select 1
 43    from schedule
 44    where schedule_id = i_schedule_id
 45    and l.location_id = location_id
 46    and i_base_date = base_date );
 47  END;
 48  /

Procedure created.

Elapsed: 00:00:00.16
SQL> EXEC CREATE_SCHEDULE(1, timestamp '2021-08-29 00:00:00');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> /

Procedure created.

Elapsed: 00:00:00.03
SQL> /

Procedure created.

Elapsed: 00:00:00.00
SQL> /
Andrey
Previous Topic: Oracle cte multiple rows (3 merged)
Next Topic: About REF datatype
Goto Forum:
  


Current Time: Sat Nov 27 05:32:11 CST 2021