How to insert Null value into table through Execute immediate...?
orabitsFeb 13 2006 — edited Feb 13 2006HI TOM,
here param is the array type.
...
...
get_nxt_val number;
stmt_str varchar2(4000);
pass_null_for_createdby varchar2(1000);
pass_null_for_createddate date;
Begin
pass_null_for_createdby := NULL;
pass_null_for_createddate := NULL;
stmt_str := 'INSERT into LISTMASTER values (' || grp_id || ', ';
For get_nxt_val in 1 .. param.count loop
stmt_str := stmt_str || CHR(39) || param(get_nxt_val) || CHR(39) || ', ';
End loop;
stmt_str := stmt_str || CHR(39) || pass_null_for_createdby || CHR(39) || ', ' ||
CHR(39) || pass_null_for_createddate || CHR(39) || ', ' ||
CHR(39) || upd_by || CHR(39) || ',' || CHR(39) || sysdate ||
CHR(39) || ');';
...
...
End;
I tried Alternate like ...
stmt_str := stmt_str || pass_null_for_createdby || ', ' ||
pass_null_for_createddate || ', ' ||
CHR(39) || upd_by || CHR(39) || ',' || CHR(39) || sysdate ||
CHR(39) || ');';
I tried another Alternate like ...
stmt_str := stmt_str || 'Null, Null' ||
pass_null_for_createddate || ', ' ||
CHR(39) || upd_by || CHR(39) || ',' || CHR(39) || sysdate ||
CHR(39) || ');';
It is showing error called...
ORA-00911: invalid character
How to insert Null value into table through Execute immediate...?