Detecting all distinct characters used in all records of a column present in an Oracle table
Please, could you help me to write a stored function that, acting on a COLUMN, will give in output the different characters contained in every record with reference to that column.
In example:
If you have the table PIPPO containing the column ALPHA with these values:
ABCDE
FGHIJ
KLMNOABCDE
FGHIJ44433
1234567
... launching the SELECT:
SELECT MAGIC_FUNCTION (ALPHA) from PIPPO;
... will produce:
1234567ABCDEFGHIJKLMNO
In example:
If you have the table PIPPO containing the column ALPHA with these values:
ABCDE
FGHIJ
KLMNOABCDE
FGHIJ44433
1234567
... launching the SELECT:
SELECT MAGIC_FUNCTION (ALPHA) from PIPPO;
... will produce:
1234567ABCDEFGHIJKLMNO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I know this question is long over, but it looked like an interesting question, so I had to take a crack at it. :)
Here's my approach. I created an aggregate function called "distinctagg" it works just like other aggregates and is pretty quick.
This took about 2 seconds on my laptop with 74302 objects in dba_objects
select distinctagg(object_name),c
The sorting and distinct search I used is basically just a simple insertion sort with duplicates removed.
Here's my approach. I created an aggregate function called "distinctagg" it works just like other aggregates and is pretty quick.
This took about 2 seconds on my laptop with 74302 objects in dba_objects
select distinctagg(object_name),c
The sorting and distinct search I used is basically just a simple insertion sort with duplicates removed.
CREATE OR REPLACE TYPE distinct_chars_type
AS
OBJECT(
v_result VARCHAR2(4000),
MEMBER FUNCTION distinct_chars(p_unique IN VARCHAR2, p_new IN VARCHAR2)
RETURN VARCHAR2
DETERMINISTIC,
STATIC FUNCTION odciaggregateinitialize(p_sctx IN OUT distinct_chars_type)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateiterate(self IN OUT distinct_chars_type, p_value IN VARCHAR2)
RETURN NUMBER,
MEMBER FUNCTION odciaggregateterminate(
self IN distinct_chars_type,
p_returnvalue OUT VARCHAR2,
p_flags IN NUMBER
)
RETURN NUMBER,
MEMBER FUNCTION odciaggregatemerge(
self IN OUT distinct_chars_type,
p_ctx2 IN distinct_chars_type
)
RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY distinct_chars_type
IS
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;
FOR i IN 1 .. NVL(LENGTH(p_new), 0)
LOOP
v_char := SUBSTR(p_new, i, 1);
IF NVL(INSTR(v_temp, v_char), 0) = 0
THEN
v_index := 1;
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_sctx IN OUT distinct_chars_type)
RETURN NUMBER
IS
BEGIN
p_sctx := distinct_chars_type(NULL);
RETURN odciconst.success;
END odciaggregateinitialize;
MEMBER FUNCTION odciaggregateiterate(self IN OUT distinct_chars_type, p_value IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
self.v_result := distinct_chars(self.v_result, p_value);
RETURN odciconst.success;
END odciaggregateiterate;
MEMBER FUNCTION odciaggregateterminate(
self IN distinct_chars_type,
p_returnvalue OUT VARCHAR2,
p_flags IN NUMBER
)
RETURN NUMBER
IS
BEGIN
p_returnvalue := self.v_result;
RETURN odciconst.success;
END odciaggregateterminate;
MEMBER FUNCTION odciaggregatemerge(
self IN OUT distinct_chars_type,
p_ctx2 IN distinct_chars_type
)
RETURN NUMBER
IS
BEGIN
self.v_result := distinct_chars(self.v_result, p_ctx2.v_result);
RETURN odciconst.success;
END odciaggregatemerge;
END;
/
CREATE OR REPLACE FUNCTION distinctagg(p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE
AGGREGATE USING distinct_chars_type;
/
ASKER