SQL> CREATE OR REPLACE FUNCTION find_gcd (
  2      p_n1    IN  POSITIVE
  3  ,   p_n2    IN  POSITIVE
  4  )
  5  RETURN POSITIVE
  6  IS
  7      l_n1    POSITIVE := p_n1;
  8      l_n2    POSITIVE := p_n2;
  9  BEGIN
 10      WHILE NOT (l_n1 = l_n2)
 11      LOOP
 12          CASE SIGN(l_n1 - l_n2)
 13          WHEN +1
 14          THEN l_n1 := l_n1 - l_n2;
 15          ELSE l_n2 := l_n2 - l_n1;
 16          END CASE;
 17      END LOOP;
 18      RETURN (l_n1);
 19  END find_gcd;
 20  /
 
Function created.
 
SQL> SELECT find_gcd(find_gcd(10,15),25) gcd
  2  FROM   DUAL
  3  /
 
       GCD
----------
         5
 
SQL>

SQL> CREATE OR REPLACE TYPE gcd_agg AS OBJECT (
  2      factor  NUMBER
  3  ,   STATIC FUNCTION odciaggregateinitialize (
  4                          sctx IN OUT gcd_agg)
  5      RETURN NUMBER
  6  ,   MEMBER FUNCTION odciaggregateiterate (
  7                          SELF    IN OUT  gcd_agg
  8                      ,   value   IN      NUMBER)
  9      RETURN NUMBER
 10  ,   MEMBER FUNCTION odciaggregateterminate (
 11                          SELF        IN  gcd_agg
 12                      ,   returnvalue OUT NUMBER
 13                      ,   flags       IN  NUMBER)
 14      RETURN NUMBER
 15  ,   MEMBER FUNCTION odciaggregatemerge (
 16                          SELF    IN OUT  gcd_agg
 17                      ,   ctx2    IN      gcd_agg)
 18      RETURN NUMBER
 19  );
 20  /
 
Type created.
 
SQL> CREATE OR REPLACE TYPE BODY gcd_agg AS
  2      STATIC FUNCTION odciaggregateinitialize (
  3                              sctx IN OUT gcd_agg)
  4      RETURN NUMBER
  5      IS
  6      BEGIN
  7          sctx := gcd_agg(TO_NUMBER(NULL));
  8          RETURN (odciconst.success);
  9      END odciaggregateinitialize;
 10
 11      MEMBER FUNCTION odciaggregateiterate (
 12                          SELF    IN OUT  gcd_agg
 13                      ,   value   IN      NUMBER)
 14      RETURN NUMBER
 15      IS
 16          l_value         POSITIVE;
 17          l_return_value  NUMBER;
 18      BEGIN
 19          BEGIN
 20              l_value := ABS(TRUNC(value));
 21              IF (SELF.factor IS NULL
 22              OR SELF.factor <= 0) THEN
 23                  SELF.factor := l_value;
 24              ELSE
 25                  WHILE NOT (SELF.factor = l_value)
 26                  LOOP
 27                      CASE SIGN(SELF.factor - l_value)
 28                      WHEN +1
 29                      THEN SELF.factor := SELF.factor - l_value;
 30                      ELSE l_value := l_value - SELF.factor;
 31                      END CASE;
 32                  END LOOP;
 33              END IF;
 34              l_return_value := odciconst.success;
 35          EXCEPTION
 36              WHEN VALUE_ERROR THEN
 37                  l_return_value := odciconst.error;
 38          END;
 39          RETURN (l_return_value);
 40      END odciaggregateiterate;
 41
 42      MEMBER FUNCTION odciaggregateterminate (
 43                          SELF        IN  gcd_agg
 44                      ,   returnvalue OUT NUMBER
 45                      ,   flags       IN  NUMBER)
 46      RETURN NUMBER
 47      IS
 48      BEGIN
 49          returnvalue := SELF.factor;
 50          RETURN (odciconst.success);
 51      END odciaggregateterminate;
 52
 53      MEMBER FUNCTION odciaggregatemerge (
 54                          SELF    IN OUT  gcd_agg
 55                      ,   ctx2    IN      gcd_agg)
 56      RETURN NUMBER
 57      IS
 58          l_value_1   POSITIVE := SELF.factor;
 59          l_value_2   POSITIVE := ctx2.factor;
 60      BEGIN
 61          IF (l_value_1 IS NULL
 62          OR l_value_1 <= 0) THEN
 63              l_value_1 := l_value_2;
 64          ELSE
 65              WHILE NOT (l_value_1 = l_value_2)
 66              LOOP
 67                  CASE SIGN(l_value_1 - l_value_2)
 68                  WHEN +1
 69                  THEN l_value_1 := l_value_1 - l_value_2;
 70                  ELSE l_value_2 := l_value_2 - l_value_1;
 71                  END CASE;
 72              END LOOP;
 73          END IF;
 74          RETURN (odciconst.success);
 75      END odciaggregatemerge;
 76  END;
 77  /
 
