包中的自定义聚合函数 [英] Custom aggregate function inside a package

问题描述

我正在尝试在Oracle中编写一个自定义聚合函数，并将该函数与其他一些函数一起打包在包中.作为一个示例(为了模拟我遇到的问题)，假设我的自定义聚合对数字进行求和看起来像这样:

I'm trying to write a custom aggregate function in Oracle and group that function inside a package together with some other functions that I have. As an example (to simulate the problem I have) suppose my custom aggregation to do a summation of numbers looks like:

CREATE OR REPLACE TYPE SUM_AGGREGATOR_TYPE AS OBJECT (
    summation NUMBER,

    STATIC FUNCTION ODCIAggregateInitialize(agg_context IN OUT
       SUM_AGGREGATOR_TYPE) RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT SUM_AGGREGATOR_TYPE,
        next_number IN NUMBER) RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT SUM_AGGREGATOR_TYPE,
        para_context IN SUM_AGGREGATOR_TYPE) RETURN NUMBER,

    MEMBER FUNCTION ODCIAggregateTerminate(self IN SUM_AGGREGATOR_TYPE,
        return_value OUT NUMBER, flags IN NUMBER) RETURN NUMBER
);

---

CREATE OR REPLACE TYPE BODY SUM_AGGREGATOR_TYPE IS

  STATIC FUNCTION ODCIAggregateInitialize(agg_context IN OUT
    SUM_AGGREGATOR_TYPE)
      RETURN NUMBER IS
  BEGIN
    agg_context := SUM_AGGREGATOR_TYPE(NULL);
    RETURN ODCIConst.Success;
  END;


  MEMBER FUNCTION ODCIAggregateIterate(self IN OUT SUM_AGGREGATOR_TYPE,
    next_number IN NUMBER)
      RETURN NUMBER IS
  BEGIN
    IF self.summation IS NULL THEN
        self.summation := next_number;
    ELSIF summation IS NOT NULL THEN
        self.summation := self.summation + next_number;
    END IF;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self IN OUT SUM_AGGREGATOR_TYPE,
    para_context IN SUM_AGGREGATOR_TYPE)
      RETURN NUMBER IS
  BEGIN
    self.summation := self.summation + para_context.summation;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self IN SUM_AGGREGATOR_TYPE,
    return_value OUT NUMBER, flags IN NUMBER)
      RETURN NUMBER IS
  BEGIN
    return_value := self.summation;
    return ODCIConst.Success;
  END;

END;

如果我编写以下函数定义:

If I write the following function definition:

CREATE OR REPLACE FUNCTION MY_SUM(input NUMBER)
  RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SUM_AGGREGATOR_TYPE;

以及要测试的相应类型声明:

and corresponding type declaration to test:

CREATE OR REPLACE TYPE VECTOR
IS
  TABLE OF NUMBER;

此声明:

select my_sum(column_value) from table(vector(1, 2, 1, 45, 22, -1));

给出70的正确结果.但是，使用函数定义创建一个包:

gives the correct result of 70. However, creating a package with the function definition:

CREATE OR REPLACE PACKAGE MY_FUNCTIONS AS
  FUNCTION MY_SUM(input NUMBER)
    RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SUM_AGGREGATOR_TYPE;
END;

并通过以下方式调用它:

and calling it via:

select MY_FUNCTIONS.my_sum(column_value) from table(vector(1, 2, 1, 45, 22, -1));

爆炸

ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], [] 

是否可以在包声明中嵌套自定义聚合函数?

Is it possible to have custom aggregate functions nested inside package declarations?

推荐答案

Oracle使用ORA-00600发出未处理的异常(即bug)的信号.第一个参数表示异常；第二个参数表示异常. ORA-17090是通用的不允许操作".通常，它们仅限于数据库版本和OS平台的特定排列.在其他时候，这只是意味着我们正在做一些非常不寻常的事情.

Oracle uses ORA-00600 to signal unhandled exceptions i.e. bugs. The first argument indicates the exception; ORA-17090 is a generic "operation not allowed". Frequently they are restricted to specific permutations of database version and OS platform. Other times it just means we're doing something really unusual.

在包中包含自定义聚合函数是否算作真的很不正常"?没有把握.当然，我们被允许在PL/SQL函数中包括数据盒带功能.但是用户定义的聚合是ODCI的特例.尽管文档没有针对软件包的明确规则，但所有示例都使用CREATE FUNCTION来实现汇总.

Does including a custom aggregate function inside a package count as "really unusual"? Not sure. Certainly we are permitted to include data cartridge functions in PL/SQL functions. But user-defined aggregates are a special case of ODCI. While the documentation has no explicit rule against packages all the examples implement the aggregate using CREATE FUNCTION.

那该怎么办?好吧，ORA-00600消息需要Oracle支持，因为它需要一个补丁.如果您有支持帐户，则可以

So, what to do? Well, ORA-00600 messages require the intervention of Oracle Support, as it needs a patch. If you have a Support account you can find out more about this particular issue here. You will need to raise an iTAR to get further resolution. Otherwise I'm afraid you're probably out of luck.

这篇关于包中的自定义聚合函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！