Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Understanding NVL: INVALID NUMBER in NVL Function

890437Sep 26 2011 — edited Sep 26 2011
Hi,

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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 24 2011
Added on Sep 26 2011
2 comments
13,758 views