问题描述
当玩众所周知的PivotImpl和我自己的衍生物时，我发现一个奇怪的行为OCDITableDescribe和OCDITableStart当查询包含FLOAT列时:
而FLOAT(正确) 检测为NUMBER在ODCITabeDecribePivotImpl的实现:CASE desc_tab(i).col_type返回2...
的CASE type_code在ODCITableStart返回4!
(and 4 is not documented anywhere as far as I was able to search在的doc...).
And of course 的outside call fails with a ORA-06595: CASE not found while executing CASE statement.
When looking a 的generated TYPE ”ST000...” for 的OBJECT, it contains a FLOAT(126):
how possible if 的CASE在ODCITableDescribe将其定义为DBMS_TYPE.TYPECODE_NUMBER... ？？？？
人们可以认为只需添加一个“WHEN 4”在的various CASE在Describe, Prepare, Fetch...
Well first在Describe it never returns 4 but 2 for NUMBER of which FLOAT is a subtype,
then在Prepare if you add a WHEN 4和call dbms_sql.define_column用一个“CAST(NULL as NUMBER)”
it pass this step but you still get a problem在Fetch:
in 的”WHEN 4” you may add try to do a outset.setnumber,又提出了一个ERR:
ORA-22626, ORA-06512 ”Type Mismatch while constructing or accessing OCIAnyData
Cause: Type supplied is not matching 的type of 的AnyData”.
So except rewriting 的queries (views, ...) to CAST AS NUMBER all 的met FLOAT...
(I don't control 的data model generated by a 4G...)
我还没有找到任何解决办法。
有什么想法吗？
而FLOAT(正确) 检测为NUMBER在ODCITabeDecribePivotImpl的实现:CASE desc_tab(i).col_type返回2...
的CASE type_code在ODCITableStart返回4!
(and 4 is not documented anywhere as far as I was able to search在的doc...).
And of course 的outside call fails with a ORA-06595: CASE not found while executing CASE statement.
When looking a 的generated TYPE ”ST000...” for 的OBJECT, it contains a FLOAT(126):
how possible if 的CASE在ODCITableDescribe将其定义为DBMS_TYPE.TYPECODE_NUMBER... ？？？？
人们可以认为只需添加一个“WHEN 4”在的various CASE在Describe, Prepare, Fetch...
Well first在Describe it never returns 4 but 2 for NUMBER of which FLOAT is a subtype,
then在Prepare if you add a WHEN 4和call dbms_sql.define_column用一个“CAST(NULL as NUMBER)”
it pass this step but you still get a problem在Fetch:
in 的”WHEN 4” you may add try to do a outset.setnumber,又提出了一个ERR:
ORA-22626, ORA-06512 ”Type Mismatch while constructing or accessing OCIAnyData
Cause: Type supplied is not matching 的type of 的AnyData”.
So except rewriting 的queries (views, ...) to CAST AS NUMBER all 的met FLOAT...
(I don't control 的data model generated by a 4G...)
我还没有找到任何解决办法。
有什么想法吗？
专家解答
我已经复制了您的问题，但是我只在使用查询时才看到它，无论如何它都没有意义？
例如
So only in the case where I did not have any kind of aggr例如ation did I see it.
你在其他情况下看到了吗？
例如
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'));
ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ----------
3000 1300 2975 5000 1600
SQL> select * from table( pivot('select job,max(f1) sal_avg from t group by job'));
ANALYST CLERK MANAGER PRESIDENT SALESMAN
---------- ---------- ---------- ---------- ----------
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
SQL>
So only in the case where I did not have any kind of aggr例如ation did I see it.
你在其他情况下看到了吗？
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
