CODEWITHSUNDEEP
oracle-databaseplsql
user15499442
user15499442

Reputation: 81

oracle database - insert into table having unique key constraint shows 1 row(s) inserted when the value is actually duplicate. - happens in a function

I created a simple table with 2 columns col1 is a number and col2 is an email. Col1 is a pk and is populated through a sequence and i have a unique key constraint on col2. the code looks like this.

CREATE TABLE  "LISTOFPEOPLETOVIEWPAGE5" 
   (    "PKCOL" NUMBER NOT NULL ENABLE, 
    "EMAIL" VARCHAR2(100), 
     CONSTRAINT "LISTOFPEOPLETOVIEWPAGE5_PK" PRIMARY KEY ("PKCOL")
  USING INDEX  ENABLE, 
     CONSTRAINT "UNIQUE_EMAIL" UNIQUE ("EMAIL")
  USING INDEX  ENABLE
   )
/

CREATE OR REPLACE EDITIONABLE TRIGGER  "BI_LISTOFPEOPLETOVIEWPAGE5" 
  before insert on "LISTOFPEOPLETOVIEWPAGE5"               
  for each row  
begin   
  if :NEW."PKCOL" is null then 
    select "LISTOFPEOPLETOVIEWPAGE5_SEQ1".nextval into :NEW."PKCOL" from sys.dual; 
  end if; 
end; 

/
ALTER TRIGGER  "BI_LISTOFPEOPLETOVIEWPAGE5" ENABLE
/

I insert an email id into this table say [email protected] and this succeeds.

Insert into LISTOFPEOPLETOVIEWPAGE5 (email) values ([email protected]);

1 row(s) inserted.

I have a function which will ask the user to provide the email id and store it in a bind variable. this function checks if the value in the bind variable already exists in the table or not. If yes, gives a output as 'email already exists' if the bind variable value is not there in the db, inserts the value into the table.

When i run this function and provide any existing email to the bind variable, i see result as follows: email already exists 1 row(s) inserted.

Code looks like follows:

declare
v_email varchar2(100);
begin

BEGIN
    select email into v_email from LISTOFPEOPLETOVIEWPAGE5 where email = :EMAIL;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        v_email := null;
END;
    if (v_email is null) then
    Insert into LISTOFPEOPLETOVIEWPAGE5 (email) values (:EMAIL);
    else
    dbms_output.put_line('email already exists');
    end if;
end;

output of this function is :
email already exists

1 row(s) inserted.

Please help me where i am going wrong in this function. Why do i see 1 row inserted? I was expecting to get unique key violation error.

Thanks in advance.

Upvotes: 0

Views: 443

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

I recreated your test case and can't reproduce what you are saying.

Sample table, sequence and trigger:

SQL> CREATE TABLE  "LISTOFPEOPLETOVIEWPAGE5"
  2     (    "PKCOL" NUMBER NOT NULL ENABLE,
  3      "EMAIL" VARCHAR2(100),
  4       CONSTRAINT "LISTOFPEOPLETOVIEWPAGE5_PK" PRIMARY KEY ("PKCOL")
  5    USING INDEX  ENABLE,
  6       CONSTRAINT "UNIQUE_EMAIL" UNIQUE ("EMAIL")
  7    USING INDEX  ENABLE
  8     )
  9  /

Table created.

SQL> create sequence LISTOFPEOPLETOVIEWPAGE5_SEQ1;

Sequence created.

SQL> CREATE OR REPLACE TRIGGER  "BI_LISTOFPEOPLETOVIEWPAGE5"
  2    before insert on "LISTOFPEOPLETOVIEWPAGE5"
  3    for each row
  4  begin
  5    if :NEW."PKCOL" is null then
  6      select "LISTOFPEOPLETOVIEWPAGE5_SEQ1".nextval into :NEW."PKCOL" from sys.dual;
  7    end if;
  8  end;
  9  /

Trigger created.

SQL> Insert into LISTOFPEOPLETOVIEWPAGE5 (email) values ('[email protected]');

1 row created.

Anonymous PL/SQL block (I use SQL*Plus and have changed :EMAIL to '&&EMAIL', but that doesn't affect what you are reporting as a problem):

SQL> declare
  2    v_email varchar2(100);
  3  begin
  4    BEGIN
  5      select email into v_email from LISTOFPEOPLETOVIEWPAGE5 where email = '&&EMAIL';
  6    EXCEPTION
  7      WHEN NO_DATA_FOUND THEN
  8        v_email := null;
  9    END;
 10
 11    if (v_email is null) then
 12       Insert into LISTOFPEOPLETOVIEWPAGE5 (email) values ('&&EMAIL');
 13    else
 14       dbms_output.put_line('email already exists');
 15    end if;
 16  end;
 17  /
Enter value for email: [email protected]
email already exists

PL/SQL procedure successfully completed.

SQL> select * From listofpeopletoviewpage5;

     PKCOL EMAIL
---------- ----------------------------------------------------------------------------------------------------
         1 [email protected]

SQL>

Upvotes: 1

Related Questions