Oracle own aggregation function in package

I created my own Oracle type and aggregation function from it:

As separate one, the function is created as:

create or replace FUNCTION MY_AGGR_FN (input NUMBER) RETURN NUMBER 
PARALLEL_ENABLE AGGREGATE USING MY_AGGR_TYPE;

It works fine! Now I'd like to put this function into pwn package but I do not see how to create package body from this.

This is my package declaration:

create or replace PACKAGE MY_PKG AS 

  FUNCTION MY_AGGR_FN(n NUMBER) RETURN NUMBER;

END MY_PKG;

But in package body I cannot just put:

create or replace PACKAGE BODY MY_PKG AS

  FUNCTION MY_AGGR_FN(n NUMBER) RETURN NUMBER
  PARALLEL_ENABLE AGGREGATE USING MY_AGGR_TYPE;

END MY_PKG;

You cannot define an aggregation function in the public signature of a package as the syntax does not allow it.

---

The CREATE FUNCTION documentation gives the syntax for a stand-alone function as:

create_function ::=

plsql_function_source ::=

The CREATE PACKAGE documentation gives the syntax for a package function signature as:

package_function_declaration ::=

While a stand-alone function can include the AGGREGATE clause in its signature, a package function signature does not allow that option (and also does not allow the Invokers Rights, Default Collation or SQL Macro clauses).

So:

CREATE TYPE FirstAggregationType AS OBJECT(
  value NUMBER,

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT FirstAggregationType
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT FirstAggregationType,
    value       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT FirstAggregationType,
    returnValue    OUT NUMBER,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT FirstAggregationType,
    ctx         IN OUT FirstAggregationType
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY FirstAggregationType
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT FirstAggregationType
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := FirstAggregationType( NULL );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT FirstAggregationType,
    value       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    IF self.value IS NULL THEN
      self.value := value;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT FirstAggregationType,
    returnValue    OUT NUMBER,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := self.value;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT FirstAggregationType,
    ctx         IN OUT FirstAggregationType
  ) RETURN NUMBER
  IS
  BEGIN
    self.value := COALESCE( self.value, ctx.value );
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

CREATE PACKAGE tools IS
  FUNCTION FIRST( value NUMBER )
  RETURN NUMBER PARALLEL_ENABLE;
END tools;
/

CREATE PACKAGE BODY tools IS
  FUNCTION FIRST( value NUMBER )
  RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING FirstAggregationType;
END tools;
/

Will compile but if you try to use it:

CREATE TABLE table_name ( id, group_id, value ) AS
SELECT 1, 13, NULL FROM DUAL UNION ALL
SELECT 2, 13,    1 FROM DUAL UNION ALL
SELECT 3, 13,    2 FROM DUAL UNION ALL
SELECT 4, 28,    1 FROM DUAL UNION ALL
SELECT 5, 28, NULL FROM DUAL UNION ALL
SELECT 6, 28,    4 FROM DUAL;

SELECT group_id,
       Tools.First( value ) AS first_value
FROM   table_name
GROUP BY group_id;

You get the exception:

ORA-00979: not a GROUP BY expression

Since the function signature does not define it as an aggregation function.

If you try to modify the package signature to add the AGGREGATE clause:

CREATE OR REPLACE PACKAGE tools IS
  FUNCTION FIRST( value NUMBER )
  RETURN NUMBER PARALLEL_ENABLE AGGREGATE;
END tools;
/

Then you get the exception:

PLS-00220: simple name required in this context

fiddle