This discussion is archived
7 Replies Latest reply: Jan 20, 2013 8:58 PM by 936666 RSS

check data already in table or not.

936666 Newbie
Currently Being Moderated
hi,

begin
Select id into is_id from mytable where id=p_id ;

if is_id=P_id
DBMS_OUTPUT.put_line ('Already Exists');
return;
else

if is_id<>P_id
insert into mytable values (..);
commit;

end if;--1
end if;--2
exception when no_data_found then
dbms_output.put_line('no data found')
end;

if id=1 which is not present in the table , i am getting error as "no data found" ,but i need to insert into mytable.

what is wrong in this query.
  • 1. Re: check data already in table or not.
    Niket Kumar Pro
    Currently Being Moderated
    begin
    Select id into is_id from mytable where id=p_id ;
    above will give you result where is_id=p_id and below will always correct
    if is_id=P_id 
    DBMS_OUTPUT.put_line ('Already Exists');
    return;
    else
    how it will go to condition where is_id<>p_id
  • 2. Re: check data already in table or not.
    936666 Newbie
    Currently Being Moderated
    previous query will do it right

    if is_id <> P_id ---(is_id != P_id)

    insert into mytable values (..);
    commit;

    end if;--1
    end if;--2

    exception when no_data_found then
    dbms_output.put_line('no data found')
    end;

    "<" ">" is not showing in the screen

    Edited by: 933663 on Jan 18, 2013 5:08 AM

    Edited by: 933663 on Jan 18, 2013 5:10 AM
  • 3. Re: check data already in table or not.
    JohnWatson Guru
    Currently Being Moderated
    A much simpler structure would rely on the constraint to prevent duplicates:
    orcl> create procedure newd as begin
      2  insert into dept(deptno) values (99);
      3  end;
      4  /
    
    Procedure created.
    
    orcl> exec newd;
    
    PL/SQL procedure successfully completed.
    
    orcl> exec newd;
    BEGIN newd; END;
    
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SCOTT.PK_DEPT) violated
    ORA-06512: at "SCOTT.NEWD", line 2
    ORA-06512: at line 1
    
    
    orcl>
    all you need do is add an exception clause to catch the ora-00001.

    Edited by: JohnWatson on Jan 18, 2013 1:07 PM
    typo.
  • 4. Re: check data already in table or not.
    John Spencer Oracle ACE
    Currently Being Moderated
    Since Oracle will always raise no data found if a select into query returns no rows, you could also reverse John's logic and do something like:
    begin
       Select id into is_id
       from mytable
       where id=p_id ;
       < do updates or whatever you need to do with exiisting row>
    exception
       when no_data_found then
          insert into mytable values (..);
    end;
    John
  • 5. Re: check data already in table or not.
    rp0428 Guru
    Currently Being Moderated
    >
    exception when no_data_found then
    dbms_output.put_line('no data found')
    end;

    if id=1 which is not present in the table , i am getting error as "no data found" ,but i need to insert into mytable.
    >
    The answer is right in front of you!

    Seems pretty obvious that you could just move the INSERT statement to the exception handler.

    Instead of printing out 'no data found' when you want to do the insert just do the insert!
  • 6. Re: check data already in table or not.
    jgarry Guru
    Currently Being Moderated
    You might want to use a [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_9016.htm#SQLRF01606]merge statement.
  • 7. Re: check data already in table or not.
    936666 Newbie
    Currently Being Moderated
    thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points