Rem  Copyright (c) Oracle Corporation 2014. All Rights Reserved.
Rem
Rem    NAME
Rem      ords_validate.sql
Rem
Rem    DESCRIPTION
Rem      This procedure checks that the objects in the Oracle REST Data Services schema are valid and
Rem      that the core objects exist.
Rem
Rem    NOTES
Rem      Assumes user with SYSDBA privilege is connected.
Rem
Rem    REQUIREMENTS
Rem      - Oracle Database Release 11.1 or later
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem      epaglina  03/14/2014 - Verifies the core objects and provides the statistics.
Rem

create or replace procedure validate_ords
as
    type tbl_name_arry   is varray(24) of varchar2(100);
    type core_arry       is table of pls_integer index by varchar2(100);
    type counter_arry    is table of pls_integer index by varchar2(100);

    obj_counter          counter_arry;    
    tbl_names            tbl_name_arry;
    core_objs            core_arry;
        
    curr_obj_type        varchar2(30) := 'UNKNOWN';
    ndx_key              varchar2(100);
    
    alter_stmt           varchar2(2000);
    compile_object_type  varchar2(255);
    compile_clause       varchar2(30);

    total_objs           pls_integer := 0;
    total_core_objs      pls_integer := 0;    
    total_compiled_objs  pls_integer := 0;
    total_invalid_objs   pls_integer := 0;

    procedure show_log(msg in varchar2 default null) is
    begin
       dbms_output.put_line('VALIDATION: ' || to_char(SYSDATE,'HH24:MI:SS') || ' ' || msg);
    end show_log;

begin

  show_log('Starting validation with connected user: ' || sys_context('USERENV', 'SESSION_USER') || ', schema: ORDS_METADATA');

  --------------------------------------------
  -- Verify that these core tables exist.  If you add or remove from the list, then update the varray size.
  --
  tbl_names := tbl_name_arry
              ('OAUTH_APPROVAL_PRIVS','OAUTH_APPROVALS','OAUTH_CLIENT_PRIVILEGES',
               'OAUTH_CLIENTS','OAUTH_PENDING_APPROVALS','OAUTH_SESSIONS',
               'ORDS_HANDLERS','ORDS_MODULES','ORDS_OBJECTS','ORDS_PARAMETERS',
               'ORDS_SCHEMAS','ORDS_TEMPLATES','ORDS_URL_MAPPINGS','ORDS_WORKSPACE_SCHEMAS',
               'SEC_AUTHENTICATORS','SEC_KEYS','SEC_ORIGINS_ALLOWED_MODULES',
               'SEC_PRIVILEGE_AUTHS','SEC_PRIVILEGE_MODULES','SEC_PRIVILEGE_ROLES',
               'SEC_PRIVILEGES','SEC_ROLE_MAPPINGS','SEC_ROLES','SEC_SESSIONS');
               
  -- The core_objs keeps tracks if a core table was created.
  for i in 1..tbl_names.count loop
    -- Initialize values to 0
    core_objs(tbl_names(i)) := 0;
  end loop;
    
  --------------------------------------------
  -- Loop over all objects owned by ORDS owner
  --
  for c1 in (select object_name, object_type, status
               from all_objects
               where owner = 'ORDS_METADATA'
              order by object_type, object_name)
  loop
    total_objs := total_objs + 1;

    --
    -- Determine object type and keep track of the total number of objects
    --
    if (c1.object_type != curr_obj_type) then
       -- create new key based on object type and initialize to 1
       obj_counter(c1.object_type) := 1;
       curr_obj_type := c1.object_type;
    else
       obj_counter(c1.object_type) := obj_counter(c1.object_type) + 1;    
    end if;
     
    --
    -- Keep track of the required core objects
    --
    if (c1.object_type = 'TABLE') and core_objs.exists(c1.object_name) then
       core_objs(c1.object_name) := 1;
    end if;
     
    --
    -- Compile these object types if they are invalid
    --
    if (c1.status = 'INVALID') AND 
       (c1.object_type in ('VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY', 
                           'TYPE', 'TYPE BODY', 'INDEXTYPE', 'OPERATOR', 'TRIGGER') ) then

         if (c1.object_type = 'PACKAGE BODY') then
             compile_object_type := 'PACKAGE';
             compile_clause := 'compile body';
         elsif (c1.object_type = 'TYPE BODY') then
             compile_object_type := 'TYPE';
             compile_clause := 'compile body';
         else
             compile_object_type := c1.object_type;
             compile_clause := 'compile';
         end if;
      
         alter_stmt := 'alter ' || compile_object_type || ' ORDS_METADATA.' || dbms_assert.enquote_name(c1.object_name, FALSE) || ' ' || compile_clause;
         
         begin
           execute immediate alter_stmt;
           -- keep track of the number of objects compiled
           total_compiled_objs := total_compiled_objs + 1;
           show_log(alter_stmt);

           exception
             when others then
              -- keep track of the number of failed compilation
              total_invalid_objs := total_invalid_objs + 1;              
              show_log('ERROR during compilation: ' || c1.object_type || ' ' || c1.object_name || ' ' || sqlerrm);
              show_log('command: '||alter_stmt);
         end;                          
    end if;

  end loop;

  -- Display the stats  
  show_log('Total objects: ' || total_objs || ', compiled objects: ' || total_compiled_objs || ', failed compilation: ' || total_invalid_objs);

  -- Display the total count for each object type
  ndx_key := obj_counter.first;
  while (ndx_key is not null) loop
       show_log('    ' || to_char(obj_counter(ndx_key)) || ' ' || ndx_key);
       ndx_key := obj_counter.next(ndx_key);
  end loop;

  -- Check the number of core objects created
  ndx_key := core_objs.first;
  while (ndx_key is not null) loop
       total_core_objs := total_core_objs + core_objs(ndx_key);       
       ndx_key := core_objs.next(ndx_key);
  end loop;

  -- show_log('Total core objects found: ' || to_char(total_core_objs));
  
  if (total_invalid_objs = 0) and (total_core_objs = tbl_names.count) then
     show_log('Validation completed.');
/* Comment out temporarily for beta
     -- Set the status to VALID
     dbms_registry.valid('ORDS');
     show_log('DBMS Registry completed.');
*/     
  else
    if (total_core_objs != tbl_names.count) then
       show_log('Total core objects found: ' || to_char(total_core_objs));
       ndx_key := core_objs.first;
       while (ndx_key is not null) loop
         if (core_objs(ndx_key) = 0) then
            show_log('Error: Missing core object ' || ndx_key);
         end if;
         ndx_key := core_objs.next(ndx_key);
       end loop;    
    end if;
    raise_application_error(-20006, 'ERROR: Validating objects failed.');
/* Comment out temporarily for beta
     -- An error occurred.  Set the status to INVALID.
     dbms_registry.invalid('ORDS');
     show_log('Validation failed. DBMS Registry set to invalid.');
*/     
  end if;
 
  
exception
  when others then
    raise_application_error(-20006, 'ERROR: Validating objects failed.');

/* Comment out temporarily for beta
    show_log('Validation failed. DBMS Registry set to invalid.');
    show_log('ERROR: '||sqlerrm);
    dbms_registry.invalid('ORDS');
*/    
end validate_ords;
/
show errors
