I am trying to learn how to create a custom aggregate function. So far, I have been able to create one that compiles fine, but calling it gives an unexpected result. The function is a very simple test function that scans a series of lines that are either set to “Y” or “N” and return “Y” if all are set to “Y” and otherwise return “N”. I run it on one line and instead return empty varchar 2.
I'm not sure what the procedure is for debugging this. I tried using DBMS_OUTPUT.PUT_LINE (), but I can not see anything in the database output. The biggest problem is that it creates a penalty function, and most of the code refers to the type of object. Thus, if I tried to debug the select statement, it calls the code in an already compiled database.
The function code is below, but I don’t want to know why it doesn’t work as hard as I want to know how to debug, so I can solve these problems myself, especially when more complex aggregated 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;
And that's what I call it. YN_TEST_TABLE currently has one line with "N" in it.
SELECT
MYSCHEMA.ALL_TRUE_PKG.ALL_TRUE(YN)
FROM
MYSCHEMA.YN_TEST_TABLE
Finally, I'm not sure if this is relevant, but I am using Toad 11.6.
Edit:
, temp, .
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;
temp , . 4 .
EDIT2:
, , , .
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, , , , . .
, put_line , , . , , , put_line.