Created
August 20, 2018 07:09
-
-
Save peter-genesys/d203bfb3d88d5a5664a86ea6ee34eeca to your computer and use it in GitHub Desktop.
CLOBlist - CLOB list aggregation function for SQL. Similar to LISTAGG, but simpler syntax, and can suppress duplicates.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
------------------------------------------------------------------ | |
-- 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
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