Rem Copyright (c) Oracle Corporation 1999 - 2006. All Rights Reserved. Rem Rem NAME Rem apexvalidate.sql Rem Rem DESCRIPTION Rem This procedure checks that the objects in the APEX application schema are valid. Rem Rem NOTES Rem Assumes the SYS user is connected. Rem Rem REQUIRENTS Rem - Oracle 10g Rem Rem MODIFIED (MM/DD/YYYY) Rem jstraub 06/21/2006 - Created, borrowed almost exclusively from CTXSYS, thanks gkaminag Rem jstraub 06/22/2006 - Broke out validate procedure into a package Rem jstraub 06/29/2006 - Turned back into standalone procedure on advice from rburns Rem jstraub 01/30/2007 - Excluded wwv_flow_custom_auth_sso (Bug 5852920) Rem jstraub 01/31/2007 - Changed query on all objects to improve performance Rem jstraub 07/13/2007 - Removed WWV_FLOW_DATA_LOAD and WWV_FLOW_XLIFF from existance check to support runtime Rem jstraub 02/01/2008 - Removed check for WWV_EXECUTE_IMMEDIATE and added check for WWV_DBMS_SQL Rem sspadafo 10/23/2008 - Added wwv_flow_assert calls (Bug 7426240) Rem sspadafo 10/23/2008 - Prefixed wwv_flow_assert with caret-3 (Bug 7426240) Rem sspadafo 10/23/2008 - Undo previous two changes; added local function simple_sql_name and use as alternative to assert (Bug 7426240) create or replace procedure validate_apex as type obj_arr is table of varchar2(30) index by binary_integer; g_objects obj_arr; l_type# binary_integer; l_owner# binary_integer; l_ltype varchar2(30) := 'FIRST'; l_status binary_integer; l_compile_sql varchar2(2000); l_obj_found boolean; INVALID_OBJECT_NAME EXCEPTION; PRAGMA EXCEPTION_INIT(INVALID_OBJECT_NAME, -44002); function simple_sql_name( p_name in varchar2) return varchar2 is l_name varchar2(256); begin if substr(p_name,1,1) = '"' and substr(p_name,length(p_name),1) = '"' then l_name := substr(p_name,2,length(p_name)-2); l_name := replace(l_name,'""','"'); if length(l_name) = 0 or length(l_name) > 30 then raise invalid_object_name; end if; return p_name; else l_name := p_name; end if; if length(l_name) = 0 or length(l_name) > 30 then raise invalid_object_name; end if; if instr('abcdefghijklmnopqrstuvwxyz',substr(lower(l_name),1,1)) = 0 then raise invalid_object_name; end if; for i in 2..length(l_name) loop if instr('abcdefghijklmnopqrstuvwxyz0123456789_$#',substr(lower(l_name),i,1)) = 0 then raise invalid_object_name; end if; end loop; return p_name; end simple_sql_name; begin select user# into l_owner# from sys.user$ where name = '^3'; for c1 in (select object_name, object_type from all_objects where owner = '^3' order by object_type, object_name) loop if (c1.object_type != l_ltype) then --jstraub added TRIGGER select decode(c1.object_type, 'INDEX', 1, 'TABLE', 2, 'VIEW', 4, 'SEQUENCE', 6, 'PROCEDURE', 7, 'FUNCTION', 8, 'PACKAGE', 9, 'PACKAGE BODY', 11, 'TRIGGER', 12, 'TYPE', 13, 'TYPE BODY', 14, 'LIBRARY', 22, 'INDEXTYPE', 32, 'OPERATOR', 33, 0) into l_type# from dual; l_ltype := c1.object_type; end if; l_status := -1; for c2 in (select status from sys.obj$ where owner# = l_owner# and name = c1.object_name and type# = l_type#) loop l_status := c2.status; end loop; if (l_status != 1) then l_compile_sql := case c1.object_type when 'VIEW' then 'alter view ^3..' || simple_sql_name(c1.object_name) || ' compile' when 'PROCEDURE' then 'alter procedure ^3..' || simple_sql_name(c1.object_name) || ' compile' when 'FUNCTION' then 'alter function ^3..' || simple_sql_name(c1.object_name) || ' compile' when 'PACKAGE' then 'alter package ^3..' || simple_sql_name(c1.object_name) || ' compile' when 'PACKAGE BODY' then 'alter package ^3..' || simple_sql_name(c1.object_name) || ' compile body' when 'TYPE' then 'alter type ^3..' || simple_sql_name(c1.object_name) || ' compile' when 'TYPE BODY' then 'alter type ^3..' || simple_sql_name(c1.object_name) || ' compile body' when 'INDEXTYPE' then 'alter indextype ^3..' || simple_sql_name(c1.object_name) || ' compile' when 'OPERATOR' then 'alter operator ^3..' || simple_sql_name(c1.object_name) || ' compile' when 'TRIGGER' then 'alter trigger ^3..' || simple_sql_name(c1.object_name) || ' compile' else null end; if l_compile_sql is not null and instr(l_compile_sql,'WWV_FLOW_CUSTOM_AUTH_SSO') = 0 then begin execute immediate l_compile_sql; exception when others then dbms_output.put_line( 'FAILED CHECK FOR '||c1.object_type||' '||c1.object_name); dbms_registry.invalid('APEX'); goto endfunc; end; end if; end if; end loop; ---populate g_objects g_objects(g_objects.count+1) := 'WWV_FLOW_COLLECTIONS$'; g_objects(g_objects.count+1) := 'WWV_FLOW_COMPANIES'; g_objects(g_objects.count+1) := 'WWV_FLOW_FND_USER'; g_objects(g_objects.count+1) := 'WWV_FLOW_ITEMS'; g_objects(g_objects.count+1) := 'WWV_FLOW_LISTS'; g_objects(g_objects.count+1) := 'WWV_FLOW_MAIL_QUEUE'; g_objects(g_objects.count+1) := 'WWV_FLOW_MESSAGES$'; g_objects(g_objects.count+1) := 'WWV_FLOW_PAGE_PLUGS'; g_objects(g_objects.count+1) := 'WWV_FLOW_STEP_ITEMS'; g_objects(g_objects.count+1) := 'WWV_FLOW_STEP_PROCESSING'; g_objects(g_objects.count+1) := 'WWV_FLOW_STEP_VALIDATIONS'; g_objects(g_objects.count+1) := 'WWV_FLOW_STEPS'; g_objects(g_objects.count+1) := 'WWV_FLOW_SW_STMTS'; g_objects(g_objects.count+1) := 'WWV_FLOWS'; g_objects(g_objects.count+1) := 'WWV_FLOW_DML'; g_objects(g_objects.count+1) := 'WWV_FLOW_ITEM'; g_objects(g_objects.count+1) := 'WWV_FLOW_LANG'; g_objects(g_objects.count+1) := 'WWV_FLOW_LOG'; g_objects(g_objects.count+1) := 'WWV_FLOW_MAIL'; g_objects(g_objects.count+1) := 'WWV_FLOW_SVG'; g_objects(g_objects.count+1) := 'WWV_FLOW_SW_PARSER'; g_objects(g_objects.count+1) := 'WWV_FLOW_SW_UTIL'; g_objects(g_objects.count+1) := 'WWV_FLOW_UTILITIES'; g_objects(g_objects.count+1) := 'F'; g_objects(g_objects.count+1) := 'P'; g_objects(g_objects.count+1) := 'Z'; g_objects(g_objects.count+1) := 'V'; ---Check for existence of core objects for j in 1.. g_objects.count loop l_obj_found := false; for c1 in (select null from all_objects where owner = '^3' and object_name = g_objects(j) ) loop l_obj_found := true; end loop; if not l_obj_found then dbms_output.put_line('FAILED EXISTENCE CHECK FOR '||g_objects(j)); dbms_registry.invalid('APEX'); goto endfunc; end if; end loop; l_obj_found := false; for c1 in (select null from all_objects where owner = 'SYS' and object_name = 'WWV_DBMS_SQL' ) loop l_obj_found := true; end loop; if not l_obj_found then dbms_output.put_line('FAILED EXISTENCE CHECK FOR WWV_DBMS_SQL'); dbms_registry.invalid('APEX'); goto endfunc; end if; dbms_registry.valid('APEX'); <> null; exception when others then dbms_registry.invalid('APEX'); end validate_apex; / show errors