Understanding NVL: INVALID NUMBER in NVL Function
890437Sep 26 2011 — edited Sep 26 2011Hi,
The below query is a part of a cursor which takes in p_po_number as input with datatype same as segment1 of po_headers_all (varchar2(20)). The columns event_system_id1 to event_system_id4 are varchar2(240). In most cases p_po_number will be null as the program is scheduled and the input parameter of p_po_number is not passed. In effect, for most cases, the below query will end like event_system_id1 = NVL(NULL,event_system_id1)
I am getting an error of INVALID NUMBER
SELECT TO_NUMBER (event_system_id1)
FROM c_fnd_intf_transact_q
WHERE event_system_id2 = :p_organization_code
AND event_system_id4 =
DECODE (:p_po_type,
:vgc_internal, 'XXX',
:vgc_standard
)
AND event_system_id1 =
NVL (p_po_number, event_system_id1)
----------------------------------------------------------------------------------------------------
I modified the query to
SELECT event_system_id1
FROM c_fnd_intf_transact_q
WHERE event_system_id2 = :p_organization_code
AND event_system_id4 =
DECODE (:p_po_type,
:vgc_internal, 'XXX',
:vgc_standard
)
AND event_system_id1 =
NVL (p_po_number, event_system_id1)
which still threw the same error thus ruling out the cause of the issue being that to_number leaving only the implicit conversions of NVL.
---------------------------------------------------------------------------------------------------------
I further modified the query to
SELECT TO_NUMBER (event_system_id1)
FROM c_fnd_intf_transact_q
WHERE event_system_id2 = :p_organization_code
AND event_system_id4 =
DECODE (:p_po_type,
:vgc_internal, 'XXX',
:vgc_standard
)
AND p_po_number is not null
which gave me the desired result in a round about way.
What confuses me here is both p_po_number and event_system_id1 are of varchar2 type. Why am I getting the error of INVALID NUMBER here and why is NVL converting the second parameter NUMBER datatype when the first one is VARCHAR2 even though null.
I am working in Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.
P.S: The column event_system_id1 is not restricted to store numbers alone. It also contains character values though I use it only for storing number as it is shared table.