1

I have a PL/SQL block where I am trying to get the count of filled and not filled values for each column of a table i.e. "demo_table".

select  count(*) into v_count from demo_table 
where owner='OWNER_1' and(rec.COLUMN_NAME is   null OR 
REGEXP_LIKE(rec.COLUMN_NAME,'^-$') OR REGEXP_LIKE (rec.COLUMN_NAME,'^-$'));

I am displaying the v_count through dbms_output. But I am not able to get the count.

This is what I am doing:

declare
  CURSOR C1 IS
  SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS
  WHERE TABLE_NAME ='demo_table';

  v_count number(10);
begin    
  for rec in c1 loop
    dbms_output.put_line(rec.COLUMN_NAME);
    select count(*) into v_count from demo_table
    where owner='OWNER_1'
    and(rec.COLUMN_NAME is null
      OR REGEXP_LIKE(rec.COLUMN_NAME,'^-$')
      OR REGEXP_LIKE (rec.COLUMN_NAME,'^-$'));
    dbms_output.put_line(v_count);
  end loop;   
  dbms_output.put_line(v_count);    
end;
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
nigam
  • 11
  • 1
  • 1
    What does 'not able to get the count' mean - it always shows zero? You're looking at the column name in your where clause, not the values of the column in the table. You'd have to switch to dynamic SQL to do this in a loop like that... – Alex Poole Mar 26 '17 at 08:50
  • 1
    [Something like this](http://stackoverflow.com/a/17613265/266304), with your filter added to the dynamic query. There are other examples around too. You may be able to do it without PL/SQL with some XML magic, too. – Alex Poole Mar 26 '17 at 09:00
  • Maybe you can add at the begin `SET SERVEROUTPUT ON`. – fingerprints Mar 27 '17 at 16:31

0 Answers0