(PL)SQL - Source Code Search (PL/SQL)

I created this package to search for multiple strings at once, in both PL/SQL sources and Apex applications.
It uses the user_sources and user_views views, as well as the Apex API to get an export of the entire application (split into objects).
A SQL-only version (which is simpler to use and faster, but doesn't search in Apex applications, yet), can be found on my Source Code Search (SQL) page.

Things to note:

The search is not case sensitive.
My first attempt was a real resource hog. An 18c XE database really struggled to give any results without a 500 Internal Server Error on this small VPS. This has been fixed however, it now only takes a few seconds.
2022-03-17: I have added a "0=1" to the where clause, because this page was also causing performance issues on my own server - mainly because some webcrawlers access this page way too much. So please remove the "0=1" to get results on your own system...
Allowing wildcards would be nice, but would require use of regular expressions, which would slow things down quite a bit.

If apex_string.split results in a line with more than 4000 characters, you will get an ORA-00910 "specified length too long for its datatype" exception.
Have a look at my Source Code Search (SQL) page for options to fix that.

Page

Identification
	Page ID	1135
	Name	(PL)SQL - Source Code Search (PL/SQL)

Region

Identification
	Sequence	30
	Title	Source Code Search
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	with iv_t as ( select t.* , ( case when t.owner = 'APEX' and instr(t.name,'/',-1) <> instr(t.name,'/') then substr( t.name, instr(t.name,'/') + 1, instr(t.name,'/',-1) - instr(t.name,'/') - 1 ) when t.owner = 'APEX' then substr( t.name, instr(t.name,'/') + 1 ) else t.name end ) as name2 , instr(t.name,'/') as i1 , instr(t.name,'/',-1) as i2 from table(ted_p1135.fn_code_search_p( i_search_items => 'materialized:EMPNO' , i_application_ids => 25384 , i_exclude_objects => 'TED_1029:TED_P1104:f25384/application/shared_components/logic/application_processes/set_page_item_pxxx_empno_from_lov.sql' , i_delimiter => ':') ) t ) , iv_t2 as ( select iv_t.* , dense_rank() over (partition by iv_t.type, iv_t.name2 order by iv_t.line) as n from iv_t ) select iv_t2.* from iv_t2 where iv_t2.n <= 1 -- limiting the results to only show some relevant examples here and 0 = 1 -- I have added this to the where clause, because this page was also causing performance issues on my own server. So please remove that to get results on your own system...
Appearance
Appearance	Template	Standard

Package

Package
	Name	TED_P1135
	Source	package ted_p1135 is type tr_result is record( owner varchar2(1000) ,type varchar2(1000) ,name varchar2(1000) ,line number ,search_item varchar2(4000) ,text varchar2(4000)); type tt_result is table of tr_result; function fn_code_search_p(i_search_items in varchar2 ,i_application_ids in varchar2 ,i_exclude_objects in varchar2 := null ,i_delimiter in varchar2 := ':') return tt_result pipelined; end ted_p1135;

Package Body

Package Body
	Name	TED_P1135
	Source	package body ted_p1135 is k_lf constant varchar2(2) := chr(10); function fn_get_text_line#(i_clob in clob ,i_offset in number ,i_search_item in varchar2) return varchar2 is begin return to_char( substr(i_clob, greatest(i_offset - 100 ,1), 200 + length(i_search_item)) ); end fn_get_text_line#; function fn_code_search_p(i_search_items in varchar2 ,i_application_ids in varchar2 ,i_exclude_objects in varchar2 := null ,i_delimiter in varchar2 := ':') return tt_result pipelined is r_result tr_result; l_files apex_t_export_files; l_instr pls_integer; l_applications_array apex_t_varchar2 := apex_string.split(p_str => i_application_ids ,p_sep => i_delimiter); l_search_items_array apex_t_varchar2 := apex_string.split(p_str => lower(i_search_items) ,p_sep => i_delimiter); l_exclude_objects_array apex_t_varchar2 := apex_string.split(p_str => upper(i_exclude_objects) ,p_sep => i_delimiter); e_20987 exception; pragma exception_init(e_20987, -20987); cursor c_app(cp_contents in clob) is select row_number() over(order by rownum) as line ,lower(t.column_value) as text from table(apex_string.split(cp_contents, chr(10))) t ; cursor c_sources is select s.name , s.type , s.line , lower(s.text) as text from user_source s where not s.name member of l_exclude_objects_array ; cursor c_views is -- user views as clob (text_length is a varchar2 column, so dbms_metadata.get_xml is used when the text_length > 4000) with iv_user_views_c as (select v.view_name ,(case when v.text_length <= 4000 then to_clob(v.text_vc) else dbms_xmlgen.convert(xmltype(dbms_metadata.get_xml('VIEW', v.view_name)).extract('//ROWSET/ROW/VIEW_T/TEXT/text()').getclobval() ,1) end) as text_clob from user_views v where not v.view_name member of l_exclude_objects_array ) select iv_user_views_c.view_name ,'VIEW' as type ,row_number() over(partition by iv_user_views_c.view_name order by rownum) as line ,lower(t.column_value) as text from iv_user_views_c cross join table(apex_string.split(iv_user_views_c.text_clob, chr(10))) t ; begin -- loop apps begin for r_applications in 1 .. l_applications_array.count loop l_files := apex_export.get_application(p_application_id => l_applications_array(r_applications) ,p_split => true); -- loop split app files for r_files in 1 .. l_files.count loop -- exclude object if needed if upper(l_files(r_files).name) not member of l_exclude_objects_array then -- loop search terms for r_search_items in 1 .. l_search_items_array.count loop -- search in the clob first - not really needed, but does make it faster l_instr := dbms_lob.instr(lob_loc => lower(l_files(r_files).contents) ,pattern => l_search_items_array(r_search_items) ); if l_instr <> 0 then -- loop lines in contents for r_app in c_app(cp_contents => l_files(r_files).contents) loop l_instr := instr(r_app.text ,l_search_items_array(r_search_items)); if l_instr <> 0 then r_result.owner := 'APEX'; r_result.type := 'EXPORT'; r_result.name := l_files(r_files).name; r_result.line := r_app.line; r_result.search_item := l_search_items_array(r_search_items); r_result.text := rtrim(r_app.text, k_lf); pipe row(r_result); end if; end loop; -- lines in contents end if; end loop; -- search terms end if; end loop; -- split app files end loop; -- apps exception when e_20987 then null; -- ignore errors for schemas that are not part of an Apex workspace end; -- loop sources for r_sources in c_sources loop -- loop search terms for r_search_items in 1 .. l_search_items_array.count loop l_instr := instr(r_sources.text ,l_search_items_array(r_search_items)); if l_instr <> 0 then r_result.owner := user; r_result.type := r_sources.type; r_result.name := r_sources.name; r_result.line := r_sources.line; r_result.search_item := l_search_items_array(r_search_items); r_result.text := rtrim(r_sources.text, k_lf); pipe row(r_result); end if; end loop; -- search terms end loop; -- sources -- loop views for r_views in c_views loop -- loop search terms for r_search_items in 1 .. l_search_items_array.count loop l_instr := instr(r_views.text ,l_search_items_array(r_search_items)); if l_instr <> 0 then r_result.owner := user; r_result.type := 'VIEW'; r_result.name := r_views.view_name; r_result.line := r_views.line; r_result.search_item := l_search_items_array(r_search_items); r_result.text := rtrim(r_views.text, k_lf); pipe row(r_result); end if; end loop; -- search terms end loop; -- views end fn_code_search_p; end ted_p1135;

Info

ORA-00910

Source Code Search

Column Actions

Code

Page

Region

Package

Package Body