Pattern Matching SP

Below Stored procedure is to find out the various patterns in the values of some specific columns.

Table  Structure :-

create table VALUE_FREQN_PATRN (col_val_pattern varchar2(1000), col_val varchar2(1000));

Stored Procedure :-

CREATE OR REPLACE PROCEDURE sp_table_value_pattern(my_table
VARCHAR2, my_col
VARCHAR2 ) IS
TYPE cur_typ IS REF CURSOR;c cur_typ;
query_str
VARCHAR2(1000);col_value
varchar2(1000);col_count
VARCHAR2(20);val_len
number(10);counter
number(10);pattern_value
varchar2(1000);BEGINquery_str := ' select ' || my_col || ' from ' || my_table ;

OPEN c FOR query_str ;
LOOPFETCH c INTO col_value;
EXIT WHEN c%NOTFOUND;
IF(col_value is NULL)
THEN val_len := 0;
ELSE IF (col_value is NOT NULL)
THEN val_len := length(col_value);
END IF;
END IF;
counter :=
1 ;pattern_value :=
'';-----Loop for each charater in value ---------------FOR counter in 1 .. val_len LOOPIF(SUBSTR(col_value,counter,1)= ' ')
Then pattern_value := pattern_value || ' ';

ELSE IF(LENGTH(TRIM(TRANSLATE(SUBSTR(col_value,counter,1),'0123456789',' '))) is NULL)
Then pattern_value := pattern_value || '9';
ELSE IF(LENGTH(TRIM(TRANSLATE(SUBSTR(col_value,counter,1), 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
,' '))) is NULL)
Then pattern_value := pattern_value || 'X';

ELSE IF(LENGTH(TRIM(TRANSLATE(SUBSTR(col_value,counter,1), 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
,' '))) is NOT NULL)
Then pattern_value := pattern_value || SUBSTR(col_value,counter,1);


END IF;
END IF;
END IF;
END IF;

END LOOP;
-----END Loop for each charater in value ---------------
INSERT INTO VALUE_FREQN_PATRN (col_val_pattern,
col_val
)


Values(pattern_value,
col_value
);COMMIT;

END LOOP;
CLOSE c;END;


@Readers :- Let me know for any more scenarios, if i can include in this SP.


Happy Coding...... :)
Rohit