Skip to Main Content


Question and Answer

Connor McDonald

Thanks for the question, Pascal.

Asked: March 10, 2021 - 8:37 am UTC

Last updated: March 31, 2021 - 4:36 am UTC

Version: 12.2 and above

Viewed 100+ times

You Asked

When playing with the well known PivotImpl and my own derivate of it, I discover a strange behavior with OCDITableDescribe and OCDITableStart when the query contains a FLOAT column:

while the FLOAT is (correctly) detected as NUMBER in ODCITabeDecribe implementation of PivotImpl: the CASE desc_tab(i).col_type returns 2...
the CASE type_code in ODCITableStart returns 4 !
(and 4 is not documented anywhere as far as I was able to search in the doc...).
And of course the outside call fails with a ORA-06595: CASE not found while executing CASE statement.

When looking a the generated TYPE "ST000..." for the OBJECT, it contains a FLOAT(126):
how possible if the CASE in ODCITableDescribe defines it as DBMS_TYPE.TYPECODE_NUMBER... ????

One could think just add a "WHEN 4" in the various CASE in Describe, Prepare, Fetch...
Well first in Describe it never returns 4 but 2 for NUMBER of which FLOAT is a subtype,
then in Prepare if you add a WHEN 4 and call dbms_sql.define_column with a "CAST(NULL as NUMBER)"
it pass this step but you still get a problem in Fetch:
in the "WHEN 4" you may add try to do a outset.setnumber, another ERR is raised:
ORA-22626, ORA-06512 "Type Mismatch while constructing or accessing OCIAnyData
Cause: Type supplied is not matching the type of the AnyData".

So except rewriting the queries (views, ...) to CAST AS NUMBER all the met FLOAT...
(I don't control the data model generated by a 4G...)
I don't found any solution yet.

Any idea?

and we said...

I've replicated your issue, but I only see it when using a query that it does not make sense to pivot anyway?


SQL> create table t as select * from scott.emp;

Table created.

SQL> alter table t add F1 float(10);

Table altered.

SQL> update t set f1 = sal;

13 rows updated.

SQL> commit;

Commit complete.

SQL> select * from table( pivot('select job,max(sal) sal_avg from t group by job'));

---------- ---------- ---------- ---------- ----------
      3000       1300       2975       5000       1600

SQL> select * from table( pivot('select job,max(f1) sal_avg from t group by job'));

---------- ---------- ---------- ---------- ----------
      3000       1300       2975       5000       1600

SQL> select * from table( pivot('select f1,max(sal) sal_avg from t group by f1'));

        10         20         30
---------- ---------- ----------

SQL> select * from table( pivot('select deptno,max(f1) sal_avg from t group by deptno'));

        10         20         30
---------- ---------- ----------
      5000       3000       2850

SQL> select * from table( pivot('select deptno,f1 from t'));
select * from table( pivot('select deptno,f1 from t'))
ERROR at line 1:
ORA-22626: Type Mismatch while constructing or accessing OCIAnyData
ORA-06512: at "SYS.ANYDATASET", line 18
ORA-06512: at "MCDONAC.PIVOTIMPL", line 322


So only in the case where I did not have any kind of aggregation did I see it.

Are you seeing it in other cases ?


  (3 ratings)


Pascal Pochet, March 24, 2021 - 8:28 am UTC

As I told in my original question, the dynamic pivot is just an example (you could have a noop functionality, the problem will still be there), I am actually busy with another function of my own that does not do a PIVOT at all but uses the basic mechanism found in the dynamic pivot example,
and the problem is related to the presence of a FLOAT in the SELECT parsed by the ODCI engine: the fact that you got an undocumented type (4) in the ODCITableStart when calling sctx.ret_type.getattreleminfo while it was reported correctly as a NUMBER in ODCITableDescribe when calling DBMS_SQL.DESCRIBE_COLUMNS2, is the issue, and actually looks like a bug.
(of course if a cast as number in the original query, the problem is solved, but not possible in all cases where I don't control the data model)

Connor McDonald
March 26, 2021 - 6:57 am UTC

Could you give us *your* situation then, so we can explore that further

shared link

A reader, March 26, 2021 - 2:02 pm UTC

Connor McDonald
March 31, 2021 - 4:36 am UTC

I already said I replicated your issue.

My question is - I'd like to see a *context* around why you want to use this technique without any aggregation, ie, the business case.

A reader, April 07, 2021 - 12:32 pm UTC

As I told you the PivotImpl was the starting point of the development I used to do something else. The actual need was dynamic query as shown in the Live SQL example and trying to avoid to create TYPE AS OBJECT and TABLE OF for each possible result set we may encounter in a context of "dynamic views" (pipelined functions)...
(these functions are used to check the results of reports created by TABLEAU on a Document database before they go lifeā€¦)

More to Explore


Check out more PL/SQL tutorials on our LiveSQL tool.