oracle - PL/SQL assinging values to a variable using cursor columns -

i trying take statistics against emp table, create table & rows inserted given below. trying develop store procedure columns particular table oracle all_tab_columns & generate statistics.

the pl/sql block of code given below compiling not returning records when run it. can please let me know might getting wrong -

`is "distinct_cnt := 'select count(distinct (' || table_rec.column_name || ')) from' || table_rec.table_name;"`  

a correct way of assigning result variable.

create table emp( empno  number(4,0), ename    varchar2(10), job      varchar2(9), mgr      number(4,0),  hiredate date,  sal      number(7,2), comm     number(7,2),  deptno   number(2,0) )  insert emp values( 7839, 'king', 'president', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10); insert emp values( 7698, 'blake', 'manager', 7839, to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30); insert emp values( 7782, 'clark', 'manager', 7839, to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10); insert emp values( 7566, 'jones', 'manager', 7839, to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20); insert emp values( 7788, 'scott', 'analyst', 7566, to_date('13-jul-87','dd-mm-rr') - 85, 3000, null, 20); insert emp values( 7902, 'ford', 'analyst', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20); insert emp values( 7369, 'smith', 'clerk', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20); insert emp values( 7499, 'allen', 'salesman', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30); insert emp values( 7521, 'ward', 'salesman', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30); insert emp values( 7654, 'martin', 'salesman', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30); insert emp values( 7844, 'turner', 'salesman', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30); insert emp values( 7876, 'adams', 'clerk', 7788, to_date('13-jul-87', 'dd-mm-rr') - 51, 1100, null, 20); insert emp values( 7900, 'james', 'clerk', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30); insert emp values( 7934, 'miller', 'clerk', 7782, to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10);   commit;   create or replace procedure p_profiling (v_tablename in varchar2)  cursor c1 select table_name, column_name all_tab_columns table_name='v_tablename';   rec_cnt number; distinct_cnt number; is_valid number; not_null number;  begin  table_rec in c1 loop  rec_cnt := 'select count(*) from' ||  table_rec.table_name;  distinct_cnt := 'select count(distinct (' || table_rec.column_name || ')) from' || table_rec.table_name;  is_valid := 'select count(*) '||table_rec.table_name ||'where'|| table_rec.column_name ||' not null             , length('||table_rec.column_name||') = length(ltrim(rtrim('||table_rec.column_name||')))';  not_null := 'select count(*)  from'|| table_rec.table_name ||'where '|| table_rec.column_name ||'is not null';  dbms_output.put_line ('rec_cnt:'||rec_cnt||' '||'distinct_cnt:'||distinct_cnt||' '||'is_valid:'||is_valid ||'  '||'table_name'||table_rec.table_name||' '||'column_name'||table_rec.column_name);  end loop;  end; 

for executing sql statements created dynamically, need use execute immediate:

create or replace procedure p_profiling (v_tablename in varchar2)  cursor c1  select table_name, column_name all_tab_columns table_name='v_tablename';   rec_cnt number; distinct_cnt number; is_valid number; not_null number;   begin  table_rec in c1  loop  if c1%rowcount = 1   execute immediate  'select count(*) from' ||   table_rec.table_name  rec_cnt;  end if;  execute immediate  'select count(distinct (' ||  table_rec.column_name || ')) from' ||  table_rec.table_name distinct_cnt;  execute immediate 'select count(*) '|| table_rec.table_name ||'where'|| table_rec.column_name ||'  not null , length('||table_rec.column_name||') =  length(ltrim(rtrim  ('||table_rec.column_name||')))' is_valid;  execute immediate  'select count(*)  from'||  table_rec.table_name ||'where '|| table_rec.column_name  ||'is not null' not_null;   dbms_output.put_line('rec_cnt:'||rec_cnt||' '||'distinct_cnt:'|| distinct_cnt||' ' ||'is_valid:'||is_valid   ||'  '||'table_name'||table_rec.table_name||' ' ||'column_name'||table_rec.column_name);  end loop;  end;