In the last post about SQL macros in 19c I tried to use a subquery as an input for the function. And when I had some DATE calculations in a query, I got ORA-62558: Unsupported data types (DATE)
The result looked somewhat confusing at first, but if you think about it, it is logical and maybe not a bug at all. You should just be very explicit while using subqueries and views(!) in this scenario. And because this behavior is not documented, it is worth sharing, I think.
Let’s see again what happened. Just selecting a date field HIREDATE
is fine. But also using SYSDATE
as a value for new (generated) column EXTRACTION_DT
is not working.
SQL> -- this works
SQL> with subq as (select empno, ename, hiredate
2 from empv
3 fetch first 5 rows only)
4 select * from get_csv(subq);
CSV_ROW
--------------------------------------------------
"EMPNO","ENAME","HIREDATE"
7654,"MARTIN",1981-09-28
7521,"WARD",1981-02-22
7566,"JONES",1981-04-02
7369,"SMITH",1980-12-17
7499,"ALLEN",1981-02-20
6 rows selected.
SQL>
SQL> -- doesn't work
SQL> with subq as (select empno, ename, hiredate, sysdate as extraction_dt
2 from empv
3 fetch first 5 rows only)
4 select * from get_csv(subq);
Error at Command Line : 235 Column : 15
Error report -
SQL Error: ORA-62558: Unsupported data types (DATE) for polymorphic table function
62558. 00000 - "Unsupported data types (%s) for polymorphic table function"
*Cause: Non-scalar data types were used in polymorphic table function.
Only scalar data types are supported.
*Action: Use only scalar data types in polymorphic table function.
SQL>
Please note that you will also have the same error using regular views which define some date calculations:
SQL> create or replace view test_view as
2 select empno, ename, hiredate
3 , sysdate as extraction_dt
4 from empv
5 fetch first 5 rows only;
View TEST_VIEW created.
SQL>
SQL> select * from get_csv(test_view);
Error at Command Line : 450 Column : 15
Error report -
SQL Error: ORA-62558: Unsupported data types (DATE) for polymorphic table function
...
The reason for this is that, in Oracle, DATE is not equal DATE. There is internal proprietary DATE format used for stored dates and an external one used for computations like SYSDATE, TO_DATE
, etc. See How does Oracle store the DATE datatype internally? (Doc ID 69028.1) for details. Consider the following listing. Selecting a date from EMP shows Typ=12 (7 bit), but using SYSDATE
, we got Typ=13 (8 bit)
SQL> select dump(hiredate) dh, dump(sysdate) ds
2 from scott.emp
3 fetch first 1 row only;
DH DS
----------------------------------- -----------------------------------
Typ=12 Len=7: 119,180,12,17,1,1,1 Typ=13 Len=8: 228,7,10,6,20,29,48,0
Now, what is supported with PTF or SQL macros? Since we use DBMS_TF.TABLE_T
as parameter type, we should look inside DBMS_TF
package, simply because the documentation only mentions the data type DATE, without further explanation. Here, we only see the type 12
create or replace NONEDITIONABLE PACKAGE DBMS_TF AS
/*
Package: DBMS_TF
DBMS_TF is the PL/SQL package that provides various utility API's to
support POLYMORPHIC TABLE functions(ptf).
*/
/* Type Codes for supported types */
TYPE_VARCHAR2 CONSTANT PLS_INTEGER := 1;
TYPE_NUMBER CONSTANT PLS_INTEGER := 2;
TYPE_DATE CONSTANT PLS_INTEGER := 12;
TYPE_RAW CONSTANT PLS_INTEGER := 23;
TYPE_ROWID CONSTANT PLS_INTEGER := 69;
TYPE_CHAR CONSTANT PLS_INTEGER := 96;
TYPE_BINARY_FLOAT CONSTANT PLS_INTEGER := 100;
TYPE_BINARY_DOUBLE CONSTANT PLS_INTEGER := 101;
TYPE_CLOB CONSTANT PLS_INTEGER := 112;
TYPE_BLOB CONSTANT PLS_INTEGER := 113;
TYPE_TIMESTAMP CONSTANT PLS_INTEGER := 180;
TYPE_TIMESTAMP_TZ CONSTANT PLS_INTEGER := 181;
TYPE_INTERVAL_YM CONSTANT PLS_INTEGER := 182;
TYPE_INTERVAL_DS CONSTANT PLS_INTEGER := 183;
Workaround
Is there a simple workaround? Just CAST SYSDATE
explicitly as DATE
and the output will be internal DATE (Typ=12)!
SQL> select dump(sysdate) ds, dump(cast (sysdate as date)) dcs
2 from scott.emp
3 fetch first 1 row only;
DS DCS
----------------------------------- -----------------------------------
Typ=13 Len=8: 228,7,10,6,20,33,29,0 Typ=12 Len=7: 120,120,10,6,21,34,30
Now we can test our subquery again and it is just working fine:
SQL> -- works after casting to date
SQL> with subq as (select empno, ename, hiredate
2 , cast(sysdate as date) as extraction_dt
3 from empv
4 fetch first 5 rows only)
5 select * from get_csv(subq);
CSV_ROW
----------------------------------------------------------
"EMPNO","ENAME","HIREDATE","EXTRACTION_DT"
7654,"MARTIN",1981-09-28,2020-10-06
7521,"WARD",1981-02-22,2020-10-06
7566,"JONES",1981-04-02,2020-10-06
7369,"SMITH",1980-12-17,2020-10-06
7499,"ALLEN",1981-02-20,2020-10-06
6 rows selected.
Why had I mentioned polymorphic table functions in the title? Well, using subqueries with PTF works just the same way using DBMS_TF.TABLE_T
and thus, we can expect the same behavior. Just a quick test with a PTF NOOP from Oracle documentation, which actually does nothing:
SQL> with subq as (select empno, ename, hiredate
2 , sysdate as extraction_dt
3 from scott.emp
4 fetch first 5 rows only)
5 select * from noop(subq);
Error at Command Line : 433 Column : 15
Error report -
SQL Error: ORA-62558: Unsupported data types (DATE) for polymorphic table function
62558. 00000 - "Unsupported data types (%s) for polymorphic table function"
*Cause: Non-scalar data types were used in polymorphic table function.
Only scalar data types are supported.
*Action: Use only scalar data types in polymorphic table function.
SQL>
SQL>
SQL> -- works after casting to date
SQL> with subq as (select empno, ename, hiredate
2 , cast(sysdate as date) as extraction_dt
3 from scott.emp
4 fetch first 5 rows only)
5 select * from noop(subq);
EMPNO ENAME HIREDATE EXTRACTION_DT
---------- ---------- ------------------- -------------------
7369 SMITH 17.12.1980 00:00:00 06.10.2020 21:17:00
7499 ALLEN 20.02.1981 00:00:00 06.10.2020 21:17:00
7521 WARD 22.02.1981 00:00:00 06.10.2020 21:17:00
7566 JONES 02.04.1981 00:00:00 06.10.2020 21:17:00
7654 MARTIN 28.09.1981 00:00:00 06.10.2020 21:17:00
There is the same problem with other data types like TIMESTAMP or INTERVAL too. So be careful when using subqueries and also regular views in this scenario. Now you know what the error messages really mean.
great catch. Super annoying problem though. Doesn’t help me when I do not control the views that are used and I can’t easily do the conversion for them. 🙁