How to create a IF NOT EXISTS query in Oracle?

Question

I'm trying to create a IF NOT EXISTS query in Oralce like this:

DECLARE
   cnt   NUMBER;
BEGIN
   SELECT COUNT(*) INTO cnt FROM MY.MYTABLE
    WHERE MYFIELD = 400;

   IF (cnt = 0)
   THEN
      SELECT 'NO ROWS' AS FAIL FROM DUAL;
   ELSE
      SELECT 'SOME ROWS' AS PASS FROM DUAL;
   END IF;
END;

but this fails for

ORA-06550: line 9, column 7:
PLS-00428: an INTO clause is expected in this SELECT statement
ORA-06550: line 11, column 7:
PLS-00428: an INTO clause is expected in this SELECT statement

Why, what is wrong with this query?

The error messages gave you all the info you needed: line 9, column 7 and line 11, column 7 — Jeffrey Kemp
– Jeffrey Kemp, Commented Feb 6, 2014 at 5:07

hkutluay · Accepted Answer · 2014-02-05 12:06:38Z

5

Use into to assign value to variable.

DECLARE
   cnt   NUMBER;
   res   NVARCHAR2(50);
BEGIN
   SELECT COUNT(*) INTO cnt FROM MY.MYTABLE
    WHERE MYFIELD = 400;

   IF (cnt = 0)
   THEN
      SELECT 'NO ROWS' INTO RES FROM DUAL;
   ELSE
      SELECT 'SOME ROWS' INTO RES FROM DUAL;
   END IF;
END;

Also you can directly assign value to variable like

IF (cnt = 0)
       THEN
          res := 'NO ROWS';
       ELSE
          res := 'SOME ROWS';
       END IF;

Best way is just using this sql

SELECT DECODE(COUNT(*), 0, 'NO ROWS','SOME ROWS')  FROM MY.MYTABLE
    WHERE MYFIELD = 400;

answered Feb 5, 2014 at 12:06

hkutluay

6,9742 gold badges37 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

smnbbrv · Accepted Answer · 2014-02-05 12:12:56Z

2

I prefer this way of checking for existence:

declare
  dummy      varchar2(1);
begin
  select null into dummy
  from MY.MYTABLE
  where MYFIELD = 400
    and rownum = 1;

  dbms_output.put_line('some rows'); -- some rows
exception
  when no_data_found then
    dbms_output.put_line('no rows'); -- no rows
end;

in my opinion it is better because count() can work quite slow for big amounts of data.

answered Feb 5, 2014 at 12:12

smnbbrv

24.7k9 gold badges85 silver badges115 bronze badges

Comments

René Nyffenegger · Accepted Answer · 2014-02-05 12:05:54Z

1

As the error message indicates you need a variable into which you can select the value:

select 'NO ROWS' as fail into v_status from dual;

Of course, you need to previously declare the variable:

declare
    v_status varchar2(10);
...

But, it is probably easier to just assign the value to the variable:

v_status := 'NO ROWS'

answered Feb 5, 2014 at 12:05

René Nyffenegger

40.9k35 gold badges180 silver badges321 bronze badges

Comments

Dmitry Nikiforov · Accepted Answer · 2014-02-05 12:21:06Z

1

SQL> create table t (x int);


SQL> insert into t values(1);


SQL> commit;

SQL> set serveroutput on
SQL> declare
  2   res varchar2(10);
  3  begin
  4    select nvl((select 'SOME ROWS' from t where x =1 and rownum <=1), 'NO ROWS')
  5    into res
  6    from dual;
  7    dbms_output.put_line(res);
  8  end;
  9  /
SOME ROWS                                                                       

SQL> declare
  2   res varchar2(10);
  3  begin
  4    select nvl((select 'SOME ROWS' from t where x = 2 and rownum <=1), 'NO ROWS')
  5    into res
  6    from dual;
  7    dbms_output.put_line(res);
  8  end;
  9  /
 NO ROWS

answered Feb 5, 2014 at 12:21

Dmitry Nikiforov

3,06816 silver badges14 bronze badges

Comments

user3275942 · Accepted Answer · 2014-02-05 16:28:15Z

1

Sounds a little bit strange maybe, but I prefer not to use and rownum=1 but and rownum < 2; and never using exception as part of business logic.

declare
  cnt   NUMBER;
  res   NVARCHAR2(50);
begin
  select COUNT(*) 
  into cnt
  from MY.MYTABLE
  where MYFIELD = 400
    and rownum < 2;

  IF (cnt = 0) THEN
    RES := 'NO ROWS';
  ELSE
    RES :='SOME ROWS';
  END IF;
end;

edited Feb 5, 2014 at 16:28

answered Feb 5, 2014 at 15:58

user3275942

293 bronze badges

Collectives™ on Stack Overflow

How to create a IF NOT EXISTS query in Oracle?

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related