How to search within Long data type column in Oracle (this is one way of doing it)

select at.trigger_name, (at.trigger_body) trig_body, at.*
from all_triggers at
where (trigger_name like 'XX%'
OR table_name like 'XX%')

create xx_triggers
as
select at.trigger_name, to_lob(at.trigger_body) trig_body
from all_triggers at
where (trigger_name like 'XX%'
OR table_name like 'XX%');

select xt.trigger_name,  upper(xt.trig_body)--, at.*
from xx_triggers xt
where upper(xt.trig_body) like '%SEGMENT1%';

Patch history tables in Oracle Apps

AD_APPL_TOPS - Stores information about all APPL_TOPS utilizing this instance.
AD_APPLIED_PATCHES - Stores information about all distinct updates applied.
AD_BUGS - Stores information about all distinct bug fixes applied.
AD_DRIVERS - Stores information about the drivers that comprise an update.
AD_FILE_VERSIONS - Stores version information for files.
AD_FILES - Stores information about files in the system.
AD_PATCH_COMMON_ACTIONS - Stores information about actions in update driver files.
AD_PATCH_DRIVER_LANGS - Stores information about the languages included in an update driver file.
AD_PATCH_DRIVER_MINIPKS - Stores information about minipacks.
AD_PATCH_RUN_BUG_ACTIONS - Stores detailed information about actions applied by an update.
AD_PATCH_RUN_BUGS - Stores information about all bug fixes included in an update , applied or not.
AD_PATCH_RUNS - Stores information about all incvocations of AutoPatch for all of the various updates applied.
AD_RELEASES - Stores release information about the Oracle Applications system.

References:
http://appsoracle.blogspot.ca/2012/03/patch-history-tables-in-oracle-apps.html

Oracle Application URL from the Apps database

SELECT profile_option_value
  FROM apps.fnd_profile_option_values
 WHERE profile_option_id =
                         (SELECT profile_option_id
                            FROM apps.fnd_profile_options
                           WHERE profile_option_name = 'APPS_FRAMEWORK_AGENT')
   AND level_value = 0