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 |
|
Unclefool
Messages: 85 Registered: August 2021
|
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 |
John Watson
Messages: 8938 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 #684796 is a reply to message #684791] |
Sun, 22 August 2021 14:21 |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Unclefool wrote on Sat, 21 August 2021 20:56I 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
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:58:26 CDT 2024
|