CLOBlist - CLOB list aggregation function for SQL. Similar to LISTAGG, but simpler syntax, and can suppress duplicates.
------------------------------------------------------------------ | |
-- Program : CLOBlist | |
-- Name : CLOB list | |
-- Author : Peter Burgess | |
-- Purpose : CLOB list aggregation function for SQL | |
-- RETURNS CLOB - to allow for more than 4000 chars to be returned by SQL | |
-- NEW type CLOBlistParam - allows for definition of the delimiter, and initiator of sequence | |
------------------------------------------------------------------ | |
--This is an aggregating function for use in SQL. | |
--It takes the argument and creates a comma delimited list of each instance. | |
WHENEVER SQLERROR CONTINUE | |
DROP TYPE CLOBlistImpl; | |
WHENEVER SQLERROR EXIT FAILURE ROLLBACK | |
create or replace type CLOBlistParam as object( | |
string VARCHAR2(4000) | |
,delimiter VARCHAR2(100) | |
,initiator VARCHAR2(100) | |
,no_dup VARCHAR2(1) ) | |
/ | |
show error | |
--Creating CLOBlist() | |
--Implement the type CLOBlistImpl to contain the ODCIAggregate routines. | |
create or replace type CLOBlistImpl as object | |
( | |
g_list CLOB, -- progressive concatenation | |
static function ODCIAggregateInitialize(sctx IN OUT CLOBlistImpl) | |
return number, | |
member function ODCIAggregateIterate(self IN OUT CLOBlistImpl | |
, value IN CLOBlistParam) return number, | |
member function ODCIAggregateTerminate(self IN CLOBlistImpl | |
, returnValue OUT CLOB | |
, flags IN number) return number, | |
member function ODCIAggregateMerge(self IN OUT CLOBlistImpl | |
, ctx2 IN CLOBlistImpl) return number | |
) | |
/ | |
show error | |
--Implement the type body for CLOBlistImpl. | |
create or replace type body CLOBlistImpl is | |
static function ODCIAggregateInitialize(sctx IN OUT CLOBlistImpl) | |
return number is | |
begin | |
sctx := CLOBlistImpl(TO_CHAR(NULL)); | |
return ODCIConst.Success; | |
end; | |
member function ODCIAggregateIterate(self IN OUT CLOBlistImpl | |
, value IN CLOBlistParam) return number is | |
begin | |
IF self.g_list IS NULL THEN | |
self.g_list := value.initiator||value.string; | |
ELSIF value.no_dup = 'Y' AND | |
value.delimiter||self.g_list||value.delimiter LIKE '%'||value.delimiter||value.string||value.delimiter||'%' | |
THEN | |
--Do not include duplicate value | |
NULL; | |
ELSE | |
self.g_list := self.g_list||value.delimiter||value.string; | |
END IF; | |
return ODCIConst.Success; | |
end; | |
member function ODCIAggregateTerminate(self IN CLOBlistImpl | |
, returnValue OUT CLOB | |
, flags IN number) return number is | |
begin | |
returnValue := self.g_list; | |
return ODCIConst.Success; | |
end; | |
member function ODCIAggregateMerge(self IN OUT CLOBlistImpl | |
, ctx2 IN CLOBlistImpl) return number is | |
begin | |
self.g_list := LTRIM( self.g_list||','||ctx2.g_list,','); | |
return ODCIConst.Success; | |
end; | |
end; | |
/ | |
show error | |
--Using CLOBlist() to create a vertical list of comma separated values | |
-- SELECT CLOBlist(CLOBlistParam(product_code,chr(10)||',','','Y')) | |
-- FROM account | |
--DROP FUNCTION CLOBlist | |
--/ | |
PROMPT Create the user-defined aggregate. | |
CREATE OR REPLACE FUNCTION CLOBlist (input CLOBlistParam) RETURN CLOB | |
PARALLEL_ENABLE AGGREGATE USING CLOBlistImpl; | |
/ | |
show error | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Explanation
CLOBlist takes contructor CLOBlistParam as a parameter.
CLOBlistParam has 4 arguments
Example usage
--vertical list of comma separated values, no duplicates.
SELECT CLOBlist(CLOBlistParam(column_name,chr(10)||',','','Y')) FROM user_tab_columns
--simple csv
SELECT CLOBlist(CLOBlistParam(table_name,',','','N')) FROM user_tables