Databases
StackOverflow2013
Postsdbo
POCustom aggregate function inside a package
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCustom aggregate function inside a package
    primarykey
    data
    text
    <p>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:</p> <pre><code>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 ); </code></pre> <hr> <pre><code>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; </code></pre> <p>If I write the following function definition:</p> <pre><code>CREATE OR REPLACE FUNCTION MY_SUM(input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SUM_AGGREGATOR_TYPE; </code></pre> <p>and corresponding type declaration to test:</p> <pre><code>CREATE OR REPLACE TYPE VECTOR IS TABLE OF NUMBER; </code></pre> <p>this statement:</p> <pre><code>select my_sum(column_value) from table(vector(1, 2, 1, 45, 22, -1)); </code></pre> <p>gives the correct result of 70. However, creating a package with the function definition:</p> <pre><code>CREATE OR REPLACE PACKAGE MY_FUNCTIONS AS FUNCTION MY_SUM(input NUMBER) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING SUM_AGGREGATOR_TYPE; END; </code></pre> <p>and calling it via:</p> <pre><code>select MY_FUNCTIONS.my_sum(column_value) from table(vector(1, 2, 1, 45, 22, -1)); </code></pre> <p>explodes with</p> <pre><code>ORA-00600: internal error code, arguments: [17090], [], [], [], [], [], [], [], [], [], [], [] </code></pre> <p>Is it possible to have custom aggregate functions nested inside package declarations?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USwcmatthysen
    1. USwcmatthysen
    plurals
    1. 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. POCustom aggregate function inside a package
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCustom aggregate function inside a package
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCustom aggregate function inside a package
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO'ORA-00600' usually indicates an Oracle bug - sometimes due to unexpected combinations of features. What exact Oracle version are you running?
      singulars
      1. POCustom aggregate function inside a package
      1. USJeffrey Kemp
    2. COI'm using Oracle 11g Release 2 (11.2.0.1.0).
      singulars
      1. POCustom aggregate function inside a package
      1. USwcmatthysen
    3. CODo you have any news on this issue? I have problems too, however, in my case the connection is aborted `ORA-03113: end-of-file on communication channel`. I guess this is even worse than the `ORA-00600`. When I check `SELECT * FROM ALL_PROCEDURES`, I can see that the user-defined aggregate has been correctly created and registered...
      singulars
      1. POCustom aggregate function inside a package
      1. USLukas Eder
 

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