Hi there now day i have a little problem with my documentations.
i rarely noted my work and then when the case came my head is exploding.
before it came too late i wanna note this my experiences about Convert comma separated string to array in pl sql or vice versa.
First Case
I have simple query A,B,C,D and i would like to return the results in one row, like this
XXX
------------
A
B
C
D
f_convert
/* Create the output TYPE, here using a VARCHAR2(100) nested table type */SQL> CREATE TYPE test_type AS TABLE OF VARCHAR2(100);
/
Type created.
/* Now, create the function.*/
SQL> CREATE OR REPLACE FUNCTION f_convert(p_list IN VARCHAR2)
RETURN test_type
AS
l_string VARCHAR2(32767) := p_list || ',';
l_comma_index PLS_INTEGER;
l_index PLS_INTEGER := 1;
l_tab test_type := test_type();
BEGIN
LOOP
l_comma_index := INSTR(l_string, ',', l_index);
EXIT WHEN l_comma_index = 0;
l_tab.EXTEND;
l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
l_index := l_comma_index + 1;
END LOOP;
RETURN l_tab;
END f_convert;
/
Function created.
/* Prove it works */
SQL> SELECT * FROM TABLE(f_convert('A,B,C,D'));
COLUMN_VALUE
--------------------------------------------------------------------------------
A
B
C
D
Second Case
i haveXXX
------------
A
B
C
D
and i would like to return the result like A,B,C,D
String agg
1.
CREATE OR REPLACE TYPE t_string_agg AS OBJECT(
g_string VARCHAR2(32767),
STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER
);
/
SHOW ERRORS
2.
CREATE OR REPLACE TYPE BODY t_string_agg ISSTATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT t_string_agg)
RETURN NUMBER IS
BEGIN
sctx := t_string_agg(NULL);
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateIterate(self IN OUT t_string_agg,
value IN VARCHAR2 )
RETURN NUMBER IS
BEGIN
SELF.g_string := self.g_string || ',' || value;
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateTerminate(self IN t_string_agg,
returnValue OUT VARCHAR2,
flags IN NUMBER)
RETURN NUMBER IS
BEGIN
returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
RETURN ODCIConst.Success;
END;
MEMBER FUNCTION ODCIAggregateMerge(self IN OUT t_string_agg,
ctx2 IN t_string_agg)
RETURN NUMBER IS
BEGIN
SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
RETURN ODCIConst.Success;
END;
END;
/
SHOW ERRORS
3.
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS
How to use
select string_AGG(X) from xxx
Done!
source :Comma to array
#http://stackoverflow.com/questions/3819375/convert-comma-separated-string-to-array-in-pl-sql
#
http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle
String_agg (arry to comma)
#https://oracle-base.com/articles/misc/string-aggregation-techniques
