Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: How to not insert duplicates with oracle

    Hello experts,

    I have the following Stored procedure written in Oracle 9.2 which is working great except that know I need to modify it to avoid duplicate columns. I want to keep this table unique with unique requestor information. Any help would be appreciated....
    Code:
    CREATE OR REPLACE PROCEDURE User_Info_Insert (UserID OUT VARCHAR2,
                                                  name IN VARCHAR2, 
                                                  Email IN VARCHAR2,
                                                  Extension IN VARCHAR2)
                                                 
    IS
    BEGIN
     
         insert into User_Info values  (User_seq.nextval,Name,Email,Extension,sysdate)
          Returning ID INTO UserID;
    END;
    I need it to say if Name, Email and Extension are the same don't insert. But I don't want it to stop executing I want it to continue without producing an error. All I need is if the columns listed are not the same don't insert it and carry on. Any ideas?

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Code:
    CREATE OR REPLACE PROCEDURE User_Info_Insert (UserID OUT VARCHAR2,
                                                  name IN VARCHAR2, 
                                                  Email IN VARCHAR2,
                                                  Extension IN VARCHAR2)
                                                 
    IS
    BEGIN
      INSERT INTO User_Info values (user_seq.nextval, Name,Email,Extension,sysdate) 
        WHERE (Name,Email,Extension) 
        NOT IN (SELECT Name,Email,Extension FROM User_Info)
      Returning ID INTO UserID;
    END;
    I'm not 100% on this because I don't really do Oracle (PL/SQL) but as a MySQL developer i'm taking a guess. Hope it helps or gives you some ideas.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Why not just a unique constraint on Name,Email,Extension and use an exception handler to handle the dup_val_on_index exception ?

  4. #4
    Join Date
    Apr 2006
    Posts
    140

    Red face

    Thanks for the replies everyone but still at a loss. aschk I tried your syntax but it did not work in oracle. I will need to investigate more. JMartinez I am not really sure what you are talking about. Maybe you could help me with this if I explain a bit more.
    I have 2 tables...1 called user_info and has the following fields
    Id NUMBER
    name VARCHAR2
    Email VARCHAR2
    Extension NUMBER
    CRDate DATETIME

    Following the rules of normalization I have another table called hardware
    ID NUMBER
    Date DATETIME
    Equip VARCHAR2
    Purpose VARCHAR2
    CRDATE DATETIME

    I am calling a asp page page and upon a submit button I insert form values into the 2 tables. However, is the same user submits another form I don't want to record his info, I just want the system to insert the rest of the form elements into the hardware table. I don;t want the errors to appear on the screen if it is a duplicate but rather just ignore the insert but continue on to insert into the hardware table. Any ideas? Thanks again

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Just one way to illustate the concept:

    Code:
    create view my_view 
    as
    select Id ,
    name ,
    Email ,
    Extension ,
    CRDate ,
    Date ,
    Equip ,
    Purpose ,
    CRDATE 
    from table1, table2
    where table1.id = table2.id
    
    --assuming there's a primary key on table1.id ...
    
    create or replace trigger my_view_it
    instead of insert 
    on my_view
    begin
    
      begin
        insert into table1
        values (:NEW.Id, :NEW.name, :NEW.Email, :NEW.Extension, :NEW.CRDate);
      exception
        when dup_val_on_index then
          null;    -- don't do anything, this record already exists
        when others then
          raise;
      end;
    
      insert into table2
      values (:NEW.Id, :NEW.Date, :NEW.Equip, :NEW.Purpose, :NEW.CRDate);
    
    end;
    ---=Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •