Skip to Main Content
  • Questions

  • Problems with insert procedure and duplicates

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Barbara.

Asked: April 23, 2020 - 8:57 am UTC

Last updated: April 27, 2020 - 4:31 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

Dear Ask Tom-Team!

you guys helped me a lot solving my previous problems with sql.
Now I have a new problem I can't find a solution by myself.
In my data base I have the following situation:
I have a firm that has one or more contact persons. For each contact person it is stored in which matter it should be contacted (attribute 'type'), e.g. for shipping, for special questions, for marketing, .... Each year we receive information on the current contact persons. These persons can be the same as in the year before or it can be one or more different persons. The information in the data base should be updated if it is a new person for a specific type, e.g. the act_dat should be set for the current entry and a new entry should be created. If the contact person e.g. for marketing stays the same, there should be no changes in the data base.
Further constraints are:
a) For shipping only one current contact person is allowed whereas for all other issues/types there could be more than one current contact person
b) Person ‚A‘ can be the contact person for shipping in year 2012 while in 2013 it is person ‚B‘. In 2014 it could be again person ‚A‘

My table contact looks like this:
CREATE TABLE contact (
    id            NUMBER
        CONSTRAINT nnc_con_id NOT NULL,
    firm_id      NUMBER
        CONSTRAINT nnc_con_firm_id NOT NULL,
    surname       VARCHAR2(150),
    lastname      VARCHAR2(150),
    division     VARCHAR2(150),
    phone       VARCHAR2(35),
    email         VARCHAR2(150),
    type           VARCHAR2(150)
        CONSTRAINT nnc_con_type NOT NULL,
    creat_id    NUMBER
        CONSTRAINT nnc_con_creat_id NOT NULL,
    creat_dat   TIMESTAMP WITH LOCAL TIME ZONE DEFAULT systimestamp
        CONSTRAINT nnc_con_creat_dat NOT NULL,
    act_id        NUMBER DEFAULT NULL,
    act_dat      TIMESTAMP WITH LOCAL TIME ZONE DEFAULT NULL
);

CREATE UNIQUE INDEX pk_con_ix ON
    contact (
        id
    ASC );

CREATE UNIQUE INDEX con_ix_ship ON
    contact ( decode(
        type, 'Shipping',
        firm_id, NULL),
    decode(
        type, 'Shipping', EXTRACT(YEAR FROM
        creat_dat), NULL),
   decode(
 type, 'Shipping',
 act_dat, null));

    
ALTER TABLE contact ADD CONSTRAINT pk_con PRIMARY KEY ( id );

CREATE SEQUENCE con_id_seq START WITH 1 NOCACHE ORDER;

CREATE OR REPLACE TRIGGER con_id_trg BEFORE
    INSERT ON contact
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := con_id_seq.nextval;
END;
/


I tried to model this with a procedure but did not succeed so far. Here is my best try:
create or replace PROCEDURE INSERT_CONTACT 
(
  IN_FIRM_ID IN contact.firm_id%TYPE
, IN_SURNAME IN contact.surname%TYPE
, IN_LASTNAME IN contact.lastname%TYPE 
, IN_DIVISION IN contact.division%TYPE
, IN_PHONE IN contact.phone%TYPE
, IN_EMAIL IN contact.email%TYPE 
, IN_TYPE IN contact.type%TYPE 
, IN_CREAT_ID IN contact.creat_id%TYPE 
, IN_CREAT_DAT IN contact.creat_dat%TYPE DEFAULT SYSTIMESTAMP
, IN_ACT_ID IN contact.act_id%TYPE DEFAULT NULL
, IN_ACT_DAT IN contact.act_dat%TYPE DEFAULT NULL
) AS 
BEGIN
    UPDATE contact
        SET act_id = in_creat_id, act_dat = in_creat_dat
            WHERE firm_id = IN_FIRM_ID
     AND act_dat IS NULL
            AND IN_type = 'Shipping'
            AND type = 'Shipping'
            AND ( type <> in_type
               OR surname <> in_surname
               OR lastname <> in_lastname
               OR division <> in_division
               OR phone <> in_phone
               OR email <> in_email);
    INSERT INTO contact 
            (firm_id, surname, lastname, division, phone, email, type, creat_id, creat_dat) 
            VALUES(in_firm_id, in_surname,in_lastname,in_division,in_phone,in_email,in_type,in_creat_id, in_creat_dat);
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                dbms_output.put_line('<<<No Insert: '||sqlerrm);
END INSERT_CONTACT;

