Forum Stats

  • 3,741,445 Users
  • 2,248,430 Discussions
  • 7,861,812 Comments

Discussions

"case when not m[1] is present then" outputs ORA-03113

Aketi Jyuuzou
Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
edited Jan 13, 2011 5:33AM in SQL & PL/SQL
I find that "case when not m[1] is present then" outputs ORA-03113:
Why?
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
select * from dual model
 dimension by(0 as d)
 measures(1 as m)
 rules(
 m[any] = case when not m[1] is present then 1 end);
ORA-03113:
Below one is alterNative solution.
select * from dual model
 dimension by(0 as d)
 measures(1 as m)
 rules(
 m[any] = case when presentv(m[1],1,0) = 0 then 3 end);

D  M
-  -
0  3
Tagged:

Best Answer

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    I don't have an answer, but this is confirmed on 10.2.0.4:
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options
    
    
    SQL> select * from dual model
      2   dimension by(0 as d)
      3   measures(1 as m)
      4   rules(
      5   m[any] = case when not m[1] is present then 1 end);
    select * from dual model
    *
    ERROR at line 1:
    ORA-03113: end-of-file on communication channel
    SomeoneElse
  • 666352
    666352 Member Posts: 1,442
    can you chech you alert trace , may be you can find more details.
    SQL> show parameter background_dump_dest;
    666352
  • Hoek
    Hoek Member Posts: 16,076 Gold Crown
    I get:
    ops$me%DB>  select * from dual model
      2    dimension by(0 as d)
      3    measures(1 as m)
      4    rules(
      5    m[any] = case when not m[1] is present then 1 end);
     select * from dual model
                   *
    ERROR at line 1:
    ORA-03002: operator not implemented
    
    
    Elapsed: 00:00:00.03
    ops$me%DB> select banner from v$version;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE    10.2.0.4.0      Production
    TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
    NLSRTL Version 10.2.0.4.0 - Production
    Hoek
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Thankys for replys.

    I checked traceFile which is below.
    Fri Mar 20 23:22:54 2009
    Errors in file d:\oracle\product\10.2.0\admin\orcl\udump\orcl_ora_948.trc:
    ORA-07445: 例外が検出されました: コア・ダンプ 
    [ACCESS_VIOLATION] [_qcss_process_expr+226] [PC:0x1E24A46] [ADDR:0x18] [UNABLE_TO_READ] []
    umm I think I will use presentv insted of is present
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,517 Black Diamond
    Accepted Answer
    [Bug 6530708|https://metalink2.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=6530708&p_database_id=BUG]

    SY.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Oh thank you,
    someday,I will get metaLink accout.
    I will see that.
  • 666352
    666352 Member Posts: 1,442
    But you can use this code .
     1  select * from dual model
     2   dimension by(0 as d)
     3   measures(1 as m)
     4   rules(
     5*  m[any] = case when  m[1] is present then null else 1 end)
     6  /
    
            D          M
    --------- ----------
            0          1
    666352
  • Laurent Schneider
    Laurent Schneider Member Posts: 5,219
    edited Jan 13, 2011 5:33AM
    I created that bug at the time I was writting my chapter on model...

    Strangely the error changed in 11.2.0.2
    select * from dual model
     dimension by(0 as d)
     measures(1 as m)
     rules(
     m[any] = case when not(m[1] is  present) then 1 end)
                  *
    Error at line 1
    ORA-03002: operator not implemented
    Edited by: Laurent Schneider on Jan 13, 2011 11:32 AM

    the ORA-3002 was already mentioned, sorry
This discussion has been closed.