Databases
StackOverflow2013
Postsdbo
POHow to debug a user defined aggregate function in Oracle 11g?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to debug a user defined aggregate function in Oracle 11g?
    primarykey
    data
    text
    <p>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.</p> <p>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.</p> <p>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.</p> <pre><code>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 &lt;&gt; 'Y' OR self.TRUE_SO_FAR &lt;&gt; '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; </code></pre> <p>And here is how I call it. YN_TEST_TABLE currently has a single row with an 'N' in it.</p> <pre><code>SELECT MYSCHEMA.ALL_TRUE_PKG.ALL_TRUE(YN) FROM MYSCHEMA.YN_TEST_TABLE </code></pre> <p>Finally, I'm not sure if this is relevant, but I'm using Toad 11.6.</p> <p>Edit:</p> <p>So I've tried inserting into a temp log table and that didn't work either.</p> <p>I added the following</p> <pre><code>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 &lt;&gt; 'Y' OR self.TRUE_SO_FAR &lt;&gt; 'Y' THEN self.TRUE_SO_FAR := 'N'; END IF; RETURN ODCIConst.Success; END; </code></pre> <p>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.</p> <p>EDIT2:</p> <p>So, to make things more interesting, this works when it is not in a package.</p> <p>I did the following</p> <pre><code>CREATE OR REPLACE FUNCTION MYSCHEMA.LAWTONFOGLES_ALL_TRUE (input VARCHAR2) RETURN VARCHAR2 AGGREGATE USING ALL_TRUE_T; </code></pre> <p>and then ran this</p> <pre><code>SELECT MYSCHEMA.LAWTONFOGLES_ALL_TRUE(YN) FROM MYSCHEMA.YN_TEST_TABLE </code></pre> <p>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.</p> <p>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.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLawtonfogle
    1. USLawtonfogle
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to debug a user defined aggregate function in Oracle 11g?
      1. USJon Heller
      1. VTFavorite
    2. VO
      singulars
      1. POHow to debug a user defined aggregate function in Oracle 11g?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to debug a user defined aggregate function in Oracle 11g?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODBMS_OUTPUT.PUT_LINE should work, you may need to enable your client to view it. On SQL*Plus, this would be "set serveroutput on", on Toad there is probably some button to click to enable DBMS Output. But you're probably better off using `CAST` `COLLECT` instead of Oracle Data Cartridge, as I explain [here](http://stackoverflow.com/a/13346078/409172).
      singulars
      1. POHow to debug a user defined aggregate function in Oracle 11g?
      1. USJon Heller
    2. COSee this [SO Question](http://stackoverflow.com/questions/4310232/custom-aggregate-function-inside-a-package) for a similar discussion, and the followup [Oracle forum post](https://forums.oracle.com/thread/2139501?tstart=0). This looks like an unsupported feature which will hopefully get resolved in a future release.
      singulars
      1. POHow to debug a user defined aggregate function in Oracle 11g?
      1. USwcmatthysen
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload