I have a table demo where I have 5 columns as below:
Create table demo
(
Demo_id number,
demo_a number,
Demo_b number,
Demo_c number
)
So here demo_id is surrogate PK and combination of demo_a,demo_b,demo_cis unique
So I have created a SP like this
save
( pdemo_a in number. ,
p_demo_b in number. ,
p_demp_c in number,
p_demo_id out number
)
So I am receiving data from API into this SP and I have to insert the data into my demo_table. So I have written logic where I am checking if the combination of (demo_a,demo_b,demo_c) exists in the table. if it exists then I am selecting demo_id into a variable and returning it to the API otherwise I am inserting the data for this unique combination in my table using no_data_found exception.
So basically code is:
Begin
Select demo_id
into p_demo_id
from demo
where demo _a=p_demo_a
and demo_b=p_demo_b
and demo_c = p_demo_c;
Exception
When no data found
then
insert into demo values()
Problem is the columns that are part of the unique key can be null so in that case when I check if the combination exists then because any of the field can be null because of which my select into clause fails. And the code goes to no_data_found section and tries to insert the record in the table but it fails beca``use that combination exists in the table. So what should I do. How do I return the demo_id to the API without throwing error.
If I got it right you need to check if there is an existing combination of parameters A, B, C (received from API) in your demo table in columns A, B, C. There could be NULL values in parameters. If there is no such a combination you have to insert a record. I don't know how you generate your PK (column DEMO_ID), so I invented my own. That primary key column could be anything. First I created the demo table with a few rows:
-- DEMO_ID DEMO_A DEMO_B DEMO_C
-- ---------- ---------- ---------- ----------
-- 100123 1 2 3
-- 100023 2 3
-- 100132 1 3 2
-- 100456 4 5 6
-- 100567 5 6 7
-- 100089 8 9
-- 100293 2 9 3
Next - a Procedure SAVE to check for the existance and to insert the record if it does not exist.
create or replace PROCEDURE SAVE
( p_demo_a in number,
p_demo_b in number,
p_demo_c in number,
p_demo_id out number
) AS
Status VarChar2(200);
BEGIN
Declare
Begin
Select DEMO_ID
Into p_demo_id
From DEMO
Where Nvl(DEMO_A, 999999999) = Nvl(p_demo_a, 999999999) And
Nvl(DEMO_B, 999999999) = Nvl(p_demo_b, 999999999) And
Nvl(DEMO_C, 999999999) = Nvl(p_demo_c, 999999999);
Status := 'OK - NO INSERT - RECORD EXISTS --> DEMO_ID = ' || p_demo_id;
Exception
WHEN NO_DATA_FOUND THEN
Begin
p_demo_id := 100000 + Nvl(To_Number(To_Char(p_demo_a) || To_Char(p_demo_b) || To_Char(p_demo_c)), 0);
INSERT INTO DEMO (DEMO_ID, DEMO_A, DEMO_B, DEMO_C)
VALUES(p_demo_id, p_demo_a, p_demo_b, p_demo_c);
Commit;
Status := 'OK - RECORD INSERTED --> DEMO_ID = ' || p_demo_id;
Exception
WHEN DUP_VAL_ON_INDEX THEN
p_demo_id := 0;
Status := 'ERR (SAVE) INNER BLOCK - DUP_VAL_ON_INDEX --> DEMO_ID = ' || p_demo_id;
WHEN OTHERS THEN
p_demo_id := 0;
Status := 'ERR (SAVE) INNER BLOCK - OTHERS --> ' || SQLERRM;
End;
WHEN OTHERS THEN
p_demo_id := 0;
Status := 'ERR (SAVE) OUTER BLOCK - OTHERS --> ' || SQLERRM;
End;
DBMS_OUTPUT.PUT_LINE(Status);
END SAVE;
Test 1.
SET SERVEROUTPUT ON
Declare
p_id NUMBER := Null;
Begin
SAVE(7, 6, 9, p_id);
If Nvl(p_id, 0) = 0 Then
DBMS_OUTPUT.PUT_LINE('Something went wrong - check the status');
End If;
End;
--
-- R e s u l t
--
-- anonymous block completed
-- OK - RECORD INSERTED --> DEMO_ID = 100769
Test 2. with null value
SET SERVEROUTPUT ON
Declare
p_id NUMBER := Null;
Begin
SAVE(7, Null, 9, p_id);
If Nvl(p_id, 0) = 0 Then
DBMS_OUTPUT.PUT_LINE('Something went wrong - check the status');
End If;
End;
--
-- R e s u l t
--
-- anonymous block completed
-- OK - RECORD INSERTED --> DEMO_ID = 100079
And if you try it again with the same parameters then the result is:
--
-- R e s u l t
--
-- anonymous block completed
-- OK - NO INSERT - RECORD EXISTS --> DEMO_ID = 100079
With my system of definition of the PK if you pass parameters A, B, C as Null, 8, 9 - there will be an error because of duplicate key.
--
-- R e s u l t
--
-- anonymous block completed
-- ERR (SAVE) INNER BLOCK - DUP_VAL_ON_INDEX --> DEMO_ID = 0
-- Something went wrong - check the status
But, as mentioned before - I don't know how you generate those surrogat keys. In this case there is a record with that key with A, B, C combination as 8, null, 9 which is different from null, 8, 9 but I created the same PK intentionally to show the possible problem. If there was a distinctive number put in place of null value while generating PK (or, even better, a sequnce number instead) then here will be a new PK and the record would be inserted. So, checking the nulls and inserting shouldn't be a problem, but PK generation could. Regards...