-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- NULL Aggregate - minimal aggregate syntax, returns NULL
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE null_agg_type
AS OBJECT
(
v_dummy char(1), -- every Oracle object type must include at least one attribute
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT null_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT null_agg_type, p_notused IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT null_agg_type, ctx2 IN null_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(self IN null_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY null_agg_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT null_agg_type)
RETURN NUMBER
IS
BEGIN
-- instantiate our type, NULL the dummy attribute
ctx := null_agg_type(null);
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT null_agg_type, p_notused IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
-- In normal functions you would do something with the input
-- add it, count it, square it, etc. but not in this null-aggregate
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(self IN OUT null_agg_type, ctx2 IN null_agg_type)
RETURN NUMBER
IS
BEGIN
-- If you have 2 results sets to consolidate, for instance parallel processing of a table
-- you will resolve the combination here.
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(self IN null_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
-- Set your return value, usually this would involve your attributes and possibly other
-- functions. For these simple null-aggregates we simply assign NULL and exit.
returnvalue := NULL;
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION nullagg(p_string VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING null_agg_type;
select count(*), nullagg(table_name) from all_tables;
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- Counting Aggregate
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE count_agg_type
AS OBJECT
(
v_result INTEGER, -- We need a place to store our count as we iterate through the group sets
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT count_agg_type)
RETURN NUMBER,
-- As with the null aggregate, we're not really using the input parameter, we're simply counting rows.
MEMBER FUNCTION odciaggregateiterate(self IN OUT count_agg_type, p_notused IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT count_agg_type, ctx2 IN count_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(self IN count_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY count_agg_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT count_agg_type)
RETURN NUMBER
IS
BEGIN
-- At the start of our count we will, of course, have 0.
ctx := count_agg_type(0);
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT count_agg_type, p_notused IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
-- As we iterate through each value, increment our counter
self.v_result := self.v_result + 1;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(self IN OUT count_agg_type, ctx2 IN count_agg_type)
RETURN NUMBER
IS
BEGIN
-- If we have two results, the merged result is simply the sum of both
self.v_result := self.v_result + ctx2.v_result;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(self IN count_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
-- No finalizing is required when done, the count is complete, so return and exit.
returnvalue := self.v_result;
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION countagg(p_string VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING count_agg_type;
select count(*),countagg(table_name) from all_tables;
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- Maximum Aggregate for numbers
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE max_num_agg_type
AS OBJECT
(
v_result NUMBER, -- We need a place to store our maximum as we iterate through the group sets
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT max_num_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT max_num_agg_type, p_number IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT max_num_agg_type, ctx2 IN max_num_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(self IN max_num_agg_type,
returnvalue OUT NUMBER,
flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY max_num_agg_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT max_num_agg_type)
RETURN NUMBER
IS
BEGIN
ctx := max_num_agg_type(NULL); -- initialize our max value to null
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT max_num_agg_type, p_number IN NUMBER)
RETURN NUMBER
IS
BEGIN
IF self.v_result IS NULL
THEN
self.v_result := p_number; -- the first number is automatically the maximum
ELSE
self.v_result := GREATEST(self.v_result, p_number); -- compare new number to old, keep the bigger
END IF;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(self IN OUT max_num_agg_type, ctx2 IN max_num_agg_type)
RETURN NUMBER
IS
BEGIN
IF self.v_result IS NULL
THEN
self.v_result := ctx2.v_result; -- if merging two sets, if the current is null, use the new one
ELSIF ctx2.v_result IS NOT NULL
THEN
self.v_result := GREATEST(self.v_result, ctx2.v_result); -- if both are populated then use the bigger
END IF;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(self IN max_num_agg_type,
returnvalue OUT NUMBER,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := self.v_result; -- like the counting aggregate, no finalizing processes are needed
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION maxnumagg(p_number NUMBER)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING max_num_agg_type;
SELECT max(num_rows), maxnumagg(num_rows) FROM user_tables;
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- Sum Aggregate for Day to Second Intervals
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE dsinterval_agg_type
AS OBJECT
(
v_result INTERVAL DAY TO SECOND, -- our working result
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT dsinterval_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(
self IN OUT dsinterval_agg_type,
p_interval IN INTERVAL DAY TO SECOND
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(
self IN OUT dsinterval_agg_type,
ctx2 IN dsinterval_agg_type
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN dsinterval_agg_type,
returnvalue OUT INTERVAL DAY TO SECOND,
flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY dsinterval_agg_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT dsinterval_agg_type)
RETURN NUMBER
IS
BEGIN
ctx := dsinterval_agg_type(NULL); -- initialize the sum to NULL
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(
self IN OUT dsinterval_agg_type,
p_interval IN INTERVAL DAY TO SECOND
)
RETURN NUMBER
IS
BEGIN
IF self.v_result IS NULL
THEN
self.v_result := p_interval; -- the first interval is the sum
ELSE
self.v_result := self.v_result + p_interval; -- add each new interval to the running total
END IF;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(
self IN OUT dsinterval_agg_type,
ctx2 IN dsinterval_agg_type
)
RETURN NUMBER
IS
BEGIN
IF self.v_result IS NULL
THEN
self.v_result := ctx2.v_result; -- If our sum is NULL, use the result of the other set
ELSIF ctx2.v_result IS NOT NULL
THEN
self.v_result := self.v_result + ctx2.v_result; -- If both sets have sums, add them to get the total
END IF;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(
self IN dsinterval_agg_type,
returnvalue OUT INTERVAL DAY TO SECOND,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := self.v_result; -- the iterate and merge did all the work, return the result
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION dsintervalagg(p_interval INTERVAL DAY TO SECOND)
RETURN INTERVAL DAY TO SECOND
PARALLEL_ENABLE
AGGREGATE USING dsinterval_agg_type;
SELECT dsintervalagg(i)
FROM (SELECT SYSTIMESTAMP - SYSDATE i FROM DUAL
UNION ALL
SELECT NUMTODSINTERVAL(3.42, 'second') FROM DUAL);
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- Concatenation Aggregate for strings (a.k.a. stragg)
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE concat_agg_type
AS OBJECT
(
-- This could be defined to 32767, but since the aggregate will be used in SQL,
-- the 4000 limit is appropriate since that's all that SQL varchar2 strings support
v_result VARCHAR2(4000),
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT concat_agg_type, p_string IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT concat_agg_type, ctx2 IN concat_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(self IN concat_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY concat_agg_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_agg_type)
RETURN NUMBER
IS
BEGIN
ctx := concat_agg_type(NULL); -- initialize the concatenation to NULL
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT concat_agg_type, p_string IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
self.v_result := self.v_result || ',' || p_string; -- Append a delimiter and new value
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(self IN OUT concat_agg_type, ctx2 IN concat_agg_type)
RETURN NUMBER
IS
BEGIN
-- If merging, simply concatenate them together
-- Since each string will either be NULL or delimiter prefixed, no need to re-delimit
self.v_result := self.v_result || ctx2.v_result;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(self IN concat_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
-- Since we prefix the string initially with a comma, remove the extra here before returning
returnvalue := LTRIM(self.v_result, ',');
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION concatagg(p_string VARCHAR2)
RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
USING concat_agg_type;
select concatagg(table_name) from all_tables where owner = 'SCOTT';
CREATE OR REPLACE TYPE CONCAT_CLOB_AGG_TYPE
AS OBJECT
(
-- This could be defined to 32767, but since the aggregate will be used in SQL,
-- the 4000 limit is appropriate since that's all that SQL varchar2 strings support
v_temp VARCHAR2(32767),
v_result CLOB,
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_clob_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT concat_clob_agg_type, p_string IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(
self IN OUT concat_clob_agg_type,
ctx2 IN concat_clob_agg_type
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN concat_clob_agg_type,
returnvalue OUT CLOB,
flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY CONCAT_CLOB_AGG_TYPE
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT concat_clob_agg_type)
RETURN NUMBER
IS
BEGIN
ctx := concat_clob_agg_type('', ''); -- initialize the concatenation to NULL
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT concat_clob_agg_type, p_string IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
-- Appending to clobs is slower than appending to varchar2
-- so use varchar2 until you can't anymore then append one big chunk
self.v_temp := self.v_temp || ',' || p_string; -- Append a delimiter and new value
IF LENGTH(self.v_temp) > 28700
THEN
self.v_result := self.v_result || self.v_temp;
self.v_temp := NULL;
END IF;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(
self IN OUT concat_clob_agg_type,
ctx2 IN concat_clob_agg_type
)
RETURN NUMBER
IS
BEGIN
-- If merging, simply concatenate them together
-- Since each string will either be NULL or delimiter prefixed, no need to re-delimit
self.v_result := self.v_result || self.v_temp || ctx2.v_result || ctx2.v_temp;
self.v_temp := NULL;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(
self IN concat_clob_agg_type,
returnvalue OUT CLOB,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
-- Since we prefix the string initially with a comma, remove the extra here before returning
returnvalue := LTRIM(self.v_result || self.v_temp, ',');
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION concatclobagg(p_string VARCHAR2)
RETURN CLOB
PARALLEL_ENABLE
AGGREGATE USING concat_clob_agg_type;
SELECT concatclobagg(owner || '.' || object_name) FROM all_objects;
CREATE OR REPLACE TYPE vcarray AS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION tbl2clob(p_tbl IN vcarray, p_delimiter IN VARCHAR2 DEFAULT ',')
RETURN CLOB
DETERMINISTIC
IS
v_str VARCHAR2(32767);
v_clob CLOB;
BEGIN
DBMS_LOB.createtemporary(v_clob, TRUE);
IF p_tbl.COUNT > 0
THEN
v_str := p_tbl(1);
FOR i IN 2 .. p_tbl.COUNT
LOOP
-- Appending to clobs is slower than appending to varchar2
-- so use varchar2 until you can't anymore then append one big chunk
v_str := v_str || p_delimiter || p_tbl(i);
IF LENGTH(v_str) > 28700
THEN
v_clob := v_clob || v_str;
v_str := NULL;
END IF;
END LOOP;
END IF;
IF v_str IS NOT NULL
THEN
v_clob := v_clob || v_str;
END IF;
RETURN v_clob;
END;
SELECT tbl2clob(CAST(COLLECT(owner || '.' || object_name ORDER BY owner, object_name) AS vcarray))
FROM all_objects;
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- Product Aggregate for numbers (multiply numbers in set, vs. sum which adds)
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE product_agg_type
AS OBJECT
(
v_product NUMBER, -- Store our running product
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT product_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT product_agg_type, p_number IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT product_agg_type, ctx2 IN product_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN product_agg_type,
returnvalue OUT NUMBER,
flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY product_agg_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT product_agg_type)
RETURN NUMBER
IS
BEGIN
ctx := product_agg_type(NULL); -- Initialize the result to NULL
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT product_agg_type, p_number IN NUMBER)
RETURN NUMBER
IS
BEGIN
-- Multiply each new number by the running product, if we don't have a product yet, seed it with 1.
self.v_product := NVL(self.v_product, 1) * p_number;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(self IN OUT product_agg_type, ctx2 IN product_agg_type)
RETURN NUMBER
IS
BEGIN
-- Multiply the products of 2 sets, using 1 for any NULL's
-- Make sure at least one of the sets has a value though
IF COALESCE(self.v_product, ctx2.v_product) IS NOT NULL
THEN
self.v_product := NVL(self.v_product, 1) * NVL(ctx2.v_product, 1);
END IF;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(
self IN product_agg_type,
returnvalue OUT NUMBER,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := self.v_product;
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION productagg(p_number IN NUMBER)
RETURN NUMBER
PARALLEL_ENABLE
AGGREGATE USING product_agg_type;
select productagg(level) from dual connect by level <= 6;
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- Average Aggregate for dates
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE avg_date_agg_type
AS OBJECT
(
v_day_sum NUMBER, -- running total of date offsets
v_day_count INTEGER, -- number of dates we've summed
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT avg_date_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT avg_date_agg_type, p_date IN DATE)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT avg_date_agg_type, ctx2 IN avg_date_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN avg_date_agg_type,
returnvalue OUT DATE,
flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY avg_date_agg_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT avg_date_agg_type)
RETURN NUMBER
IS
BEGIN
ctx := avg_date_agg_type(0, 0); -- initialize the sum and count both to 0
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT avg_date_agg_type, p_date IN DATE)
RETURN NUMBER
IS
BEGIN
-- The TO_CHAR function with 'j' format return the Julian value
-- for the day portion only and drops the time
-- this function will add the time as a fractional day to the Julian.
-- Subtract one to create a delta from minimum-supported time.
self.v_day_sum :=
self.v_day_sum + TO_NUMBER(TO_CHAR(p_date, 'j')) + (p_date - TRUNC(p_date)) - 1;
self.v_day_count := self.v_day_count + 1;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(self IN OUT avg_date_agg_type, ctx2 IN avg_date_agg_type)
RETURN NUMBER
IS
BEGIN
-- Add the sums and counts to generate an average over both sets
self.v_day_sum := self.v_day_sum + ctx2.v_day_sum;
self.v_day_count := self.v_day_count + ctx2.v_day_sum;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(
self IN avg_date_agg_type,
returnvalue OUT DATE,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
-- If we haven't iterated over any values, the count will be 0, so the average is NULL
-- we can't divide by zero anyway. If we do have a count then generate the mean delta
-- and add it to the start of the Oracle calendar.
returnvalue :=
CASE
WHEN self.v_day_count > 0
THEN
-- Add the average delta to min-time to create the "average" date
TO_DATE('1', 'j') + (self.v_day_sum / self.v_day_count)
ELSE
NULL
END;
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION avgdateagg(p_date DATE)
RETURN DATE
PARALLEL_ENABLE
AGGREGATE USING avg_date_agg_type;
SELECT avgdateagg(last_analyzed) FROM all_tables;
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
--- Distinct characters
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE distinct_char_agg_type
AS OBJECT
(
v_result VARCHAR2(4000), -- as with the string concatenation function above, we can only return 4000 characters in SQL
MEMBER FUNCTION distinct_chars(p_unique IN VARCHAR2, p_new IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC,
STATIC FUNCTION odciaggregateinitialize(p_ctx IN OUT distinct_char_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT distinct_char_agg_type, p_string IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(
self IN OUT distinct_char_agg_type,
p_ctx2 IN distinct_char_agg_type
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN distinct_char_agg_type,
p_returnvalue OUT VARCHAR2,
p_flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY distinct_char_agg_type
IS
-- Given a string of unique characters and a new string, check if the new string
-- introduces any new characters that need to be added to the unique set.
-- This function assumes the "unique" string has already been validated to be unique
-- The usage of this function in the rest of the object's functions ensures this is true.
MEMBER FUNCTION distinct_chars(p_unique IN VARCHAR2, p_new IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC
IS
v_temp VARCHAR2(32767) := NULL;
v_index INTEGER;
v_char CHAR(1);
BEGIN
v_temp := p_unique;
-- Look at each character of the new string
-- if it's unique then add it via simple sorted insertion
FOR i IN 1 .. NVL(LENGTH(p_new), 0)
LOOP
v_char := SUBSTR(p_new, i, 1);
-- Is the new character already in our unique string?
IF NVL(INSTR(v_temp, v_char), 0) = 0
THEN
v_index := 1;
-- The character is new, iterate through the unique string
-- to determine where to put the new character, this creates a sorted output.
WHILE v_index <= NVL(LENGTH(v_temp), 0)
LOOP
IF v_char < SUBSTR(v_temp, v_index, 1)
THEN
v_temp :=
SUBSTR(v_temp, 1, v_index - 1) || v_char || SUBSTR(v_temp, v_index);
EXIT;
END IF;
v_index := v_index + 1;
END LOOP;
IF v_index > NVL(LENGTH(v_temp), 0)
THEN
v_temp := v_temp || v_char;
END IF;
END IF;
END LOOP;
RETURN v_temp;
END distinct_chars;
STATIC FUNCTION odciaggregateinitialize(p_ctx IN OUT distinct_char_agg_type)
RETURN NUMBER
IS
BEGIN
p_ctx := distinct_char_agg_type(NULL); -- Initialize our unique set to be NULL
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT distinct_char_agg_type, p_string IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
-- Use or new function to add distinct characters of each new string
self.v_result := distinct_chars(self.v_result, p_string);
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(
self IN OUT distinct_char_agg_type,
p_ctx2 IN distinct_char_agg_type
)
RETURN NUMBER
IS
BEGIN
-- As with iterate, simply add new characters from the other set.
self.v_result := distinct_chars(self.v_result, p_ctx2.v_result);
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(
self IN distinct_char_agg_type,
p_returnvalue OUT VARCHAR2,
p_flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
p_returnvalue := self.v_result; -- Iterate and Merge do all the work, return the result
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION distinctagg(p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING distinct_char_agg_type;
select distinctagg(table_name) from user_tables;
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- Most common day of the week aggregate for dates
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE common_dow_agg_type
AS OBJECT
(
v_sun_count INTEGER,
v_mon_count INTEGER,
v_tue_count INTEGER,
v_wed_count INTEGER,
v_thu_count INTEGER,
v_fri_count INTEGER,
v_sat_count INTEGER,
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT common_dow_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT common_dow_agg_type, p_date IN DATE)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(
self IN OUT common_dow_agg_type,
ctx2 IN common_dow_agg_type
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN common_dow_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY common_dow_agg_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT common_dow_agg_type)
RETURN NUMBER
IS
BEGIN
-- initialize all of the counters to zero
ctx :=
common_dow_agg_type(
0,
0,
0,
0,
0,
0,
0
);
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT common_dow_agg_type, p_date IN DATE)
RETURN NUMBER
IS
BEGIN
-- increment the appropriate counter for each day of the week
CASE TO_CHAR(p_date, 'Dy')
WHEN 'Sun'
THEN
self.v_sun_count := self.v_sun_count + 1;
WHEN 'Mon'
THEN
self.v_mon_count := self.v_mon_count + 1;
WHEN 'Tue'
THEN
self.v_tue_count := self.v_tue_count + 1;
WHEN 'Wed'
THEN
self.v_wed_count := self.v_wed_count + 1;
WHEN 'Thu'
THEN
self.v_thu_count := self.v_thu_count + 1;
WHEN 'Fri'
THEN
self.v_fri_count := self.v_fri_count + 1;
WHEN 'Sat'
THEN
self.v_sat_count := self.v_sat_count + 1;
END CASE;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(
self IN OUT common_dow_agg_type,
ctx2 IN common_dow_agg_type
)
RETURN NUMBER
IS
BEGIN
-- sum the counts from both sets
self.v_sun_count := self.v_sun_count + ctx2.v_sun_count;
self.v_mon_count := self.v_mon_count + ctx2.v_mon_count;
self.v_tue_count := self.v_tue_count + ctx2.v_tue_count;
self.v_wed_count := self.v_wed_count + ctx2.v_wed_count;
self.v_thu_count := self.v_thu_count + ctx2.v_thu_count;
self.v_fri_count := self.v_fri_count + ctx2.v_fri_count;
self.v_sat_count := self.v_sat_count + ctx2.v_sat_count;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(
self IN common_dow_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
v_max INTEGER
:= GREATEST(
self.v_sun_count,
self.v_mon_count,
self.v_tue_count,
self.v_wed_count,
self.v_thu_count,
self.v_fri_count,
self.v_sat_count
);
BEGIN
-- Return whichever day has the highest count
-- If there is a tie, then earliest in the week will be returned
returnvalue :=
CASE
WHEN self.v_sun_count = v_max THEN 'Sunday'
WHEN self.v_mon_count = v_max THEN 'Monday'
WHEN self.v_tue_count = v_max THEN 'Tuesday'
WHEN self.v_wed_count = v_max THEN 'Wednesday'
WHEN self.v_thu_count = v_max THEN 'Thursday'
WHEN self.v_fri_count = v_max THEN 'Friday'
ELSE 'Saturday'
END;
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION day_of_weekagg(p_date DATE)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING common_dow_agg_type;
SELECT day_of_weekagg(last_analyzed) FROM all_tables;
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- Return random value from a set (simple version)
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE random_agg_type
AS OBJECT
(
v_string VARCHAR2(4000),
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT random_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT random_agg_type, p_string IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT random_agg_type, ctx2 IN random_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN random_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY random_agg_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT random_agg_type)
RETURN NUMBER
IS
BEGIN
ctx := random_agg_type(NULL); -- initialize our random selection to NULL
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT random_agg_type, p_string IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
-- If we haven't set a value yet, then use the first value passed in
-- if we have a value "flip a coin" to decide if we use the new value
-- or keep the old one.
IF v_string IS NULL
THEN
v_string := p_string;
ELSIF DBMS_RANDOM.random < 0
THEN
v_string := p_string;
END IF;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(self IN OUT random_agg_type, ctx2 IN random_agg_type)
RETURN NUMBER
IS
BEGIN
-- "flip a coin" to decide which value to keep
IF DBMS_RANDOM.random < 0
THEN
v_string := ctx2.v_string;
END IF;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(
self IN random_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := v_string;
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION random_value_agg(p_string VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING random_agg_type;
SELECT department_id, random_value_agg(salary)
FROM hr.employees
GROUP BY department_id;
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- Return random value from a set (less skew version)
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE random_agg_type
AS OBJECT
(
v_string VARCHAR2(4000), -- our current random selection
v_count NUMBER, -- how many values have we iterated through
MEMBER FUNCTION random_0n(p_n IN INTEGER)
RETURN INTEGER,
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT random_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT random_agg_type, p_string IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT random_agg_type, ctx2 IN random_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN random_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
);
CREATE OR REPLACE TYPE BODY random_agg_type
IS
-- We only want to select random values 0-N (actually N-1), but no such function exists in DBMS_RANDOM package
-- so we'll create one and adjust for modulo bias to ensure even distribution.
MEMBER FUNCTION random_0n(p_n IN INTEGER)
RETURN INTEGER
IS
v_biasmax INTEGER;
v_result INTEGER;
BEGIN
IF p_n <= 0 OR p_n > 2 ** 32 - 1
THEN
RAISE VALUE_ERROR;
END IF;
-- To prevent modulo bias limit the random range
-- to the largest number evenly divisible by N
v_biasmax := (2 ** 32 - 1) - MOD(2 ** 32 - 1, p_n);
-- If random number is greater than the bias limit
-- then pick a new number.
-- This "could" theoretically run forever but in practice it'll terminate
-- within the first or "maybe" second cycle.
-- DBMS_RANDOM.RANDOM is obsolete, but included here simply
-- for purposes of example and is compatible with all versions from 9i forward.
-- Due to range limitation of dbms_random.random the result will only
-- be from the first 4,294,967,296 values within the aggregation group.
-- all others will be ignored.
LOOP
v_result := DBMS_RANDOM.random + (2 ** 31);
EXIT WHEN v_result <= v_biasmax;
END LOOP;
RETURN MOD(v_result, p_n);
END random_0n;
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT random_agg_type)
RETURN NUMBER
IS
BEGIN
ctx := random_agg_type(NULL, 0);
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT random_agg_type, p_string IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
-- Adjust the count for the new element
v_count := v_count + 1;
-- Generate a random number 0 to N-1, if it's 0,
-- then the new value becomes the current selection
IF random_0n(v_count) = 0
THEN
v_string := p_string;
END IF;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(self IN OUT random_agg_type, ctx2 IN random_agg_type)
RETURN NUMBER
IS
BEGIN
-- Weight the selection of random value from the contexts by the relative sizes
-- For example:
-- self.v_count = 10
-- p_ctx2.v_count = 7
-- If random number from 0-16 < 7 then select the p_ctx2 value
-- otherwise keep the value currently in use
-- Note, this does have a 2^32-1 limit before there is an error
-- due to inability to prevent modulo bias
IF random_0n(v_count + ctx2.v_count) < ctx2.v_count
THEN
v_string := ctx2.v_string;
END IF;
v_count := v_count + ctx2.v_count;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(
self IN random_agg_type,
returnvalue OUT VARCHAR2,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := v_string;
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION random_value_agg(p_string VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING random_agg_type;
SELECT department_id, random_value_agg(salary)
FROM hr.employees
GROUP BY department_id;
CREATE OR REPLACE TYPE number_date_type AS OBJECT
(
value NUMBER,
correlated_time DATE
);
/
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
---- Correlated Time for Maximum Number Aggregate
-----------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE cortime_max_num_agg_type
AS OBJECT
(
v_temp NUMBER, -- What is the max?
v_time DATE, -- When did it happen?
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT cortime_max_num_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT cortime_max_num_agg_type, p_pair IN number_date_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(self IN OUT cortime_max_num_agg_type, ctx2 IN cortime_max_num_agg_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(self IN cortime_max_num_agg_type,
returnvalue OUT DATE,
flags IN NUMBER
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY cortime_max_num_agg_type
IS
STATIC FUNCTION odciaggregateinitialize(ctx IN OUT cortime_max_num_agg_type)
RETURN NUMBER
IS
BEGIN
ctx := cortime_max_num_agg_type(NULL, NULL); -- initialize max and time to NULL
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(
self IN OUT cortime_max_num_agg_type,
p_pair IN number_date_type
)
RETURN NUMBER
IS
BEGIN
-- The first pass through the value and correlated time are automatically
-- the max. After that, check if the new value is bigger than the old
-- if so, use the new pair. If the old and new are the same value, then
-- keep the time that is latest.
IF v_temp IS NULL
THEN
v_temp := p_pair.VALUE;
v_time := p_pair.correlated_time;
ELSIF v_temp < p_pair.VALUE
THEN
v_temp := p_pair.VALUE;
v_time := p_pair.correlated_time;
ELSIF v_temp = p_pair.VALUE
THEN
v_time := GREATEST(v_time, p_pair.correlated_time);
END IF;
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregatemerge(
self IN OUT cortime_max_num_agg_type,
ctx2 IN cortime_max_num_agg_type
)
RETURN NUMBER
IS
BEGIN
-- If the current value is null, use the other one
-- If the new set is greater than our current one, use the new one
-- If the new and current are the same, then take the latest time
IF v_temp IS NULL
THEN
v_temp := ctx2.v_temp;
v_time := ctx2.v_time;
ELSIF ctx2.v_temp > v_temp
THEN
v_temp := ctx2.v_temp;
v_time := ctx2.v_time;
ELSIF ctx2.v_temp = v_temp
THEN
v_time := GREATEST(v_time, ctx2.v_time);
END IF;
RETURN odciconst.success;
END odciaggregatemerge;
MEMBER FUNCTION odciaggregateterminate(
self IN cortime_max_num_agg_type,
returnvalue OUT DATE,
flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
returnvalue := self.v_time;
RETURN odciconst.success;
END odciaggregateterminate;
END;
/
CREATE OR REPLACE FUNCTION correlated_timeagg(p_pair number_date_type)
RETURN DATE
PARALLEL_ENABLE
AGGREGATE USING cortime_max_num_agg_type;
SELECT max(num_rows), correlated_timeagg(number_date_type(num_rows,last_analyzed)) FROM all_tables;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
i never knew this before...!!!
out.txt which has a log of all activities, similar to this, if we had a consolidated code(code.sql file), it would have been better..
Thank you sdstuber..!!!