Variable behavior in Oracle PL/SQL block

Question

I have two versions of an Oracle PL/SQL block:

declare
  rec_count number;
  col_nm sys.all_tab_columns.column_name%type;
  
begin
  for col in (select column_name
              from sys.all_tab_columns
              where table_name = 'WEID_DIR_PERSON_00_MV'
              order by column_name)
    loop
      col_nm := col.column_name;
      select count(*) into rec_count
      from csuban.weid_dir_person_00_mv a
      where a.account_type = 'P'
        and a.active_flag_eiddata = 'Y'
        and a.admin_acct_flag <> 'Y'
        and col_nm <> (select col_nm
                       from csuban.midp_weid_dir_person_mv b 
                       where b.account_type = 'P'
                         and b.active_flag_eiddata = 'Y'
                         and b.admin_acct_flag <> 'Y'
                         and a.csu_id = b.csu_id);
      dbms_output.put_line(col_nm||': '||rec_count);
    end loop;
end;

declare
  rec_count number;
  col_nm sys.all_tab_columns.column_name%type;
  sqlstring varchar(4000);
  
begin
  for col in (select column_name
              from sys.all_tab_columns
              where table_name = 'WEID_DIR_PERSON_00_MV'
              order by column_name)
    loop
      col_nm := col.column_name;
      sqlstring := 'select count(*) 
                    from csuban.weid_dir_person_00_mv a
                    where a.account_type = ''P''
                      and a.active_flag_eiddata = ''Y''
                      and a.admin_acct_flag <> ''Y''
                      and '||col_nm||' <> (select '||col_nm||'
                                             from csuban.midp_weid_dir_person_mv b 
                                             where b.account_type = ''P''
                                               and b.active_flag_eiddata = ''Y''
                                               and b.admin_acct_flag <> ''Y''
                                               and a.csu_id = b.csu_id)';
      EXECUTE IMMEDIATE (sqlstring) into rec_count;
      dbms_output.put_line(col_nm||': '||rec_count);     
    end loop;
end;

The first version does not work as intended. I get a count of zero for every value of the variable col_nm. The second version works perfectly with accurate counts for each value of col_nm.

Why does the col_nm variable behave differently in each version?

Answer 1

In the first block you have

and col_nm <> (select col_nm ...`

col_nm is a variable in your script. In the loop you put a column name into it. Let's say this column name is mycolumn. Then you have

and 'mycolumn' <> (select 'mycolumn' ...

The string 'mycolumn' is never <> 'mycolumn'. So there is no match.

In the second block you build a SQL string

'select ... where ... and ' || col_nm || ' <> (select ' || col_nm || '...'

This becomes

'select ... where ... and mycolumn <> (select mycolumn ...'

which SQL string that you then execute. It compares a weid_dir_person_00_mv.mycolumn with a midp_weid_dir_person_mv.mycolumn, provided there is a column mycolumn in both tables. If the table midp_weid_dir_person_mv doesn't contain such a column, you have again a value (namely weid_dir_person_00_mv.mycolumn) compared to itself.