如何在Oracle 11g中调试用户定义的聚合函数? [英] How to debug a user defined aggregate function in Oracle 11g?

问题描述

我正在尝试学习如何创建用户定义的聚合函数.到目前为止，我已经能够创建一个可以正常编译的程序，但是调用它会产生意想不到的结果.该函数是一个非常简单的测试函数，它遍历设置为"Y"或"N"的许多行，如果全部都设置为"Y"，则返回"Y"，否则返回"N".我在一行上运行它，然后取回一个空白的varchar 2.

I'm trying to learn how to create a user defined aggregate function. So far, I've been able to create one that compiles fine, but calling it gives an unexpected result. The function is a very simple test function that looks through a number of rows that are either set to 'Y' or 'N' and returns 'Y' if all are set to 'Y' and otherwise returns 'N'. I'm running it on a single row and getting back a blank varchar 2 instead.

我不确定调试该过程要执行什么步骤.我尝试使用DBMS_OUTPUT.PUT_LINE()，但在数据库输出上看不到任何内容.最大的问题是它很好地创建了函数，并且大多数代码都在对象类型中.因此，如果我要调试select语句，它就是在已经编译的数据库上调用代码.

I'm not sure what is the procedure to go through with debugging this. I've tried using DBMS_OUTPUT.PUT_LINE(), but I cannot see anything on the database output. The largest problem is that it is creating the function fine, and most of the code is in an object type. Thus, if I were to try to debug the select statement, it is calling code on the database that has already been compiled.

下面是该函数的代码，但是我不想知道为什么它不如我想知道的那样调试，所以我可以自己解决这些问题，尤其是当更复杂的聚合函数存在时参与其中.

Below is the code for the function, but I don't want to know why this isn't working as much as I want to know how to debug so I can solve these issues myself, especially when more complex aggregate functions are involved.

CREATE OR REPLACE TYPE MYSCHEMA.ALL_TRUE_T AS OBJECT
(
    TRUE_SO_FAR VARCHAR2(1),
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ALL_TRUE_T) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ALL_TRUE_T, value IN VARCHAR2) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateTerminate(self IN ALL_TRUE_T, returnValue OUT VARCHAR2, flags IN NUMBER) RETURN NUMBER,
    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT ALL_TRUE_T, ctx2 IN ALL_TRUE_T) RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY MYSCHEMA.ALL_TRUE_T IS
    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT ALL_TRUE_T)
    RETURN NUMBER IS
    BEGIN
        sctx := ALL_TRUE_T('Y');
        return ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ALL_TRUE_T, value IN VARCHAR2)
    RETURN NUMBER IS
    BEGIN
        IF value <> 'Y' OR self.TRUE_SO_FAR <> 'Y' THEN
            self.TRUE_SO_FAR := 'N';
        END IF;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateTerminate(self IN ALL_TRUE_T, returnValue OUT VARCHAR2, flags IN NUMBER)
    RETURN NUMBER IS
    BEGIN
        returnValue := self.TRUE_SO_FAR;
        RETURN ODCIConst.Success;
    END;

    MEMBER FUNCTION ODCIAggregateMerge(self IN OUT ALL_TRUE_T, ctx2 IN ALL_TRUE_T)
    RETURN NUMBER IS
    BEGIN
        IF ctx2.TRUE_SO_FAR = 'N' THEN
            self.TRUE_SO_FAR := 'N';
        END IF;
        RETURN ODCIConst.Success;
    END;
END;


CREATE OR REPLACE PACKAGE MYSCHEMA.ALL_TRUE_PKG IS
    FUNCTION ALL_TRUE (input VARCHAR2) RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY MYSCHEMA.ALL_TRUE_PKG IS
    FUNCTION ALL_TRUE (input VARCHAR2) RETURN VARCHAR2
        AGGREGATE USING ALL_TRUE_T;
END;

这就是我的称呼. YN_TEST_TABLE当前只有一行，其中带有"N".

And here is how I call it. YN_TEST_TABLE currently has a single row with an 'N' in it.

SELECT
    MYSCHEMA.ALL_TRUE_PKG.ALL_TRUE(YN)
FROM
    MYSCHEMA.YN_TEST_TABLE

最后，我不确定这是否有意义，但我使用的是Toad 11.6.

Finally, I'm not sure if this is relevant, but I'm using Toad 11.6.

所以我尝试将其插入到临时日志表中，但这也不起作用.

So I've tried inserting into a temp log table and that didn't work either.

我添加了以下内容

MEMBER FUNCTION ODCIAggregateIterate(self IN OUT ALL_TRUE_T, value IN VARCHAR2)
RETURN NUMBER IS
BEGIN
    BEGIN
        INSERT INTO MYSCHEMA.LAWTONFOGLES_TEMP_LOG
        (
            ID,
            Message,
            Time
        )
        VALUES
        (
            'all_true',
            'test1',
            systimestamp
        );
    END;
    IF value <> 'Y' OR self.TRUE_SO_FAR <> 'Y' THEN
        self.TRUE_SO_FAR := 'N';
    END IF;
    RETURN ODCIConst.Success;
END;

临时日志中没有任何内容，但也没有错误消息.好像四个聚合函数部件中的任何一个都没有被调用.

There was nothing in the temp log, but also no error message. It is as if none of the 4 aggregate function parts are even being called.

因此，为了使事情变得更有趣，当它不在包装中时可以使用.

So, to make things more interesting, this works when it is not in a package.

我做了以下

CREATE OR REPLACE FUNCTION MYSCHEMA.LAWTONFOGLES_ALL_TRUE (input VARCHAR2) RETURN VARCHAR2
AGGREGATE USING ALL_TRUE_T;

然后运行

SELECT
    MYSCHEMA.LAWTONFOGLES_ALL_TRUE(YN)
FROM
    MYSCHEMA.YN_TEST_TABLE

并获得了我预期的结果.看起来代码本身不是问题，但是将其放在程序包中会导致代码中断.星期四，我的Oracle DBA将与oracle展开交流，因此我将确保更新为什么将其放入软件包中会破坏它，但是当他们回到我们身边时，将其保留为一种功能就不会了.在那之前，我可能只需要将其保留在包装之外即可.

and got the results I expected. It seems that the code itself isn't a problem, but putting it in a package causes it to break. Thursday my Oracle DBA will be opening a ticket up with oracle, so I'll be sure to update with why does putting this in a package break it but leaving it as just a function doesn't when they get back with us. Until then I may just have to keep this outside of a package.

此外，我尝试在其工作时在其上添加put_line，但仍未获得输出.我认为用户定义的聚合函数的工作方式会阻止put_line起作用.

Also, I tried to add a put_line on it when it was working and still did not get an output. I think that the way user defined aggregate functions work prevent put_line from working.

推荐答案

如果您使用的是TOAD，请确保在运行proc之前打开DBMS_OUTPUT记录，以便可以看到输出.它应该在底部的DBMS选项卡上(如果已打开).通常，您会看到一个红色的圆圈，因为它默认为关闭状态.单击圆圈，使其变为绿色.

If you're using TOAD, be sure to turn on DBMS_OUTPUT recording before you run your proc so you can see your outputs. It should be on the bottom DBMS tab (if you have it open). Typically you'll see a red circle since it's defaulted as off. Click the circle so that it's green.

以该链接为例: 查看全文