3 Replies Latest reply: Dec 1, 2010 2:52 PM by 799380 RSS

    Custom aggregate function inside a package.

    799380
      Hi there,

      I'm trying to write a custom aggregate function 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;

      this statement:

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

      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));

      explodes with:

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

      Is it possible to have custom aggregate functions nested inside package declarations?
      I'm using Oracle 11g, Release 2 (11.2.0.1.0).
        • 1. Re: Custom aggregate function inside a package.
          789943
          Is it possible to have custom aggregate functions nested inside package declarations?
          Yes, it is possible, you have succesfuly created your function. Your problem is that the database throws ORA-600 on execute. And with ORA-600 you can do 2 things: 1) google ORA-600 17090 or 2) contact your Oracle Support.
          You could also try to declare the function without PARALLEL_ENABLE - just to try to see if it changes anything. You can also try to call your function against a regular table with rows and columns - not against an collection type with table() operator.
          Anyway - these 2 tests should be usefull for Oracle Support.

          I never tried to put a custom aggregate function into a package. First - the cases when you need a custom aggregate function to be written for your system are very rare. Second - even if I needed 1 then I never needed 2 or more custom aggregate functions on my system. And as I do not like to make my life more complex than necessary, I have created it as a stand-alone function. And it is works (slowly).
          • 2. Re: Custom aggregate function inside a package.
            odie_63
            Hi,

            The documentation states that we may (must?) use CREATE FUNCTION :
            http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10765/aggr_functions.htm#BEJBEBHE

            So maybe it means packaged aggregate functions aren't supported.
            • 3. Re: Custom aggregate function inside a package.
              799380
              HiddenName wrote:
              Is it possible to have custom aggregate functions nested inside package declarations?
              Yes, it is possible, you have succesfuly created your function. Your problem is that the database throws ORA-600 on execute. And with ORA-600 you can do 2 things: 1) google ORA-600 17090 or 2) contact your Oracle Support.
              You could also try to declare the function without PARALLEL_ENABLE - just to try to see if it changes anything. You can also try to call your function against a regular table with rows and columns - not against an collection type with table() operator.
              Anyway - these 2 tests should be usefull for Oracle Support.

              I never tried to put a custom aggregate function into a package. First - the cases when you need a custom aggregate function to be written for your system are very rare. Second - even if I needed 1 then I never needed 2 or more custom aggregate functions on my system. And as I do not like to make my life more complex than necessary, I have created it as a stand-alone function. And it is works (slowly).
              I tried using a standard table as you suggested:
              -----
              CREATE TABLE TEST_DATA
              (
              test_value NUMBER
              );

              INSERT INTO TEST_DATA
              (SELECT column_value test_value from TABLE(vector(1, 2, 1, 45, 22, -1)));

              COMMIT;

              select my_sum(test_value) from test_data;

              select my_functions.my_sum(test_value) from test_data;
              -----

              I also tried removing the PARALLEL_ENABLE clause to create the package as follows:

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

              And unfortunately it still breaks with the following error: SQL Error: ORA-00600: internal error code, arguments: [17090]. This looks like an Oracle bug to me as the PL/SQL parsing engine should have disallowed me to even create this if it is not supported in Oracle. Instead, it allows me to create the package, and breaks when I call the function with this weird error (additionally cutting my connection from the database) instead of disallowing me to do this altogether and printing a nice error message telling me that Oracle doesn't support this. How would I go about logging a ticket for this?

              Edited by: wcmatthysen on Dec 1, 2010 12:51 PM