7 Replies Latest reply: Jan 20, 2013 10:58 PM by 936666 RSS

    check data already in table or not.

    936666
      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
          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
            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
              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
                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
                  >
                  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
                    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
                      thanks