Type body created.
 
SQL> CREATE OR REPLACE FUNCTION gcd (input NUMBER)
  2  RETURN NUMBER
  3  PARALLEL_ENABLE AGGREGATE USING gcd_agg;
  4  /
 
Function created.
 
SQL> CREATE TABLE t (id VARCHAR2(2), n INT)
  2  /
 
Table created.
 
SQL> INSERT INTO t VALUES ('A',25);
SQL> INSERT INTO t VALUES ('A',30);
SQL> INSERT INTO t VALUES ('A',55);
SQL> INSERT INTO t VALUES ('B',77);
SQL> INSERT INTO t VALUES ('C',7176);
SQL> INSERT INTO t VALUES ('C',5428);
SQL> INSERT INTO t VALUES ('C',7820);
 
SQL> SELECT id, gcd(t.n) FROM t GROUP BY id
  2  /
 
ID   GCD(T.N)
-- ----------
A           5
B          77
C          92
 
SQL>

create or replace 
type gcf_type as object 
( 
  num integer,
  static function ODCIAggregateInitialize 
    ( sctx in out gcf_type ) 
    return number ,
  member function ODCIAggregateIterate 
    ( self  in out gcf_type , 
      value in     integer 
    ) return number ,
  member function ODCIAggregateTerminate 
    ( self        in  gcf_type, 
      returnvalue out integer, 
      flags in number 
    ) return number ,
  member function ODCIAggregateMerge 
    ( self in out gcf_type, 
      ctx2 in     gcf_type 
    ) return number 
); 
/

create or replace 
type body gcf_type 
is
  static function ODCIAggregateInitialize 
  ( sctx in out gcf_type ) 
  return number 
  is 
  begin 
    sctx := gcf_type( null ) ; 
    return ODCIConst.Success ; 
  end;
  member function ODCIAggregateIterate 
  ( self  in out gcf_type , 
    value in     integer 
  ) return number 
  is 
     big integer; 
     sml integer; 
     rem integer; 
     val number; 
  begin 
    CASE 
        WHEN self.num IS NULL  OR self.num = 0   THEN self.num := abs(value); 
        WHEN value IS NULL     OR value = 0      THEN NULL; 
        WHEN abs(self.num) = 1 OR abs(value) = 1 THEN self.num := 1; 
        WHEN abs(self.num) = abs(value)          THEN NULL; 
        ELSE 
            big := greatest(abs(self.num), abs(value)); 
            sml := least(abs(self.num), abs(value));           
            self.num := sml;           
            rem := mod(big, sml); 
            IF rem > 0 THEN 
                val := self.ODCIAggregateIterate(rem); 
            END IF; 
    END CASE; 
    
    return ODCIConst.Success; 
  end;
  member function ODCIAggregateTerminate 
  ( self        in  gcf_type , 
    returnvalue out integer , 
    flags       in  number 
  ) return number 
  is 
  begin 
    returnValue := self.num; 
    return ODCIConst.Success; 
  end;
  member function ODCIAggregateMerge 
  ( self in out gcf_type , 
    ctx2 in     gcf_type 
  ) return number 
  is 
      val number; 
  begin 
    val := self.ODCIAggregateIterate(ctx2.num); 
    return ODCIConst.Success; 
  end;
end; 
/

create or replace 
function gcf 
  ( input integer ) 
  return integer 
  deterministic 
  parallel_enable 
  aggregate using gcf_type 
; 
/