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)

Please post text rather than images. And include the result you currently see, and what you want to see if they are different. Is your null handling supposed to be saying that the target column can be null or match the target val? — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2018-08-03T08:04:04

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;
/

Looking NULL values for different Oracle Type

2 Answers