So
EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2013','DD.MM.YYYY'));

should insert the data.
EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2013','DD.MM.YYYY'));

should change nothing.
EXECUTE insert_contact(12,'Tom','Cat',null,null,null,'Shipping',1,to_date('11.01.2013','DD.MM.YYYY'));

should update the current entry (setting the act_dat) and insert a new entry (since there is only one current shipping person allowed).
EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2014','DD.MM.YYYY'));

should update the current entry (setting the act_dat) and insert a new entry.

EXECUTE insert_contact(12,'Tom','Cat',null,null,null,'Marketing',1,to_date('10.01.2013','DD.MM.YYYY'));

should insert a new entry.
EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Marketing',1,to_date('10.01.2013','DD.MM.YYYY'));

should insert a new entry (no update, since there is more than one marketing current person allowed).

EXECUTE insert_contact(12,'Tina','Bird',null,null,null,'Marketing',1,to_date('11.01.2014','DD.MM.YYYY'));

should update the current two entries (setting the act_dat) and insert a new entry (since there is a new marketing person).



Regarding the error handling I plan to store it in a table and not just using dbms_output.
I really appreciate if you guys can give me a hint how to solve my problem.
Thanks a lot
Barbara

and Connor said...

Your update statement *always* has:

AND IN_type = 'Shipping'
AND type = 'Shipping'

So it is never going to do anything with when you pass "Marketing". Isn't that the core issue here ?

Also, note the change I've made to the WHERE clause to handle null , because "a <> b" does not work with nulls

SQL>
SQL> CREATE TABLE contact (
  2      id            NUMBER
  3          CONSTRAINT nnc_con_id NOT NULL,
  4      firm_id      NUMBER
  5          CONSTRAINT nnc_con_firm_id NOT NULL,
  6      surname       VARCHAR2(150),
  7      lastname      VARCHAR2(150),
  8      division     VARCHAR2(150),
  9      phone       VARCHAR2(35),
 10      email         VARCHAR2(150),
 11      type           VARCHAR2(150)
 12          CONSTRAINT nnc_con_type NOT NULL,
 13      creat_id    NUMBER
 14          CONSTRAINT nnc_con_creat_id NOT NULL,
 15      creat_dat   TIMESTAMP WITH LOCAL TIME ZONE DEFAULT systimestamp
 16          CONSTRAINT nnc_con_creat_dat NOT NULL,
 17      act_id        NUMBER DEFAULT NULL,
 18      act_dat      TIMESTAMP WITH LOCAL TIME ZONE DEFAULT NULL
 19  );

Table created.

SQL>
SQL> CREATE UNIQUE INDEX pk_con_ix ON
  2      contact (
  3          id
  4      ASC );

Index created.

SQL>
SQL> CREATE UNIQUE INDEX con_ix_ship ON
  2      contact ( decode(
  3          type, 'Shipping',
  4          firm_id, NULL),
  5      decode(
  6          type, 'Shipping', EXTRACT(YEAR FROM
  7          creat_dat), NULL),
  8     decode(
  9   type, 'Shipping',
 10   act_dat, null));

Index created.

SQL>
SQL>
SQL> ALTER TABLE contact ADD CONSTRAINT pk_con PRIMARY KEY ( id );

Table altered.

SQL>
SQL> CREATE SEQUENCE con_id_seq START WITH 1 NOCACHE ORDER;

Sequence created.

SQL>
SQL> CREATE OR REPLACE TRIGGER con_id_trg BEFORE
  2      INSERT ON contact
  3      FOR EACH ROW
  4      WHEN ( new.id IS NULL )
  5  BEGIN
  6      :new.id := con_id_seq.nextval;
  7  END;
  8  /

Trigger created.

