0 Replies Latest reply: Apr 28, 2013 3:06 AM by 1005865 RSS

    ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 AAA.TYPESTT

    1005865
      Schema为AAA

      执行的SQL语句为

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

      错误信息为

      ORA-00932: 数据类型不一致: 应为 NUMBER, 但却获得 AAA.TYPESTT

      自定义类型为

      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函数的定义如下

      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 ;

      请教

      帖子经 1002862编辑过