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