SQL>
SQL>
SQL> create or replace PROCEDURE INSERT_CONTACT
  2  (
  3    IN_FIRM_ID IN contact.firm_id%TYPE
  4  , IN_SURNAME IN contact.surname%TYPE
  5  , IN_LASTNAME IN contact.lastname%TYPE
  6  , IN_DIVISION IN contact.division%TYPE
  7  , IN_PHONE IN contact.phone%TYPE
  8  , IN_EMAIL IN contact.email%TYPE
  9  , IN_TYPE IN contact.type%TYPE
 10  , IN_CREAT_ID IN contact.creat_id%TYPE
 11  , IN_CREAT_DAT IN contact.creat_dat%TYPE DEFAULT SYSTIMESTAMP
 12  , IN_ACT_ID IN contact.act_id%TYPE DEFAULT NULL
 13  , IN_ACT_DAT IN contact.act_dat%TYPE DEFAULT NULL
 14  ) AS
 15  BEGIN
 16      UPDATE contact
 17          SET act_id = in_creat_id, act_dat = in_creat_dat
 18              WHERE firm_id = IN_FIRM_ID
 19       AND act_dat IS NULL
 20              AND IN_type = 'Shipping'
 21              AND type = 'Shipping'
 22              AND ( decode(type,in_type,0,1) = 1
 23                 OR decode(surname,in_surname,0,1) = 1
 24                 OR decode(lastname,in_lastname,0,1) = 1
 25                 OR decode(division,in_division,0,1) = 1
 26                 OR decode(phone,in_phone,0,1) = 1
 27                 OR decode(email,in_email,0,1) = 1
 28                 );
 29         dbms_output.put_line('updated: '||sql%rowcount);
 30      INSERT INTO contact
 31              (firm_id, surname, lastname, division, phone, email, type, creat_id, creat_dat)
 32              VALUES(in_firm_id, in_surname,in_lastname,in_division,in_phone,in_email,in_type,in_creat_id, in_creat_dat);
 33              dbms_output.put_line('inserted 1');
 34          EXCEPTION
 35              WHEN DUP_VAL_ON_INDEX THEN
 36                  dbms_output.put_line('insert not done');
 37  END INSERT_CONTACT;
 38  /

Procedure created.

SQL> set serverout on
SQL>
SQL> --should insert the data.
SQL> EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2013','DD.MM.YYYY'));
updated: 0
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL> --should change nothing.
SQL> EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2013','DD.MM.YYYY'));
updated: 0
insert not done

PL/SQL procedure successfully completed.

SQL>
SQL> --should update the current entry (setting the act_dat) and insert a new entry (since there is only one current shipping person allowed).
SQL> EXECUTE insert_contact(12,'Tom','Cat',null,null,null,'Shipping',1,to_date('11.01.2013','DD.MM.YYYY'));
updated: 1
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL> --should update the current entry (setting the act_dat) and insert a new entry.
SQL> EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Shipping',1,to_date('10.01.2014','DD.MM.YYYY'));
updated: 1
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL> --should insert a new entry.
SQL> EXECUTE insert_contact(12,'Tom','Cat',null,null,null,'Marketing',1,to_date('10.01.2013','DD.MM.YYYY'));
updated: 0
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL> --should insert a new entry (no update, since there is more than one marketing current person allowed).
SQL> EXECUTE insert_contact(12,'Maria','Mouse',null,null,null,'Marketing',1,to_date('10.01.2013','DD.MM.YYYY'));
updated: 0
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL> select * from contact
  2  @pr
==============================
ID                            : 1
FIRM_ID                       : 12
SURNAME                       : Maria
LASTNAME                      : Mouse
DIVISION                      :
PHONE                         :
EMAIL                         :
TYPE                          : Shipping
CREAT_ID                      : 1
CREAT_DAT                     : 10-JAN-13 12.00.00.000000 AM
ACT_ID                        : 1
ACT_DAT                       : 11-JAN-13 12.00.00.000000 AM
==============================
ID                            : 3
FIRM_ID                       : 12
SURNAME                       : Tom
LASTNAME                      : Cat
DIVISION                      :
PHONE                         :
EMAIL                         :
TYPE                          : Shipping
CREAT_ID                      : 1
CREAT_DAT                     : 11-JAN-13 12.00.00.000000 AM
ACT_ID                        : 1
ACT_DAT                       : 10-JAN-14 12.00.00.000000 AM
==============================
ID                            : 4
FIRM_ID                       : 12
SURNAME                       : Maria
LASTNAME                      : Mouse
DIVISION                      :
PHONE                         :
EMAIL                         :
TYPE                          : Shipping
CREAT_ID                      : 1
CREAT_DAT                     : 10-JAN-14 12.00.00.000000 AM
ACT_ID                        :
ACT_DAT                       :
==============================
ID                            : 5
FIRM_ID                       : 12
SURNAME                       : Tom
LASTNAME                      : Cat
DIVISION                      :
PHONE                         :
EMAIL                         :
TYPE                          : Marketing
CREAT_ID                      : 1
CREAT_DAT                     : 10-JAN-13 12.00.00.000000 AM
ACT_ID                        :
ACT_DAT                       :
==============================
ID                            : 6
FIRM_ID                       : 12
SURNAME                       : Maria
LASTNAME                      : Mouse
DIVISION                      :
PHONE                         :
EMAIL                         :
TYPE                          : Marketing
CREAT_ID                      : 1
CREAT_DAT                     : 10-JAN-13 12.00.00.000000 AM
ACT_ID                        :
ACT_DAT                       :

