This discussion is archived
9 Replies Latest reply: May 1, 2013 9:23 PM by 1005865 RSS

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

1005865 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    True, therefore I hope OP will update this thread with his/her findings.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points