FIND TABLE NAME and COLUMN NAME based on RECORD/DATA

FIND TABLE NAME and COLUMN NAME based on RECORD/DATA



var val varchar2(10);
exec :val := 'RETURNED'

SELECT owner, table_name, substr (:val, 1, 11) "Searchword", SUBSTR(t.column_value.getstringval (),1, 50) "Column/Value" from all_tab_columns,
table(xmlsequence(dbms_xmlgen.getxmltype ('select ' || column_name || ' from ' ||owner||'.'||table_name|| ' where upper('|| column_name|| ') like upper(''%' || :val || '%'')'
 ).extract ('ROWSET/ROW/*'))) t where owner NOT IN ('USER_NAME/SCHEMA NAME') ;



SELECT owner, table_name, substr (:val, 1, 11) "Searchword", SUBSTR(t.column_value.getstringval (),1, 50) "Column/Value" from all_tab_columns,
table(xmlsequence(dbms_xmlgen.getxmltype ('select ' || column_name || ' from ' ||owner||'.'||table_name|| ' where upper('|| column_name|| ') like upper(''%' || :val || '%'')'
 ).extract ('ROWSET/ROW/*'))) t where owner='SCHEMA NAME' and table_name like 'TABLE_NAME' ;