6

I am completely new to PL/SQL. I have written the following PL/SQL script. But it doesn't execute and gives compilation error:

set serveroutput on SIZE 1000000;
IF EXISTS (select * from my_table)
begin
dbms_output.put_line('has rows');
end;
else
begin
dbms_output.put_line('no rows');
end;

Can anyone tell me what is wrong with this?

How can I do this?

0
6

Wrong If syntax. And exists not allowed here. Try this way:

declare
  cnt number;
begin
  select count(*) into cnt from my_table;
  if cnt != 0 then
    dbms_output.put_line('has rows');
  else
    dbms_output.put_line('no rows');
  end if;
end;
0
14

Anonymous PL/SQL blocks don't start with IF. The above code should be in between a begin and end; at least.

EXISTS is an SQL function, it can't be used in PL/SQL like that.

Try something like this:

set serveroutput on
declare
  c number;
begin
  select count(*) into c from my_table where rownum = 1;
  if c != 0 then
    dbms_output.put_line('has rows');
  else
    dbms_output.put_line('no rows');
  end if;
end;
/

Yes, using EXISTS in the query is also possible:

set serveroutput on
declare
  c varchar2(10);
begin
  select case when exists (select * from my_table) then 'has rows' else 'no rows' end into c from dual;
  dbms_output.put_line(c);
end;
/

Note that both the EXISTS and rownum = 1 version will stop on the first row found, and that's the point, so we don't need to read the whole table/index.

5
  • I like your second solution. There's no point in doing a count(*) of the entire table just to determine whether the table has any rows. – Colin 't Hart Mar 14 '16 at 16:21
  • Also, do you actually need to select count(*) into c ... where rownum = 1? Couldn't you just select rownum into c ... where rownum = 1? – Andriy M Mar 14 '16 at 17:15
  • @Andriy M select rownum returns 0 row for an empty table, so the select ... into throws an ORA-01403 error. – Balazs Papp Mar 14 '16 at 17:33
  • Ah, I see. An equivalent Transact-SQL statement (SELECT TOP (1) @c = something FROM ...) would just skip the assignment. I hoped PL/SQL would work the same way. I was wrong then. Thanks for the explanation. – Andriy M Mar 14 '16 at 17:37
  • As of Oracle 12, we can also use LIMIT. – jpmc26 Feb 9 '19 at 3:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.