Looking Null Values For Different Oracle Type
Solution 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;
beginfor 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 >0Then--some code
cDel :=0;
end if;
end loop;
end;
/
Solution 2:
If I understood your question correctly, you want to include situations when v_ret (i).col is NULL.
If so, you can try using the following code in your select statement:
WHERE ('||v_ret (i).col||'isnull OR '||v_ret (i).col||' = '||v_ret (i).val||')
instead of:
WHERE NVL('||v_ret (i).col||', ''<null>'') in ('''||v_ret (i).val||''', ''<null>'')
As a default converter you may try using CAST function: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/CAST.html
Post a Comment for "Looking Null Values For Different Oracle Type"