出现这个问题的原因主要是,创建函数方法时,在头部缺少声明BODY里的方法,然后调用时会使用到BODY里的方法则导致报错,解决方法是:检查调用对象的DDL,确保报错的方法,在其头部有声明。
以下一个案例分享:
【1】创建一个自定义类型
CREATE OR REPLACE TYPE "TYPESUMVARCHAR2" as object (
sum VARCHAR2(4000),
static function ODCIAggregateInitialize(sctx IN OUT TYPESUMVARCHAR2) return number,
member function ODCIAggregateIterate(self IN OUT TYPESUMVARCHAR2,value IN varchar2) return number,
member function ODCIAggregateMerge(self IN OUT TYPESUMVARCHAR2,ctx2 IN TYPESUMVARCHAR2) return number
);
--BODY
CREATE OR REPLACE TYPE BODY "TYPESUMVARCHAR2" IS
STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT TYPESUMVARCHAR2)
RETURN NUMBER IS
BEGIN
SCTX := TYPESUMVARCHAR2('');
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT TYPESUMVARCHAR2,
VALUE IN VARCHAR2) RETURN NUMBER IS
BEGIN
--SELF.SUM := substr(SELF.SUM || ',' || VALUE,0,2000);
if instr(SELF.SUM, value ) = 0 or SELF.SUM is null then
SELF.SUM := substr(SELF.SUM || ',' || VALUE,0,2000) ;
else
SELF.SUM := substr(SELF.SUM ||'',0,2000) ;
end if ;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN TYPESUMVARCHAR2,
RETURNVALUE OUT VARCHAR2,
FLAGS IN NUMBER) RETURN NUMBER IS
BEGIN
RETURNVALUE := SUBSTR(SELF.SUM, 2);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT TYPESUMVARCHAR2,
CTX2 IN TYPESUMVARCHAR2) RETURN NUMBER IS
BEGIN
RETURN ODCICONST.SUCCESS;
END;
END;
【2】创建一个函数,该函数调用上方的自定义类型,同时新建一张表进行测试
CREATE OR REPLACE FUNCTION SUMC2(input varchar2) RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING TYPESUMVARCHAR2;
--新建一张表用于测试
create table test (ID varchar);
执行成功后,调用该函数
select SUMC2(T.ID) from test T group by T.ID;
数据库报错“试图调用对象的内部方法[ODCIAGGREGATETERMINATE]”
经排查是SUMC2函数使用了自定义类型TYPESUMVARCHAR2,而该类型在头部缺少声明BODY里的ODCIAggregateTerminate,导致数据库报错,补全声明即可解决:
CREATE OR REPLACE TYPE "TYPESUMVARCHAR2" as object (
sum VARCHAR2(4000),
static function ODCIAggregateInitialize(sctx IN OUT TYPESUMVARCHAR2) return number,
member function ODCIAggregateIterate(self IN OUT TYPESUMVARCHAR2,value IN varchar2) return number,
member function ODCIAggregateTerminate(self IN TYPESUMVARCHAR2,returnValue OUT VARCHAR2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT TYPESUMVARCHAR2,ctx2 IN TYPESUMVARCHAR2) return number
);
--执行报错则需要删掉整个自定义类型再重建
再次测试
select SUMC2(T.ID) from test T group by T.ID;
文章
阅读量
获赞