?
Solved

Detecting all distinct characters used in all records of a column present in an Oracle table

Posted on 2008-11-05
3
Medium Priority
?
603 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:hc2342uhxx3vw36x96hq
3 Comments
 
LVL 14

Accepted Solution

by:
ajexpert earned 2000 total points
ID: 22888503
Hi,
This is not the best possible solution, but the simplest one
You can create a table in memory or a tempoary table with one column storing character

Now you can store the distinct values in the temporary table created.  Once the temporay table is populated return the rows of temporary table using ref cursor.

Please view the sample code.
--CREATE TABLE IN MEMORY OR TEMPORARY TABLE...(I have created physical ---table for simplicity)
CREATE TABLE TEMPS
( COL1 VARCHAR2(1) );
 
-- CODE AS PROCEDURE
 create or replace procedure distchar
   as
  CURSOR C1 IS
  SELECT dname from dept;
  vname VARCHAR2(2000);
  vlen NUMBER;
  vchar VARCHAR2(1);
  vcount NUMBER(1);
  begin
  FOR c1_rec in C1
  LOOP
  vname := C1_REC.DNAME;
  vlen := LENGTH(vname);
  for i in 1..vlen
  LOOP
  vchar  := (SUBSTR(VNAME,I,1));
  vcount:=0;
  SELECT count(1) into vcount from temps WHERE charcol = vchar; 
  IF vcount = 0 THEN
    INSERT INTO temps VALUES (vchar);
    COMMIT;
  END IF;
  END LOOP;
  END LOOP;
  END;
/

Open in new window

0
 
LVL 1

Author Closing Comment

by:hc2342uhxx3vw36x96hq
ID: 31513614
OK ;-)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 23083392
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),count(*) from dba_objects

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;
/

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question