Oracle nvl need to insert null into a number field

Question

This is a fallback script for a delete script i am running. Here is the query i am using to generate the insert statements.

select 'insert into remark_element(ELEMENTID, 
                                   REMARKID, 
                                   VALUE, 
                                   POSITION, 
                                   INFO_TYPE, 
                                   SCRIPTID)
                           values('||elementid||',
                                  '||remarkid||',
                                '''||nvl(value,'null')||''',
                                  '||position||',
                                  '||nvl(info_type,null)||',
                                  '||nvl(scriptid,null)||''||')' 
                          from remark_element 
                          where elementid in(....

This is the result of that query:

insert into remark_element(ELEMENTID,
                           REMARKID, 
                           VALUE, 
                           POSITION, 
                           INFO_TYPE, 
                           SCRIPTID)     
                    values(29650520,
                           20263860,
                          '0/877-426-6251-A',
                           1,,);

running this gives ORA-00936: missing expression because of the empty info_type and scriptid, fields, which are both numbers and are allowed to be nullable. i need the generated query to say

insert into remark_element(ELEMENTID, 
                           REMARKID, 
                           VALUE, 
                           POSITION, 
                           INFO_TYPE, 
                           SCRIPTID) 
                    values(29650520,
                           20263860,
                          '0/877-426-6251-A',
                           1,
                           null,
                           null);

when info_type and scriptid are null. i have tried doing nvl(info_type,'null') but get ORA-01722: invalid number for trying to put a string into a number field. how can i manipulate this to return as null instead of ,, when the info_type or scriptid is null

score 2 · Answer 1 · answered Oct 27 '15 at 01:26

2

Since your output is ultimately a string, just have both nvl arguments be a string by using to_char to cast your column to a string:

nvl(to_char(info_type), 'null')

answered Oct 27 '15 at 01:26

sstan

35,425
6
48
66

Gordon Linoff · Answer 2 · 2015-10-27T02:02:56.113

0

You are generating code. I prefer to use coalesce(), because it is ANSI standard. The second argument should be the string 'null' rather than the constant null.

select 'insert into remark_element(ELEMENTID, REMARKID, VALUE, POSITION, INFO_TYPE, SCRIPTID) values('||elementid || ',' || remarkid || ',''' || nvl(value,'null') || ''',' ||position||',' ||
coalesce(cast(info_type as varchar2(255)), 'null') || ',' || coalesce(cast(scriptid as varchar2(255)), 'null') || ''||')'
from remark_element where elementid in(....

edited Oct 27 '15 at 02:02

answered Oct 27 '15 at 01:10

Gordon Linoff

1,242,037
58
646
786

coalesce returns the first non null expression in the list, so we still have the same issue that we had of returning a string when oracle is expecting a number. i ran your query and still got the same error – rtd353 Oct 27 '15 at 01:22
slightly different error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR – rtd353 Oct 27 '15 at 01:22
@Gordon, both `NVL` and `coalesce` are strict about data types; you cannot compare a `NUMBER` column with a `CHAR` literal (that cannot be implicitly cast to a `NUMBER`) like that. – Mick Mnemonic Oct 27 '15 at 01:38

score 0 · Answer 3 · answered Oct 27 '15 at 02:01

An alternative to @sstan's answer is to use the decode function which is not as sensitive as NVL or coalesce when it comes to mixing different data types:

decode(info_type, null, 'null', info_type)

The above statement inspects the value of info_type and returns 'null' if the value was null; otherwise the inspected value itself is returned.

score 0 · Answer 4 · answered Oct 27 '15 at 02:13

Try this, work for me:

select 'insert into remark_element(ELEMENTID, REMARKID, VALUE, POSITION, INFO_TYPE, SCRIPTID) values('||elementid||','||remarkid||','''||nvl(value,'null')||''','||position||','||
nvl(info_type,'null')||','||nvl(scriptid,'null')||''||')' from remark_element where elementid = 29650520

When you are executing the query and the Oracle find null the result is nothing, but if use 'null', it will be considered like a string.

So, replace (info_type,null) for (info_type,'null'), with quotation marks.

Wernfried Domscheit · Answer 5 · 2015-10-27T10:00:08.517

Your query must be like this:

select 'insert into remark_element(ELEMENTID, 
                               REMARKID, 
                               VALUE, 
                               POSITION, 
                               INFO_TYPE, 
                               SCRIPTID)
                       values('||elementid||',
                              '||remarkid||',
                              '||nvl2(value,''''||value||'''','null')||',
                              '||position||',
                              '||nvl2(info_type,to_char(info_type), 'null')||',
                              '||nvl2(scriptid,to_char(scriptid), 'null')||')' 
                      from remark_element 
                      where elementid in(....

This will fail with `ORA-01722: invalid number` because the second and third input argument of `NVL2` have different data types. You need to either use `decode` or wrap the column within `to_char()` (`nvl2(info_type, to_char(info_type), 'null')`). — Mick Mnemonic, Oct 27 '15 at 09:50

Oracle nvl need to insert null into a number field

5 Answers5