9 Replies Latest reply: May 1, 2013 11:23 PM by 1005865 RSS

    ORA-00932: inconsistent datatypes: expected NUMBER got AAA.TYPESTT

    1005865
      Schema is 'AAA'

      SQL is

      SELECT ID,STT,grouping(ID),grouping(STT)
      FROM TABLE (CORP('sys1','code1'))
      group by grouping sets((ID),(STT));

      Error msg is

      ORA-00932: inconsistent datatypes: expected NUMBER got AAA.TYPESTT

      TYPESTT and CORPSTT type is defined as below

      CREATE OR REPLACE TYPE "AAA"."TYPESTT"
      AS OBJECT( ID VARCHAR2 (30),
      STT VARCHAR (10))

      CREATE OR REPLACE TYPE "AAA"."CORPSTT" AS TABLE OF TYPESTT

      CORP function is defined as below

      CREATE
      OR
      REPLACE
      FUNCTION "AAA"."CORP"(SYS1 VARCHAR ,
      CODE1 VARCHAR)
      RETURN CORPSTT PIPELINED AS V1 TYPESTT ;
      VCODE VARCHAR(30);
      VSYS VARCHAR(10);
      V_DATE VARCHAR(10);
      BEGIN
      VCODE := CODE1 ;
      VSYS := SYS1 ;
      V_DATE := TO_CHAR(SYSDATE,'YYYY-MM-DD') ;
      FOR MYROW IN
      (
      SELECT
      ID ,
      STT
      FROM
      RLCP
      UNION ALL
      SELECT
      CORPID ,
      CORPROLE
      FROM
      RRCP )
      LOOP
      V1 := TYPESTT(MYROW.ID, MYROW.STT);
      PIPE ROW (V1);
      END LOOP;
      RETURN ;
      END ;

      Thanks very much for replies

      Edited by: 1002862 on 2013-4-28 上午1:14
        • 1. Re: ORA-00932: inconsistent datatypes: expected NUMBER got AAA.TYPESTT
          Hoek
          Welcome to the forum.
          What database version are you using?

          On 11.2.0.1 I got the following, made use of dual, not having your tables, but thanks for the rest of the testcase, and I changed the brackets:
          SQL> drop type corpstt;
          
          Type dropped.
          
          SQL> drop type typestt;
          
          Type dropped.
          
          SQL> create or replace type typestt
            2  as object( id varchar2 (30),
            3  stt varchar (10));
            4  /
          
          Type created.
          
          SQL> create or replace type corpstt as table of typestt;
            2  /
          
          Type created.
          
          SQL> create or replace function CORP(sys1 varchar, code1 varchar)
            2    return corpstt
            3    pipelined as
            4    v1     typestt;
            5    vcode  varchar(30);
            6    vsys   varchar(10);
            7    v_date varchar(10);
            8  begin
            9    vcode  := code1;
           10    vsys   := sys1;
           11    v_date := to_char(sysdate
           12                     ,'YYYY-MM-DD');
           13    for myrow in (select 1 id, 'A' stt
           14                    from dual
           15                  union all
           16                  select 2, 'B'
           17                    from dual)
           18    loop
           19      v1 := typestt(myrow.id
           20                   ,myrow.stt);
           21      pipe row(v1);
           22    end loop;
           23    return;
           24  end;
           25  /
          
          Function created.
          
          SQL> select id
            2  ,      stt
            3  ,      grouping(id)
            4  ,      grouping(stt)
            5    from table(corp('sys1'
            6                   ,'code1'))
            7   group by grouping sets((id, stt));
          
          ID                             STT        GROUPING(ID) GROUPING(STT)
          ------------------------------ ---------- ------------ -------------
          2                              B                     0             0
          1                              A                     0             0
          
          2 rows selected.
          • 2. Re: ORA-00932: inconsistent datatypes: expected NUMBER got AAA.TYPESTT
            Solomon Yakobson
            Hoek wrote:
            On 11.2.0.1 I got the following
            And on:
            SQL> select  *
              2    from  v$version
              3  /
            
            BANNER
            --------------------------------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
            PL/SQL Release 11.2.0.3.0 - Production
            CORE    11.2.0.3.0      Production
            TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
            NLSRTL Version 11.2.0.3.0 - Production
            
            SQL> create or replace function CORP(sys1 varchar, code1 varchar)
              2      return corpstt
              3      pipelined as
              4      v1     typestt;
              5      vcode  varchar(30);
              6      vsys   varchar(10);
              7      v_date varchar(10);
              8    begin
              9      vcode  := code1;
             10      vsys   := sys1;
             11      v_date := to_char(sysdate
             12                       ,'YYYY-MM-DD');
             13      for myrow in (select 1 id, 'A' stt
             14                      from dual
             15                    union all
             16                    select 2, 'B'
             17                      from dual)
             18      loop
             19        v1 := typestt(myrow.id
             20                     ,myrow.stt);
             21        pipe row(v1);
             22      end loop;
             23      return;
             24    end;
             25  /
            
            Function created.
            
            SQL> SELECT  ID,
              2          STT,
              3          grouping(ID),
              4          grouping(STT)
              5    FROM  TABLE(CORP('sys1','code1')) t
              6    group by grouping sets((ID),(STT))
              7  /
                    STT,
               *
            ERROR at line 2:
            ORA-00932: inconsistent datatypes: expected NUMBER got SCOTT.TYPESTT
            
            
            SQL>
            Must be a bug. The only solution I found is using undocumented hint MATERIALIZE:
            SQL> SELECT  ID,
              2          STT,
              3          grouping(ID),
              4          grouping(STT)
              5    FROM  TABLE(CORP('sys1','code1')) t
              6    group by grouping sets((ID),(STT))
              7  /
                    STT,
               *
            ERROR at line 2:
            ORA-00932: inconsistent datatypes: expected NUMBER got SCOTT.TYPESTT
            
            
            SQL> WITH t AS (
              2             SELECT  /*+ MATERIALIZE */
              3                     ID,
              4                     STT
              5               FROM  TABLE(CORP('sys1','code1'))
              6            )
              7  SELECT  ID,
              8          STT,
              9          grouping(ID),
             10          grouping(STT)
             11    FROM  t
             12    group by grouping sets((ID),(STT))
             13  /
            
            ID                             STT        GROUPING(ID) GROUPING(STT)
            ------------------------------ ---------- ------------ -------------
            1                                                    0             1
            2                                                    0             1
                                           A                     1             0
                                           B                     1             0
            
            SQL>
            SY.
            • 3. Re: ORA-00932: inconsistent datatypes: expected NUMBER got AAA.TYPESTT
              Hoek
              There's a difference, I used:
              group by grouping sets((id, stt));
              and not:
              group by grouping sets((ID),(STT))
              But a(nother) nice catch, SY!
              • 4. Re: ORA-00932: inconsistent datatypes: expected NUMBER got AAA.TYPESTT
                Solomon Yakobson
                Hoek wrote:
                There's a difference, I used:
                Well, that difference produces quite different grouping sets and therefore works on my 11.2.0.3.0:
                SQL> WITH t AS (
                  2             SELECT  /*+ MATERIALIZE */
                  3                     ID,
                  4                     STT
                  5               FROM  TABLE(CORP('sys1','code1'))
                  6            )
                  7  SELECT  ID,
                  8          STT,
                  9          grouping(ID),
                 10          grouping(STT)
                 11    FROM  t
                 12    group by grouping sets((ID),(STT))
                 13  /
                
                ID                             STT        GROUPING(ID) GROUPING(STT)
                ------------------------------ ---------- ------------ -------------
                1                                                    0             1
                2                                                    0             1
                                               A                     1             0
                                               B                     1             0
                
                SQL> select id
                  2    ,      stt
                  3    ,      grouping(id)
                  4    ,      grouping(stt)
                  5      from table(corp('sys1'
                  6                     ,'code1'))
                  7     group by grouping sets((id, stt));
                
                ID                             STT        GROUPING(ID) GROUPING(STT)
                ------------------------------ ---------- ------------ -------------
                2                              B                     0             0
                1                              A                     0             0
                
                SQL>
                SY.
                • 5. Re: ORA-00932: inconsistent datatypes: expected NUMBER got AAA.TYPESTT
                  Hoek
                  You're correct.
                  Not sure what results OP is really after, though, I just used dual to reproduce, don't know the real tables/data...
                  • 6. Re: ORA-00932: inconsistent datatypes: expected NUMBER got AAA.TYPESTT
                    Solomon Yakobson
                    Actually, it is very good finding. It narrows it down to grouping sets.

                    SY.
                    • 7. Re: ORA-00932: inconsistent datatypes: expected NUMBER got AAA.TYPESTT
                      Hoek
                      True, therefore I hope OP will update this thread with his/her findings.