title: ORA-06502: PL/SQL: numeric or value error: character to number conversion
body: <p>I have created a function with the code below to retrieve the count of blank spaces for each column in a table...however i encounter the error on executing for multiple rows Cant figure where the error conversion is the issue</p>
  
  <pre><code>set SERVEROUTPUT on
  select  table_name,column_name,
  get_rows( table_name,column_name) cnt
  from all_tab_columns where table_name='TEST' 
  /
  </code></pre>
  
  <p>the error:</p>
  
  <pre><code>ORA-06502: PL/SQL: numeric or value error: character to number conversion error
  ORA-06512: at "JAMES.GET_ROWS", line 31
  ORA-01722: invalid number
  ORA-06512: at "JAMES.GET_ROWS", line 24
  ORA-06512: at "JAMES.GET_ROWS", line 24
  06502. 00000 -  "PL/SQL: numeric or value error%s"
  *Cause:    An arithmetic, numeric, string, conversion, or constraint error
             occurred. For example, this error occurs if an attempt is made to
             assign the value NULL to a variable declared NOT NULL, or if an
             attempt is made to assign an integer larger than 99 to a variable
             declared NUMBER(2).
  *Action:   Change the data, how it is manipulated, or how it is declared so
             that values do not violate constraints.
  
  
  
  
  create or replace function get_rows( l_table in varchar2,l_column_name in varchar2 ) return number
          as
            l_session_name varchar2 ( 30 )   := 'UNDEFINED';
            l_owner        varchar2 ( 30 )   := 'JAMES';
            --l_table        varchar2(30)      := 'TEST';
            l_cnt          NUMBER        default NULL;
            sql_stmt       varchar2 ( 1000 ) := null;
            l_space        varchar2 ( 30 )   := ' ';
          begin
            select sys_context ( 'userenv','session_user' )
            into l_session_name
            from dual;
              for i in
            (select owner, table_name,column_name
            from all_tab_columns
            where table_name = l_table
            and owner         = l_owner
            and column_name = l_column_name
            order by column_id 
            )
            loop
              sql_stmt := 'SELECT COUNT('||i.column_name||') FROM ' || i.owner ||'.' || i.table_name || ' WHERE ' ||i.column_name|| '='||''' ''' ;
  
               execute immediate sql_stmt into l_cnt;
  
              end loop;
             return l_cnt ;
             EXCEPTION
            WHEN OTHERS THEN
              dbms_output.put_line('Returning Error : '||SQLERRM);
              RETURN SQLERRM;
  
          end;
  
      /
  </code></pre>