In my previous article I stressed the importance of using correct data types. However in some cases you may be forced to choose one type when you really need to use two or more. Also, in some cases, you may think you’re using consistent data types but Oracle will insist on changing them anyway, using some kind of Jedi-mind -trick.
These are not the data types you’re looking for.
The first problem can occur when you’re trying to use a single type for multiple input sources. Some might give you a CHAR and others a VARCHAR2 and others a CLOB. If your input will potentially be large enough to require a CLOB, then, obviously, you will need a CLOB type to support it, even if it means it’s overkill for the smaller inputs.
This can be ameliorated with overloading though. Within a package you can define procedures and functions with the same name but differentiated by the parameters they use. Below I create a simple package called ECHO that has multiple functions also named ECHO. As the name suggests they do nothing but return their corresponding inputs, so the DATE version returns a DATE, the VARCHAR2 version returns VARCHAR2, the CLOB version returns a CLOB, etc.
CREATE OR REPLACE PACKAGE echo
IS
FUNCTION echo(p IN CLOB)
RETURN CLOB;
FUNCTION echo(p IN DATE)
RETURN DATE;
FUNCTION echo(p IN NUMBER)
RETURN NUMBER;
FUNCTION echo(p IN TIMESTAMP)
RETURN TIMESTAMP;
FUNCTION echo(p IN TIMESTAMP WITH TIME ZONE)
RETURN TIMESTAMP WITH TIME ZONE;
FUNCTION echo(p IN VARCHAR2)
RETURN VARCHAR2;
END;
CREATE OR REPLACE PACKAGE BODY echo
IS
FUNCTION echo(p IN CLOB)
RETURN CLOB
IS
BEGIN
RETURN p;
END;
FUNCTION echo(p IN DATE)
RETURN DATE
IS
BEGIN
RETURN p;
END;
FUNCTION echo(p IN NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p;
END;
FUNCTION echo(p IN TIMESTAMP)
RETURN TIMESTAMP
IS
BEGIN
RETURN p;
END;
FUNCTION echo(p IN TIMESTAMP WITH TIME ZONE)
RETURN TIMESTAMP WITH TIME ZONE
IS
BEGIN
RETURN p;
END;
FUNCTION echo(p IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
RETURN p;
END;
END;
SQL> select echo.echo('abc') from dual;
ECHO.ECHO('ABC')
------------------------------------------
abc
SQL> select echo.echo(12345) from dual;
ECHO.ECHO(12345)
----------------
12345
SQL> select echo.echo(sysdate) from dual;
ECHO.ECHO
---------
07-APR-20
We can use the DUMP function to confirm each of these is returning a different data type.
QL> select dump(echo.echo('abc')) from dual;
DUMP(ECHO.ECHO('ABC'))
-----------------------------------------------
Typ=1 Len=3: 97,98,99
SQL> select dump(echo.echo(12345)) from dual;
DUMP(ECHO.ECHO(12345))
-----------------------------------------------
Typ=2 Len=4: 195,2,24,46
SQL> select dump(echo.echo(sysdate)) from dual;
DUMP(ECHO.ECHO(SYSDATE))
-----------------------------------------------
Typ=12 Len=7: 120,120,4,7,24,7,23
This seems to work ok, but it’s not entirely reliable. It is possible to overload a function with both CHAR and VARCHAR2 parameters, but it’s not possible to actually use the overloading. I will add a CHAR version of the echo function to the package
FUNCTION echo(p IN CHAR)
RETURN CHAR
IS
BEGIN
RETURN p;
END;
But, if if you try to use it Oracle can’t determine which version you want and raises an exception.
SQL> select echo.echo('abc') from dual;
select echo.echo('abc') from dual
*
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'ECHO' match this call
The error occurs even if you make it explicit what type you intend for the function parameter.
SQL> select echo.echo(cast('abc' as char(3))) from dual;
select echo.echo(cast('abc' as char(3))) from dual
*
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'ECHO' match this call
SQL> select echo.echo(cast('abc' as varchar2(3))) from dual;
select echo.echo(cast('abc' as varchar2(3))) from dual
*
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'ECHO' match this call
For the most part this limitation is minor; but it does deserve noting. If you pass in a padded CHAR value to the VARCHAR2 function, it will process it as a VARCHAR2 preserving the trailing spaces. However, the function’s internal type will be VARCHAR2 which means comparison results will use VARCHAR2 rules and not CHAR rules, so you may get unexpected results in some cases.
It is also interesting to note that some data types may be interchangeable but not equivalent. In the next example the SYSDATE function is used to generate a DATE value, that value is then passed to the ECHO function to also return a DATE value. However, the return types of the two DATES, while functionally equivalent, are different as are their internal byte structures. Note the type change from 13 to 12.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. SQL> set sqlformat ansiconsole SQL> SELECT v, 2 e, 3 DUMP(v), 4 DUMP(e) 5 FROM (SELECT v, echo.echo(v) e 6 FROM (SELECT SYSDATE v FROM DUAL)); V E DUMP(V) DUMP(E) 2020-04-08 00:04:42 2020-04-08 00:04:42 Typ=13 Len=8: 228,7,4,8,0,4,42,0 Typ=12 Len=7: 120,120,4,8,1,5,43
Timestamps (with and without time zones) also change types. Here they change from type 188 to 181.
SQL> alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss.ff6'; Session altered. SQL> SELECT v, 2 e, 3 DUMP(v), 4 DUMP(e) 5 FROM (SELECT v, echo.echo(v) e 6* FROM (SELECT SYSTIMESTAMP v FROM DUAL)); V E DUMP(V) DUMP(E) 2020-04-08 00:08:49.665010 2020-04-08 00:08:49.665010 Typ=188 Len=20: 228,7,4,8,4,8,49,0,80,63,163,39,252,0,5,0,0,0,170,175 Typ=181 Len=13: 120,120,4,8,5,9,50,39,163,63,80,16,60
Even using the 12c feature of PRAGMA UDF or a WITH clause PL/SQL Declarations will not force the data types to be preserved. Here combining the both still causes a type change from 13 to 12.
SQL> WITH 2 FUNCTION udfecho(p IN DATE) 3 RETURN DATE 4 IS 5 PRAGMA UDF; 6 BEGIN 7 RETURN p; 8 END; 9 SELECT d, 10 e, 11 DUMP(d), 12 DUMP(e) 13 FROM (SELECT d, udfecho(d) e 14 FROM (SELECT SYSDATE d FROM DUAL)) 15 / D E DUMP(D) DUMP(E) 2020-04-08 00:41:07 2020-04-08 00:41:07 Typ=13 Len=8: 228,7,4,8,0,41,7,0 Typ=12 Len=7: 120,120,4,8,1,42,8
Most of the time these little quirks will not produce any functional issues; but it’s important to be aware of them so you can understand when the exceptions do happen. When a data type changes it may produce unexpected results as noted above with CHAR vs VARCHAR2, you may also see additional function calls in explain plains where the optimizer canonicalizes arguments to an operator of some condition. These can then suffer the same problems as described in the previous article.