rem
rem This script can be used to display some diagnostics about the OWB
rem Repository and its Control Center Service.
rem 
rem
rem USAGE
rem Logon as the OWB Repository Owner (OWBSYS)
rem    
rem    @<OWB-HOME>/owb/rtp/sql/service_doctor.sql
rem
rem This script will display various diagnostic messages
rem

set serveroutput on;
set role OWB_USER;

declare
  l_available number;
  l_count number;
  l_job_procs number;

begin
  l_count := owbsys.wb_rt_script_util.count_user_errors();
  if l_count = 0
  then
    dbms_output.put_line('All PL/SQL packages and functions are valid');
  else
    dbms_output.put_line('>>>>>> There are errors in one or more PL/SQL packages and functions');
  end if;    

  l_count := owbsys.wb_rt_script_util.count_platform_properties();
  if l_count > 2
  then
    dbms_output.put_line('Platform properties have been loaded correctly');
  else
    dbms_output.put_line('>>>>>> Not all platform properties have been loaded');
  end if;    
  
  l_count := owbsys.wb_rt_script_util.count_platform_locations();
  if l_count = 1
  then
    dbms_output.put_line('Platform location has been seeded correctly');
  else
    dbms_output.put_line('>>>>>> Platform location has not been seeded');
  end if;    
  
  l_count := owbsys.wb_rt_script_util.count_nls_messages();
  if l_count > 0
  then
    dbms_output.put_line('NLS messages have been loaded correctly');
  else
    dbms_output.put_line('>>>>>> NLS messages have not been correctly loaded');
  end if;    

  l_job_procs := owbsys.wb_rt_script_util.count_job_queue_processes();
  if l_job_procs < 10
  then
    dbms_output.put_line('>>>>>> warning: job_queue_processes is ' || l_job_procs || 
      ' - recommended minimum value for owb is 10.');
  end if;
  
  l_available := owbsys.wb_rt_script_util.show_service();
  if l_available <> 0
  then
    dbms_output.put_line('The platform service is available');
  else
    dbms_output.put_line('>>>>>> The platform service is not available');
  end if;
  
  declare
    l_message varchar(512);
  begin
    owbsys.wb_rt_service_doctor.check_startup_script;
    dbms_output.put_line('Service script is accessible to the database server');    
  exception
    when others
    then
      dbms_output.put_line('>>>>>> There is a problem accessing the service startup script from the database server');
      dbms_output.put_line('>>>>>>   Here is the detailed error message which contains the name of the start script file:-');
      l_message := replace(sqlerrm, 'ORA-29532: ', '');
      l_message := replace(l_message, 'Java call terminated by uncaught Java exception: ', '');
      l_message := replace(l_message, 'java.sql.SQLException: ', '');
      dbms_output.put_line('>>>>>>     ' || l_message);
      dbms_output.put_line('>>>>>>   Please verify that the disk containing the file is visible to the database server');
      dbms_output.put_line('>>>>>>   Please verify that the database server account has access rights on the file');
  end;
  
  declare
    l_message varchar(512);
  begin
    owbsys.wb_rt_service_doctor.check_repos_connection;
    dbms_output.put_line('Connection information stored within the repository is correct');
  exception
    when others
    then
      dbms_output.put_line('>>>>>> There is a problem with the repository connection information used to start the service');
      dbms_output.put_line('>>>>>>   Here is the detailed error message which contains the connection information passed to the service:-');
      l_message := replace(sqlerrm, 'ORA-29532: ', '');
      l_message := replace(l_message, 'Java call terminated by uncaught Java exception: ', '');
      l_message := replace(l_message, 'java.sql.SQLException: ', '');
      dbms_output.put_line('>>>>>>     ' || l_message);
      dbms_output.put_line('>>>>>>   Please verify that this information is accurate');
      dbms_output.put_line('>>>>>>   If this information is correct, then please use the ''set_repository_password.sql'' script to reset the stored password');
  end;
  
end;
/