Use the package below to find where Apex items are used in PL/SQL code.
Useful for finding items that have been renamed/removed from the Apex application, but are still referenced in PL/SQL code - in which case the value would be null, possibly resulting in all sorts of strange behaviour.


Usage example, to find all Apex items that are referenced in PL/SQL packages named like PCK% (specification or body), but that do not exist in Apex application ID 25384:
select i.* from table(ted_apex_items.user_source_items) i where i.name like 'PCK%' and i.type like 'PACKAGE%' and i.item_name not in ( select api.item_name from apex_application_items api where api.application_id = 25384 ) and i.item_name not in ( select app.item_name from apex_application_page_items app where app.application_id = 25384 )

Package

Package
	Name	TED_APEX_ITEMS
	Source	package ted_apex_items is type t_items_row is record ( name varchar2(30) , type varchar2(12) , line number , text varchar2(4000) , item_name varchar2(100) , item_position number ) ; type t_items_tab is table of t_items_row; function user_source_items return t_items_tab pipelined deterministic ; end ted_apex_items;

Package Body

Package Body
	Name	TED_APEX_ITEMS
	Source	package body ted_apex_items is function user_source_items return t_items_tab pipelined deterministic is cursor c1 is -- all user_source lines containing a v('%') construct select s.* from user_source s where regexp_count( s.text, 'v\( *''[^'']*'' *\)' ) > 0 -- ### NOTE: This regexp_count misses lines like: v('P' || trim(p_page_id) || '_ID'); -- ### You get those if you use this where clause: where regexp_like( s.text, 'v\( *''.*'' *\)' ) and not (s.name = $$PLSQL_UNIT and s.TYPE like 'PACKAGE%') -- skip the current package order by 1,2,3 ; v_item_position_0 pls_integer; v_item_position pls_integer; v_item_name varchar2(1000); v_items_row t_items_row; v_is_ok boolean; begin for c1_rec in c1 loop v_items_row := null; v_items_row.name := c1_rec.name; v_items_row.type := c1_rec.type; v_items_row.line := c1_rec.line; v_items_row.text := c1_rec.text; v_item_position_0 := 1; -- loop all v('%') constructs loop -- get the next v('%') construct position v_item_position := regexp_instr ( c1_rec.text, 'v\( *''[^'']*'' *\)', v_item_position_0 ); -- exit loop if no v('%') construct is found if v_item_position = 0 then exit; end if; -- get the v('%') construct string v_item_name := regexp_substr( c1_rec.text, 'v\( *''[^'']*'' *\)', v_item_position ); -- clean up v_item_name v_item_name := upper( replace( regexp_substr( v_item_name, '''[^'']*''' ), '''', '' ) ); -- Determine v_is_ok v_is_ok := true; -- Global variables from APEX_APPLICATION package (Apex version 4.2) if v_item_name in ( 'G_USER' , 'G_FLOW_ID' , 'G_FLOW_STEP_ID' , 'G_FLOW_OWNER' , 'G_REQUEST' , 'G_BROWSER_LANGUAGE' , 'G_DEBUG' , 'G_HOME_LINK' , 'G_LOGIN_URL' , 'G_IMAGE_PREFIX' , 'G_FLOW_SCHEMA_OWNER' , 'G_PRINTER_FRIENDLY' , 'G_PROXY_SERVER' , 'G_SYSDATE' , 'G_PUBLIC_USER' , 'G_GLOBAL_NOTIFICATION' ) then v_is_ok := false; end if; -- Built-in Substitution Strings (Apex version 4.2) if v_item_name in ( 'APEX$ROW_NUM' , 'APEX$ROW_SELECTOR' , 'APEX$ROW_STATUS' , 'APP_ID' , 'APP_ALIAS' , 'APP_DATE_TIME_FORMAT' , 'APP_IMAGES' , 'APP_NLS_DATE_FORMAT' , 'APP_NLS_TIMESTAMP_FORMAT' , 'APP_NLS_TIMESTAMP_TZ_FORMAT' , 'APP_IMAGES' , 'APP_PAGE_ID' , 'APP_SESSION' , 'SESSION' -- is an alias of APP_SESSION , 'APP_SESSION_VISIBLE' , 'APP_UNIQUE_PAGE_ID' , 'APP_USER' , 'AUTHENTICATED_URL_PREFIX' , 'BROWSER_LANGUAGE' , 'CURRENT_PARENT_TAB_TEXT' , 'DEBUG' , 'HOME_LINK' , 'IMAGE_PREFIX' , 'LOGIN_URL' , 'LOGOUT_URL' , 'PRINTER_FRIENDLY' , 'PROXY_SERVER' , 'PUBLIC_URL_PREFIX' , 'REQUEST' , 'SCHEMA OWNER' , 'SQLERRM' , 'SYSDATE_YYYYMMDD' , 'WORKSPACE_IMAGES' ) then v_is_ok := false; end if; -- Other prefixes that look like "v" if substr( c1_rec.text, v_item_position-length('owa_util.get_cgi_env')+1, length('owa_util.get_cgi_env') ) = 'owa_util.get_cgi_env' then v_is_ok := false; end if; -- all is ok if v_is_ok = true then -- get the "exact" v_item_position v_item_position := regexp_instr ( c1_rec.text, v_item_name, v_item_position, 1, 0, 'i' ); -- fill and pipe v_items_row v_items_row.item_name := v_item_name; v_items_row.item_position := v_item_position; pipe row(v_items_row); end if; -- set the search position for the next iteration of the loop to the current position + 1 v_item_position_0 := v_item_position + 1; end loop; end loop; end; end ted_apex_items;