{"id":1556,"date":"2020-10-06T23:32:36","date_gmt":"2020-10-06T21:32:36","guid":{"rendered":"https:\/\/blog.sqlora.com\/en\/?p=1556"},"modified":"2020-10-07T08:52:52","modified_gmt":"2020-10-07T06:52:52","slug":"using-subqueries-with-ptf-or-sql-macros","status":"publish","type":"post","link":"https:\/\/blog.sqlora.com\/en\/using-subqueries-with-ptf-or-sql-macros\/","title":{"rendered":"Using Subqueries and Views with PTF or SQL macros"},"content":{"rendered":"\n

In the last post about SQL macros in 19c<\/a> 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)<\/code> 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.<\/p>\n\n\n\n\n\n\n\n

Let’s see again what happened. Just selecting a date field HIREDATE<\/strong><\/code> is fine. But also using SYSDATE<\/strong><\/code> as a value for new (generated) column EXTRACTION_DT<\/strong><\/code> is not working.<\/p>\n\n\n

\nSQL> -- this works\nSQL> with subq as (select empno, ename, hiredate \n  2                from empv \n  3                fetch first 5 rows only)\n  4  select * from get_csv(subq);\n \nCSV_ROW                                           \n--------------------------------------------------\n"EMPNO","ENAME","HIREDATE"\n7654,"MARTIN",1981-09-28\n7521,"WARD",1981-02-22\n7566,"JONES",1981-04-02\n7369,"SMITH",1980-12-17\n7499,"ALLEN",1981-02-20\n \n6 rows selected. \n \nSQL> \nSQL> -- doesn't work\nSQL> with subq as (select empno, ename, hiredate, sysdate as extraction_dt \n  2                from empv\n  3                fetch first 5 rows only)\n  4  select * from get_csv(subq);\n \nError at Command Line : 235 Column : 15\nError report -\nSQL Error: ORA-62558: Unsupported data types (DATE) for polymorphic table function\n62558. 00000 -  "Unsupported data types (%s) for polymorphic table function"\n*Cause:    Non-scalar data types were used in polymorphic table function.\n           Only scalar data types are supported.\n*Action:   Use only scalar data types in polymorphic table function.\nSQL> \n<\/pre><\/div>\n\n\n
Please note that you will also have the same error using regular views which define some date calculations:<\/p>\n\n\n
\nSQL> create or replace view test_view as\n  2  select empno, ename, hiredate\n  3       , sysdate as extraction_dt \n  4  from   empv\n  5  fetch first 5 rows only;\n\nView TEST_VIEW created.\n\nSQL> \nSQL> select * from get_csv(test_view);\n\nError at Command Line : 450 Column : 15\nError report -\nSQL Error: ORA-62558: Unsupported data types (DATE) for polymorphic table function\n...\n<\/pre><\/div>\n\n\n
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<\/strong><\/code>, etc. See How does Oracle store the DATE datatype internally? (Doc ID 69028.1)<\/a> for details. Consider the following listing. Selecting a date from EMP shows Typ=12 (7 bit), but using SYSDATE<\/strong><\/code>, we got Typ=13 (8 bit)<\/p>\n\n\n
\nSQL> select dump(hiredate) dh, dump(sysdate) ds\n  2  from  scott.emp\n  3  fetch first 1 row only;\n\nDH                                  DS                                 \n----------------------------------- -----------------------------------\nTyp=12 Len=7: 119,180,12,17,1,1,1   Typ=13 Len=8: 228,7,10,6,20,29,48,0\n<\/pre><\/div>\n\n\n
Now, what is supported with PTF or SQL macros? Since we use DBMS_TF.TABLE_T<\/strong><\/code> as parameter type, we should look inside DBMS_TF<\/strong><\/code> package, simply because the documentation only mentions the data type DATE, without further explanation. Here, we only see the type 12<\/p>\n\n\n
\ncreate or replace NONEDITIONABLE PACKAGE DBMS_TF AS\n\n   \/*\n    Package: DBMS_TF\n\n     DBMS_TF is the PL\/SQL package that provides various utility API's to\n     support POLYMORPHIC TABLE functions(ptf).\n\n   *\/\n\n  \/* Type Codes for supported types *\/\n  TYPE_VARCHAR2            CONSTANT PLS_INTEGER :=   1;\n  TYPE_NUMBER              CONSTANT PLS_INTEGER :=   2;\n  TYPE_DATE                CONSTANT PLS_INTEGER :=  12;\n  TYPE_RAW                 CONSTANT PLS_INTEGER :=  23;\n  TYPE_ROWID               CONSTANT PLS_INTEGER :=  69;\n  TYPE_CHAR                CONSTANT PLS_INTEGER :=  96;\n  TYPE_BINARY_FLOAT        CONSTANT PLS_INTEGER := 100;\n  TYPE_BINARY_DOUBLE       CONSTANT PLS_INTEGER := 101;\n  TYPE_CLOB                CONSTANT PLS_INTEGER := 112;\n  TYPE_BLOB                CONSTANT PLS_INTEGER := 113;\n  TYPE_TIMESTAMP           CONSTANT PLS_INTEGER := 180;\n  TYPE_TIMESTAMP_TZ        CONSTANT PLS_INTEGER := 181;\n  TYPE_INTERVAL_YM         CONSTANT PLS_INTEGER := 182;\n  TYPE_INTERVAL_DS         CONSTANT PLS_INTEGER := 183;\n\n<\/pre><\/div>\n\n\n
Workaround<\/h2>\n\n\n\n
Is there a simple workaround? Just CAST SYSDATE<\/strong><\/code> explicitly as DATE<\/strong><\/code> and the output will be internal DATE (Typ=12)! <\/p>\n\n\n
\nSQL> select dump(sysdate) ds, dump(cast (sysdate as date)) dcs\n  2  from  scott.emp\n  3  fetch first 1 row only;\n\nDS                                  DCS                                \n----------------------------------- -----------------------------------\nTyp=13 Len=8: 228,7,10,6,20,33,29,0 Typ=12 Len=7: 120,120,10,6,21,34,30\n<\/pre><\/div>\n\n\n
Now we can test our subquery again and it is just working fine:<\/p>\n\n\n
\nSQL> -- works after casting to date\nSQL> with subq as (select empno, ename, hiredate\n  2                     , cast(sysdate as date) as extraction_dt \n  3                from empv\n  4                fetch first 5 rows only)\n  5  select * from get_csv(subq);\n\nCSV_ROW                                                                                      \n----------------------------------------------------------\n"EMPNO","ENAME","HIREDATE","EXTRACTION_DT"\n7654,"MARTIN",1981-09-28,2020-10-06\n7521,"WARD",1981-02-22,2020-10-06\n7566,"JONES",1981-04-02,2020-10-06\n7369,"SMITH",1980-12-17,2020-10-06\n7499,"ALLEN",1981-02-20,2020-10-06\n\n6 rows selected. \n<\/pre><\/div>\n\n\n
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<\/strong><\/code> and thus, we can expect the same behavior. Just a quick test with a PTF NOOP from Oracle documentation<\/a>, which actually does nothing:<\/p>\n\n\n
\nSQL> with subq as (select empno, ename, hiredate\n  2                     , sysdate as extraction_dt \n  3                from scott.emp\n  4                fetch first 5 rows only)\n  5  select * from noop(subq);\n\nError at Command Line : 433 Column : 15\nError report -\nSQL Error: ORA-62558: Unsupported data types (DATE) for polymorphic table function\n62558. 00000 -  "Unsupported data types (%s) for polymorphic table function"\n*Cause:    Non-scalar data types were used in polymorphic table function.\n           Only scalar data types are supported.\n*Action:   Use only scalar data types in polymorphic table function.\nSQL> \nSQL> \nSQL> -- works after casting to date\nSQL> with subq as (select empno, ename, hiredate\n  2                     , cast(sysdate as date) as extraction_dt \n  3                from scott.emp\n  4                fetch first 5 rows only)\n  5  select * from noop(subq);\n\n     EMPNO ENAME      HIREDATE            EXTRACTION_DT      \n---------- ---------- ------------------- -------------------\n      7369 SMITH      17.12.1980 00:00:00 06.10.2020 21:17:00\n      7499 ALLEN      20.02.1981 00:00:00 06.10.2020 21:17:00\n      7521 WARD       22.02.1981 00:00:00 06.10.2020 21:17:00\n      7566 JONES      02.04.1981 00:00:00 06.10.2020 21:17:00\n      7654 MARTIN     28.09.1981 00:00:00 06.10.2020 21:17:00\n<\/pre><\/div>\n\n\n
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.<\/p>\n\n\n