title: PL/SQL assinging values to a variable using cursor columns
body: <p>I am trying to take some statistics against the Emp table, the create table &amp; the rows inserted are given below. I am trying to develop a store procedure which will get all the columns for a particular table from oracle <code>ALL_TAB_COLUMNS</code> &amp; I will generate the statistics.</p>
  
  <p>The PL/SQL block of code given below is compiling but not returning any records when I run it. Can anyone please let me know where I might be getting wrong - </p>
  
  <pre><code>`is "distinct_cnt := 'SELECT COUNT(DISTINCT (' || table_rec.COLUMN_NAME || ')) FROM' || table_rec.TABLE_NAME;"` 
  </code></pre>
  
  <p>a correct way of assigning the result to a variable.</p>
  
  <pre><code>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 into emp values( 7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
  insert into emp values( 7698, 'BLAKE', 'MANAGER', 7839, to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
  insert into emp values( 7782, 'CLARK', 'MANAGER', 7839, to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
  insert into emp values( 7566, 'JONES', 'MANAGER', 7839, to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
  insert into emp values( 7788, 'SCOTT', 'ANALYST', 7566, to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
  insert into emp values( 7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20);
  insert into emp values( 7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20);
  insert into emp values( 7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30);
  insert into emp values( 7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30);
  insert into emp values( 7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30);
  insert into emp values( 7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30);
  insert into emp values( 7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20);
  insert into emp values( 7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30);
  insert into 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)
  IS
  
  cursor c1 is
  select TABLE_NAME,
  COLUMN_NAME
  from
  ALL_TAB_COLUMNS
  where TABLE_NAME='V_tablename';
  
  
  REC_CNT NUMBER;
  distinct_cnt NUMBER;
  is_valid NUMBER;
  not_null NUMBER;
  
  BEGIN
  
  FOR 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(*) FROM '||table_rec.TABLE_NAME ||'WHERE'|| table_rec.COLUMN_NAME ||' IS NOT NULL
              AND 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;
  </code></pre>