CREATE OR REPLACE PROCEDURE Find_Column_Name_Prc ( ip_data_to_find_v IN VARCHAR2, -- If the input is a Date the format should 'YYYYMMDD' for ex. 23/08/2006 should entered as 20060823 op_column_name_v OUT VARCHAR2 -- output format will be tablename.columnname ) AS l_count NUMBER(10); l_data_to_find_v VARCHAR2(100); l_data_to_find_n NUMBER(15); TYPE col IS REF CURSOR; c col; BEGIN l_data_to_find_v := '''%'||ip_data_to_find_v||'%'''; FOR tables_cr IN ( SELECT TABLE_NAME FROM USER_TABLES ) LOOP FOR columns_cr IN ( SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = tables_cr.TABLE_NAME ORDER BY COLUMN_ID ) LOOP IF columns_cr.DATA_TYPE = 'NUMBER' OR columns_cr.DATA_TYPE = 'LONG' THEN BEGIN l_data_to_find_n := TO_NUMBER(ip_data_to_find_v); OPEN c FOR 'SELECT COUNT(*) FROM ' || tables_cr.TABLE_NAME || ' WHERE '|| columns_cr.COLUMN_NAME || ' = '|| l_data_to_find_n; EXCEPTION WHEN OTHERS THEN NULL; END; ELSIF columns_cr.DATA_TYPE = 'DATE' THEN OPEN c FOR 'SELECT COUNT(*) FROM ' || tables_cr.TABLE_NAME || ' WHERE to_char(TRUNC('|| columns_cr.COLUMN_NAME || '),''YYYYMMDD'') LIKE '|| l_data_to_find_v; ELSE BEGIN OPEN c FOR 'SELECT COUNT(*) FROM ' || tables_cr.TABLE_NAME || ' WHERE '|| columns_cr.COLUMN_NAME || ' LIKE '|| l_data_to_find_v; EXCEPTION WHEN OTHERS THEN CLOSE c; END; END IF; IF c%ISOPEN THEN FETCH c INTO l_count; IF l_count > 0 THEN op_column_name_v := op_column_name_v ||tables_cr.table_NAME || '.'|| columns_cr.COLUMN_NAME || ', '; END IF; CLOSE c; END IF; END LOOP; END LOOP; op_column_name_v := RTRIM(op_column_name_v,', '); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ( 'sqlerrm= ' || SQLERRM ); END; /