sql - Looking NULL values for different Oracle Type

Question

I'm doing collection of two columns(col & val). Into second select col is parameter of other columns and val is value of this column.

declare
  TYPE t_my_list is record(id varchar2(1000), col VARCHAR2(4000),val VARCHAR2(4000));
  TYPE list_3STR is table of t_my_list;    
  v_stmt    VARCHAR2(32000) := 'SELECT id, col, val FROM userA.tableA';
  v_lstmt   VARCHAR2(32000);
  v_ret     list_3STR := list_3STR();
  cDel   number;

begin
 EXECUTE IMMEDIATE v_stmt BULK COLLECT INTO v_ret;

  for i in v_ret.first..v_ret.last loop
   v_lstmt := 'SELECT count(*) FROM userB.tableB 
      WHERE NVL('||v_ret (i).col||', ''<null>'') in ('''||v_ret (i).val||''', ''<null>'') and idB = '''||v_ret (i).id||'''';

   EXECUTE IMMEDIATE v_lstmt INTO cDel;
    If cDel > 0 Then
    --some code
    cDel = 0;   
    end if;
  end loop;
end;

But into my select statement I can have null so I'm using NVL. Also, as I can have number so I need to use convert to_char('||v_ret (i).col||'). Also, type of column is number, RAW, date etc..

My question:

• is other posibility then NVL?

• if not, has oracle default converter? (all types need to be Varchar2)

Answer 1

You can change your code to do:

   v_lstmt := 'SELECT count(*) FROM userB.tableB WHERE id = '''||v_ret (i).id||''''
     || ' and ('||v_ret (i).col||' is null or '||v_ret (i).col||' = :val)';

   EXECUTE IMMEDIATE v_lstmt INTO cDel using v_ret (i).val;

That checks that the column is null or matches the supplied val, and uses a bind variable to supply the value to check to cut down parsing a bit.

However this still relies on implicit conversion, so if you had a date value in the table for instance you'd be relying on your NLS settings to convert it to match the target table column type.

You can use the all_tab_columns view to find the data type of the target column and do explicit conversion of the val to that type before binding. A more involved but possibly more robust approach would be to use dbms_sql for the inner dynamic SQL instead of execute immediate.

The outer query doesn't seem to need to be dynamic though, you coudl do:

declare
  v_lstmt   VARCHAR2(32000);
  cDel   number;
begin
  for rec in (SELECT id, col, val FROM tableA) loop
    v_lstmt := 'SELECT count(*) FROM tableB WHERE id = '''||rec.id||''''
      || ' and ('||rec.col||' is null or '||rec.col||' = :val)';


    dbms_output.put_line(v_lstmt);
    EXECUTE IMMEDIATE v_lstmt INTO cDel using rec.val;
    If cDel > 0 Then
      --some code
      cDel := 0;   
    end if;
  end loop;
end;
/