Oracle – How to Check if Table Has Rows

oracleplsql

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?

Best Answer

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;

Related Solutions

Referencing PL/SQL variables in FOR loop

The error in you script is that the script now expects a table named tablename, having a column named columnname. In this case you don't know the table and column names so you should use dynamic sql to run this. Next to that, if possible, forget about LONG and implement lobs instead.

For docu see http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm#i13057

sample code slightly modified to fit your needs:

decLARE
  TYPE EmpCurTyp  IS REF CURSOR;
  v_emp_cursor    EmpCurTyp;
  v_stmt_str      VARCHAR2(200);
  z               long;
  x               number := 0;
  a               number;
  tablename       varchar2(100):= 'dba_views';
  columnname       varchar2(100):= 'text';
BEGIN
  -- Dynamic SQL statement
  v_stmt_str := 'SELECT '||columnname||' from '||tablename;
  -- Open cursor
  OPEN v_emp_cursor FOR v_stmt_str;
  -- Fetch rows from result set one at a time:
  LOOP
      FETCH v_emp_cursor INTO z;
      EXIT WHEN v_emp_cursor%NOTFOUND;
      a:=UTL_RAW.LENGTH (UTL_RAW.CAST_TO_RAW(z));
      x:= x+a;
  END LOOP;
  -- Close cursor:
  CLOSE v_emp_cursor;
  dbms_output.put_line (x);
END;
/

Using a sequence created in the same script in Oracle

Something like this:

declare 
   counter integer;
begin
   select count(*) 
      into counter
   from user_sequences
   where sequence_name = 'MY_SEQUENCE';

   if (counter = 0) then 
      execute immediate 'create sequence my_sequence';
   end if;
end;
/

insert into my_table (id, some_col)
values
(my_sequence.nextval, 42)
/

EDIT: If the insert is in the same block as the create then it will need to be done as an execute immediate as well else the Sequence won't be recognised. If they're in separate blocks then it should work fine.

Best Answer

Related Solutions

Referencing PL/SQL variables in FOR loop

Using a sequence created in the same script in Oracle

Related Question