Table Value Frequency Stored Procedure

Below stored procedure is based on finding out the number of occurence for same value for specific columns. This SP is parameterized so that you can feed in the table name and column name and occurence of the value also.

Target table structure:-

CREATE TABLE APPS.VALUE_FREQN_TABLE
   (
 COLUMN_NAME VARCHAR2(100),  COL_VAL VARCHAR2(1000),
 COL_VAL_FREQN NUMBER(15,0),   COL_VAL_FREQN_PERCEN NUMBER(15,0)
   )


Stored Procedure
CREATE OR REPLACE PROCEDURE sp_table_value_freqn(

my_table
VARCHAR2, my_col
VARCHAR2,Occur
Number ) IS
TYPE cur_typ IS REF CURSOR;c cur_typ;
query_str
VARCHAR2(200);col_value
varchar2(200);col_count
VARCHAR2(20);inv_cust
VARCHAR2(20);inv_amt
NUMBER;BEGINquery_str := ' select ' || my_col || ' , count( ' || my_col || ' ) from ' || my_table || ' group by ' || my_col ||
' Having count(*) > ' || Occur;

OPEN c FOR query_str ;
LOOPFETCH c INTO col_value, col_count;
EXIT WHEN c%NOTFOUND;
INSERT INTO VALUE_FREQN_TABLE (Column_name,
Col_Val,
Col_val_freqn,
Col_val_freqn_Percen
)


Values(my_col,
col_value,
col_count,
0);COMMIT;

END LOOP;
CLOSE c;END;
Please let us know for any suggestion or change....

Happy Coding... :):)