Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
    
It is often a good idea to include the specific error message(s) that you are getting. I know you've been getting helpful answers already, but generally speaking, including the error message makes answering the question easier. – Michael Kjörling Mar 14 at 14:58
    
sure!! Thanks @MichaelKjörling – Anant Tyagi Mar 16 at 10:12
up vote 6 down vote accepted

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;
share|improve this answer

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.

share|improve this answer
    
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 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 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 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 at 17:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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