CODEWITHSUNDEEP
oracle-database
Hydrogen-4
Hydrogen-4

Reputation: 207

Oracle, need help understanding to_number and to_date functions

I been working with a complex view written by some other company in 2005. I am trying to understand what it is doing for reasons beyond this post. By the highly complex nature of this view (over 500 lines of code) I take it that the writers new what they where doing.

I keep finding things like TO_NUMBER(null), TO_DATE(null) in various places.

Seems to me like totally unnecessary use of a function. Is there any technical reasons or advantages that justify why this was design like this?

Upvotes: 1

Views: 234

Answers (4)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

Well, I would prefer CAST(NULL AS DATE) or CAST(NULL AS NUMBER) instead of TO_DATA(NULL), looks more logical in my eyes.

I know two scenarios where such an expression is required. One it the case of UNION, as already stated in the other aswers.

Another scenario is the case of overloaded procedures/functions, for example:

CREATE OR REPLACE PROCEDURE MY_PROC(val IN DATE) AS
BEGIN
   DELETE FROM EMP WHERE HIRE_DATE = val;
END;
/

CREATE OR REPLACE PROCEDURE MY_PROC(val IN NUMBER) AS
BEGIN
   DELETE FROM EMP WHERE EMP_ID = val;
END;
/

Calling the procedure like MY_PROC(NULL); does not work, Oracle does not know which procedure to execute. You must call it like MY_PROC(CAST(NULL AS DATE)); for example.

Upvotes: 1

APC
APC

Reputation: 146269

By default NULL does not have a data type:

SQL> select dump(null) from dual;

DUMP
----
NULL

SQL>

However, if we force Oracle into making a decision it will default to making it a string:

SQL> create or replace view v1 as
select 1 as id
       , null as dt
from dual
/
  2    3    4    5  
View created.

SQL> desc v1
 Name           Null?    Type
 -------------- -------- ----------------------------
 ID                      NUMBER
 DT                      VARCHAR2

SQL>

But this not always desirable. We might need to use NULL in a view for a number of reasons (defining an API, filling out a jagged UNION, etc) and so we cast the NULL to another datatype to get the projection we need.

SQL> create or replace view v1 as
select 1 as id
       , to_date(null) as dt
from dual
/
  2    3    4    5  
View created.

SQL> desc v1
 Name           Null?    Type
 -------------- -------- ----------------------------
 ID                      NUMBER
 DT                      DATE

SQL>

Later versions have got smarter with regards to handling UNION. On my 11gR2 database, even though I use the null in first declared query (and that usually drives things) I still get the correct datatype:

SQL> create or replace view v1 as
select 1 as id
       , null as dt
from dual
union all
select 2 as id
       , sysdate as something_else
from dual
/
  2    3    4    5    6    7    8    9  
View created.

SQL> 
SQL> desc v1
 Name           Null?    Type
 -------------- -------- ----------------------------
 ID                      NUMBER
 DT                      DATE

SQL>

Upvotes: 2

Jon Heller
Jon Heller

Reputation: 36902

Explicitly casting NULL may be left over from 8i, or to workaround a bug, or as ammoQ said, "superstitious".

In some old and rare cases the implicit conversion of NULL in set operations caused errors like ORA-01790: expression must have same datatype as corresponding expression. I can't find any great references for this old behavior, but Google returns a few results that claim a query like this would fail in 8i:

select 'a' a from dual
union
select null a from dual;

And there is at least one similar bug, "Bug 9456979 Wrong result from push of NVL / DECODE into UNION view with NULL select list item - superceded".

But don't let 16 year-old software and some rare bug dictate how to program. And don't think there's a positive correlation between code size programming skill. There's a negative correlation: good programmers will create smaller, more readable code, and won't leave as many mysteries for future coders.

Upvotes: 1

iSaumya
iSaumya

Reputation: 1623

As a oracle PL/SQL programmer, I really don't find any logical reason for doing the things you have specified. The only logical approach to deal with null in oracle is to use nvl(), I really don't find any reason to use TO_NUMBER(null), TO_DATE(null) in a complex view.

Upvotes: 0

Related Questions