Hi Experts,
I have this scenario:
I have the below table and I need to find out the characters in the attributes_value and attributes_value_clob columns whose ASCII value is greater than 127.
Required Data setup:
CREATE TABLE attributes_table
(
attribute_id NUMBER,
attribute_value VARCHAR2(4000),
attribute_value_clob CLOB
);
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO attributes_table
VALUES (i,'Just º inserting ¬ for fun',dbms_random.String('x', 10000)||'¾ Just ± inserting µ for fun ¿'||dbms_random.String('x', 10000));
END LOOP;
COMMIT;
END;
My attempt:
CREATE OR replace FUNCTION Fn_find_ascii_gt_127 RETURN VARCHAR2
AS
l_ascii_value NUMBER;
l_list_return VARCHAR2(4000);
TYPE type_list_ascii_values IS TABLE OF NUMBER;
l_list_ascii_values TYPE_LIST_ASCII_VALUES := Type_list_ascii_values();
CURSOR c1
IS
SELECT attribute_value,
attribute_value_clob
FROM attributes_table;
BEGIN
FOR rec IN c1
LOOP
IF rec.attribute_value IS NULL AND rec.attribute_value_clob IS NULL THEN
CONTINUE;
END IF;
IF rec.attribute_value IS NOT NULL THEN
FOR i IN 1..Length(rec.attribute_value)
LOOP
l_ascii_value := Ascii(Substr(rec.attribute_value,i,1));
IF l_ascii_value > 127 THEN
IF l_ascii_value NOT member OF l_list_ascii_values THEN
l_list_ascii_values.extend;
L_list_ascii_values(l_list_ascii_values.last) := l_ascii_value;
END IF;
END IF;
END LOOP;
END IF;
IF rec.attribute_value_clob IS NOT NULL THEN
FOR i IN 1..Length(rec.attribute_value_clob)
LOOP
l_ascii_value := Ascii(Substr(rec.attribute_value_clob,i,1));
IF l_ascii_value > 127 THEN
IF l_ascii_value NOT member OF l_list_ascii_values THEN
l_list_ascii_values.extend;
L_list_ascii_values(l_list_ascii_values.last) := l_ascii_value;
END IF;
END IF;
END LOOP;
END IF;
END LOOP;
IF l_list_ascii_values IS NOT NULL AND l_list_ascii_values.count > 0 THEN
FOR i IN l_list_ascii_values.first..l_list_ascii_values.count
LOOP
l_list_return := l_list_return||L_list_ascii_values(i)||',';
END LOOP;
END IF;
RETURN l_list_return;
END;
Even though it is working, it is taking a lot of time. Can anyone suggest some better/efficient way of doing it ?
Regards,
Ranagal