PL/SQL procedure successfully completed.

SQL>
SQL> --should update the current two entries (setting the act_dat) and insert a new entry (since there is a new marketing person)
SQL> EXECUTE insert_contact(12,'Tina','Bird',null,null,null,'Marketing',1,to_date('11.01.2014','DD.MM.YYYY'));
updated: 0
inserted 1

PL/SQL procedure successfully completed.

SQL>
SQL>



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Prevent insert of existing entry

Barbara, April 24, 2020 - 7:12 am UTC

Thank you for your help! The decode had been a great help. I changed the procedure so that there will be also an update if type <> 'Shipping'.
create or replace PROCEDURE INSERT_CONTACT 
(
  IN_FIRM_ID IN contact.firm_id%TYPE
, IN_SURNAME IN contact.surname%TYPE
, IN_LASTNAME IN contact.lastname%TYPE 
, IN_DIVISION IN contact.division%TYPE
, IN_PHONE IN contact.phone%TYPE
, IN_EMAIL IN contact.email%TYPE 
, IN_TYPE IN contact.type%TYPE 
, IN_CREAT_ID IN contact.creat_id%TYPE 
, IN_CREAT_DAT IN contact.creat_dat%TYPE DEFAULT SYSTIMESTAMP
, IN_ACT_ID IN contact.act_id%TYPE DEFAULT NULL
, IN_ACT_DAT IN contact.act_dat%TYPE DEFAULT NULL
) AS 
BEGIN
    UPDATE contact
        SET act_id = in_creat_id, act_dat = in_creat_dat
            WHERE firm_id = IN_FIRM_ID
            AND act_dat IS NULL
            AND ((decode(type,IN_type,0,1) = 0
                AND type = 'Shipping')
                OR (decode(type,IN_type,0,1) = 0
                AND type <> 'Shipping'))            
            AND (decode(type,in_type,0,1) = 1
                OR decode(surname,in_surname,0,1) = 1
                OR decode(lastname,in_lastname,0,1) = 1
                OR decode(division,in_division,0,1) = 1
                OR decode(phone,in_phone,0,1) = 1
                OR decode(email,in_email,0,1) = 1);
    dbms_output.put_line('updated: '||sql%rowcount);
    INSERT INTO contact 
            (firm_id, surname, lastname, division, phone, email, type, creat_id, creat_dat) 
            VALUES(in_firm_id, in_surname,in_lastname,in_division,in_phone,in_email,in_type,in_creat_id, in_creat_dat);
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
                dbms_output.put_line('<<<No Insert: '||sqlerrm);
END INSERT_CONTACT;


One problem remains. When the user wants to insert a contact person that is already in the table, the update is permitted by my procedure but I do not know how to prevent the insert too (since this is not covered by the DUP_VAL_ON_INDEX). 
EXECUTE insert_contact(12,'Tina','Bird',null,null,null,'Marketing',1,to_date('11.01.2015','DD.MM.YYYY'));

This entry should not be allowed since the person is already in the table.
Maybe a self defined exception that checks whether the person is already in the database could be a solution?
Thanks for your help! :-)
Chris Saxon
April 27, 2020 - 4:31 pm UTC

The problem is many people can have the same first and last names!

You need to find unique attributes for these contacts; email is a good candidate.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library