Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\apex\apexvalidate.sql

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');

<<endfunc>>
  null;

exception
  when others then
    dbms_registry.invalid('APEX');

end validate_apex;
/
show errors

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de