Deepak Deepak - 5 days ago 6
SQL Question

why oracle user defined function shows error in Oracle Sql Developer 4.0.3.16

In Oracle SQL Developer Version 1.5.4, same code doesn't give any error.
I am currently connected to oracle 11g database server.

create or replace function getdate
RETURN VARCHAR2 IS
v_datevalue date;
begin
v_datevalue := '01-APR-2015';
return (cast(v_datevalue as varchar2));
end;
/


enter image description here

Answer
v_datevalue date;
begin
v_datevalue := '01-APR-2015';

This is a bug in your code. Never ever depend on the implicit data type conversion. Your issue is not with the tool but the fact that you are assigning a STRING to a DATE data type.

You might be just lucky to see an implicit conversion without an error, if and only if the locale-specific NLS_DATE_FORMAT is exactly same as that you have used in the literal.

it compiled but why it mark as an error in worksheet

The error will be at run time and not at compile time. The data type conversion happens at run time and not at compile time. So, your function would compile successfully, however, it will error out when you execute it.

Root cause

SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY';

Session altered.

SQL>
SQL> CREATE OR REPLACE
  2    FUNCTION GETDATE
  3      RETURN VARCHAR2
  4    IS
  5      v_datevalue DATE;
  6    BEGIN
  7      v_datevalue := '01-APR-2015';
  8      RETURN (CAST(v_datevalue AS VARCHAR2));
  9    END;
 10    /

Function created.

SQL>
SQL> SELECT GETDATE FROM DUAL;
SELECT GETDATE FROM DUAL
       *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "LALIT.GETDATE", line 6


SQL>

So, what happened? Changing the NLS_DATE_FORMAT clearly shows the bug in the code.

Implicit date conversion

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';

Session altered.

SQL>
SQL> SELECT GETDATE FROM DUAL;

GETDATE
-----------------------------------------------------
01-APR-2015

SQL>

Now, with the same format as that of the literal, the code managed to give an output because Oracle has successfully done the implicit data type conversion.

Fix

Always use TO_DATE to explicitly convert a literal into DATE. Never depend on the implicit conversion. TO_DATE at individual SQL statement level, will override the NLS settings of your client tool.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY';

Session altered.

SQL>
SQL> CREATE OR REPLACE
  2    FUNCTION GETDATE
  3      RETURN VARCHAR2
  4    IS
  5      v_datevalue DATE;
  6    BEGIN
  7      v_datevalue := TO_DATE('01-APR-2015','DD-MON-YYYY');
  8      RETURN (CAST(v_datevalue AS VARCHAR2));
  9    END;
 10    /

Function created.

SQL>
SQL> SELECT GETDATE FROM DUAL;

GETDATE
-------------------------------------------------------------
04/01/2015

SQL>
Comments