Rem drv: <create type="pkgbodys"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/rec/rec_pkgbodys.sql /st_emdbsa_11.2/2 2009/02/26 12:19:48 nzhao Exp $
Rem
Rem rec_pkgbodys.sql
Rem
Rem Copyright (c) 2003, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      rec_pkgbodys.sql
Rem
Rem    DESCRIPTION
Rem      Backup and Recovery PL/SQL procedures and functions.
Rem      - Submit an oracle-suggested recurring backup job (DB Control only).
Rem      - PL/SQL ROWSET handlers.
Rem      - Callback for restartable backups.
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    pbantis     12/05/08 - Bug 7344865 change retry_backup_job().
Rem    nzhao       11/05/08 - Change the tag name for OSS to support 11.2 DB.
Rem    pbantis     09/02/05 - Change retrieval of large parameter. 
Rem    pbantis     09/01/05 - Restartable backups - handle db target version. 
Rem    pbantis     08/30/05 - Add retry_backup_job(). 
Rem    pbantis     08/29/05 - More default param values. 
Rem    pbantis     08/19/05 - Default param values for update_mgmt_ha_backup(). 
Rem    pbantis     07/28/05 - Remove commits from plsql rowset handlers.
Rem    pbantis     07/25/05 - Add update_mgmt_ha_* procedures. 
Rem    chyu        06/28/05 - New repmgr header impl 
Rem    hying       02/14/05 - Fix merge conflict 
Rem    hying       02/09/05 - add db_name 
Rem    dsahrawa    11/04/04 - use mgmt_jobs.get_job_schedule_record 
Rem    pbantis     11/03/04 - Turn DEFINE back on. 
Rem    pbantis     10/27/04 - Support OMS-side perl. 
Rem    dsahrawa    06/22/04 - use MGMT_JOBS.get_job_schedule_record 
Rem    hying       12/16/03 - 3316974, commit 
Rem    ysun        12/02/03 - modify cred params for do_blackout 
Rem    hying       12/01/03 - do_blackout 
Rem    pbantis     10/09/03 - Override credentials 
Rem    pbantis     09/29/03 - Escape perl special characters in the rman.
Rem                           Support for large parameter.
Rem    skini       09/09/03 - Change in schedule_record structure 
Rem    hying       08/08/03 - Fix extend 
Rem    hying       08/07/03 - Fix bug 3086483 
Rem    hying       07/07/03 - Fix schema 0706
Rem    hying       07/03/03 - Change package name
Rem    hying       06/05/03 - hying_oss
Rem    hying       06/05/03 - Created
Rem

-- This forces sqlplus to ignore ampersand chars
SET DEFINE OFF

CREATE OR REPLACE  PACKAGE BODY MGMT_REC as
--
-- PROCEDURE: submit_out_of_box_backup
--   for use by DBCA to create daily database backup to disk
--
PROCEDURE submit_out_of_box_backup(
    p_host_username IN VARCHAR2,
    p_host_password IN VARCHAR2,
    p_db_username IN VARCHAR2,
    p_db_password IN VARCHAR2,
    p_db_role IN VARCHAR2,
    p_db_connect_string IN VARCHAR2,
    p_db_name IN VARCHAR2,
    p_target_name IN VARCHAR2,
    p_target_type IN VARCHAR2,
    p_job_name IN VARCHAR2,
    p_job_desc IN VARCHAR2,
    p_execution_hours IN NUMBER,
    p_execution_minutes IN NUMBER,
    p_noarchivelog_mode IN VARCHAR2) IS
 
job_targets MGMT_JOB_TARGET_LIST;
job_params MGMT_JOB_PARAM_LIST;
job_id RAW(16);
execution_id RAW(16);
schedule MGMT_JOB_SCHEDULE_RECORD;
rman_script VARCHAR2(256);
large_param CLOB;
large_param_value VARCHAR2(256);

creds MGMT_JOB_CRED_ARRAY := MGMT_JOB_CRED_ARRAY();
l_cred_rows MGMT_CRED_ROW_ARRAY := MGMT_CRED_ROW_ARRAY();
l_cred_record MGMT_CRED_RECORD;
role VARCHAR2(8);
setName VARCHAR2(16);
cred_columns MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();

BEGIN

    job_targets := MGMT_JOB_TARGET_LIST();
    job_targets.extend(1);
    job_targets(1) := MGMT_JOB_TARGET_RECORD(p_target_name, p_target_type);

    -- Submit the parameters for the job. All parameters that your job
    -- needs must be specified here
    job_params := MGMT_JOB_PARAM_LIST();
    job_params.extend(13);

    rman_script := 'run { allocate channel oem_disk_backup device type disk; recover copy of database with tag ''ORA_OEM_LEVEL_0''; backup incremental level 1 cumulative copies=1 for recover of copy with tag ''ORA_OEM_LEVEL_0'' database;}';
    job_params(1) := MGMT_JOB_PARAM_RECORD('backup_strategy', MGMT_JOBS.PARAM_TYPE_SCALAR, 'basic', null);
    job_params(2) := MGMT_JOB_PARAM_RECORD('device_type', MGMT_JOBS.PARAM_TYPE_SCALAR, 'disk', null);
    job_params(3) := MGMT_JOB_PARAM_RECORD('daily_backup_script', MGMT_JOBS.PARAM_TYPE_SCALAR, rman_script, null);
    job_params(4) := MGMT_JOB_PARAM_RECORD('is_cold_backup', MGMT_JOBS.PARAM_TYPE_SCALAR, p_noarchivelog_mode, null);
    job_params(5) := MGMT_JOB_PARAM_RECORD('blackout_target_name', MGMT_JOBS.PARAM_TYPE_SCALAR, p_target_name, null);
    job_params(6) := MGMT_JOB_PARAM_RECORD('blackout_target_type', MGMT_JOBS.PARAM_TYPE_SCALAR, p_target_type, null);
    job_params(7) := MGMT_JOB_PARAM_RECORD('db_connect_string', MGMT_JOBS.PARAM_TYPE_SCALAR, p_db_connect_string, null);
    job_params(8) := MGMT_JOB_PARAM_RECORD('use_rcvcat', MGMT_JOBS.PARAM_TYPE_SCALAR, 'NO', null);
    job_params(9) := MGMT_JOB_PARAM_RECORD('db_10_or_higher', MGMT_JOBS.PARAM_TYPE_SCALAR, 'YES', null);
    job_params(10) := MGMT_JOB_PARAM_RECORD('rman_perl_script', MGMT_JOBS.PARAM_TYPE_LARGE, null, null);
    job_params(11) := MGMT_JOB_PARAM_RECORD('do_blackout', MGMT_JOBS.PARAM_TYPE_SCALAR, 'NO', null);
    job_params(12) := MGMT_JOB_PARAM_RECORD('db_name', MGMT_JOBS.PARAM_TYPE_SCALAR, p_db_name, null);
    job_params(13) := MGMT_JOB_PARAM_RECORD('modified_rman_perl_script', MGMT_JOBS.PARAM_TYPE_LARGE, null, null);

    -- Always override the preferred credentials.
    creds.extend(2);
    l_cred_rows.extend(2);

    -- Database host credentials
    l_cred_rows(1) := MGMT_CRED_ROW_RECORD('HostUsername', p_host_username);
    l_cred_rows(2) := MGMT_CRED_ROW_RECORD('HostPassword', p_host_password);
    l_cred_record := MGMT_CRED_RECORD(MGMT_USER.get_current_em_user,
                                      'DBHostCreds', l_cred_rows);
    creds(1) := MGMT_JOB_CRED_RECORD(null, p_target_type, null, l_cred_record);

    -- Database credentials
    l_cred_rows.extend(1);
    role := UPPER(p_db_role);
    IF role = 'NORMAL' THEN
      l_cred_rows(1) := MGMT_CRED_ROW_RECORD('username', p_db_username);
      l_cred_rows(2) := MGMT_CRED_ROW_RECORD('password', p_db_password);
      l_cred_rows(3) := MGMT_CRED_ROW_RECORD('role', role);
      setName := 'DBCredsNormal';
    ELSE
      l_cred_rows(1) := MGMT_CRED_ROW_RECORD('sysDBAUserName', p_db_username);
      l_cred_rows(2) := MGMT_CRED_ROW_RECORD('sysDBAPassword', p_db_password);
      l_cred_rows(3) := MGMT_CRED_ROW_RECORD('sysDBARole', role);
      setName := 'DBCredsSYSDBA';
      cred_columns.extend(3);
      cred_columns(1) := 'sysDBAUserName';
      cred_columns(2) := 'sysDBAPassword';
      cred_columns(3) := 'sysDBARole';

      job_params.extend(2);
      job_params(14) := MGMT_JOB_PARAM_RECORD('db_credential_set_name', 1, setName, null);
      job_params(15) := MGMT_JOB_PARAM_RECORD('db_credential_columns', MGMT_JOBS.PARAM_TYPE_VECTOR, null, cred_columns);
    END IF;
    l_cred_record := MGMT_CRED_RECORD(MGMT_USER.get_current_em_user,
                                      setName, l_cred_rows);
    creds(2) := MGMT_JOB_CRED_RECORD(null, p_target_type, null, l_cred_record);

    -- Schedule
    schedule := MGMT_JOBS.get_job_schedule_record(MGMT_JOBS.DAILY_FREQUENCY_CODE,
                                         SYSDATE, null,
                                         p_execution_hours, p_execution_minutes,
                                         0, null, null,
                                         MGMT_JOBS.TIMEZONE_REPOSITORY, 0,0,
                                         null);
    -- Submit the job.
    MGMT_JOBS.submit_job(p_job_name,
                         p_job_desc,
                         'Backup',
                         job_targets,
                         job_params,
                         schedule,
                         job_id,
                         execution_id,
                         null, 0,
                         creds);

    -- Set the 1st large parameter.
    large_param := MGMT_JOBS.get_large_param(job_id, execution_id, 'rman_perl_script');
    large_param_value := '&br_save_agent_env(); ' || 
      '&br_prebackup($l_db_connect_string, $l_is_cold_backup, $l_use_rcvcat, $l_db_10_or_higher, $l_backup_strategy); ' || 
      'my $result = &br_backup(); ' || 
      'exit($result);';
    dbms_lob.write(large_param, LENGTH(large_param_value), 1, large_param_value);

    -- Set the 2nd large parameter.
    large_param := MGMT_JOBS.get_large_param(job_id, execution_id, 'modified_rman_perl_script');
    large_param_value := ' ';
    dbms_lob.write(large_param, LENGTH(large_param_value), 1, large_param_value);

    commit;
    
    dbms_output.put_line('Oracle Suggested Backup Job ' || p_job_name || ' successfully inserted');
    dbms_output.put_line('Job id is ' || job_id);
    dbms_output.put_line('Execution id is ' || execution_id);

END submit_out_of_box_backup;

-- PL/SQL Procedure used by loader to handle table inserts for mgmt_ha_backup.
PROCEDURE update_mgmt_ha_backup(
    p_target_guid IN RAW,
    p_collection_timestamp IN DATE DEFAULT SYSDATE,
    p_end_time IN DATE DEFAULT NULL,
    p_status IN VARCHAR2 DEFAULT NULL,
    p_session_key IN NUMBER DEFAULT NULL,
    p_session_recid IN NUMBER DEFAULT NULL,
    p_session_stamp IN NUMBER DEFAULT NULL,
    p_command_id IN VARCHAR2 DEFAULT NULL,
    p_start_time in DATE DEFAULT NULL,
    p_time_taken_display IN VARCHAR2 DEFAULT NULL,
    p_input_type IN VARCHAR2 DEFAULT NULL,
    p_output_device_type IN VARCHAR2 DEFAULT NULL,
    p_input_bytes_display IN VARCHAR2 DEFAULT NULL,    
    p_output_bytes_display IN VARCHAR2 DEFAULT NULL,
    p_output_bytes_per_sec_display IN VARCHAR2 DEFAULT NULL) IS
BEGIN

    -- Insert the entry into mgmt_ha_backup table
    BEGIN
      INSERT INTO mgmt_ha_backup
        (target_guid, collection_timestamp, end_time, status, session_key,
         session_recid, session_stamp, command_id, start_time, time_taken_display, 
         input_type, output_device_type, input_bytes_display, output_bytes_display,
         output_bytes_per_sec_display)
      VALUES
        (p_target_guid, p_collection_timestamp, p_end_time, p_status, p_session_key,
         p_session_recid, p_session_stamp, p_command_id, p_start_time, p_time_taken_display, 
         p_input_type, p_output_device_type, p_input_bytes_display, p_output_bytes_display,
         p_output_bytes_per_sec_display);
    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
    -- Entry already exists, update the entry
      UPDATE mgmt_ha_backup SET
        collection_timestamp = p_collection_timestamp, 
        end_time = p_end_time, 
        status = p_status, 
        session_key = p_session_key,
        session_recid = p_session_recid, 
        session_stamp = p_session_stamp, 
        command_id = p_command_id, 
        start_time = p_start_time, 
        time_taken_display = p_time_taken_display, 
        input_type = p_input_type, 
        output_device_type = p_output_device_type, 
        input_bytes_display = p_input_bytes_display, 
        output_bytes_display = p_output_bytes_display,
        output_bytes_per_sec_display = p_output_bytes_per_sec_display
      WHERE target_guid = p_target_guid;
    END;

EXCEPTION WHEN OTHERS THEN NULL;

END update_mgmt_ha_backup;
    
-- PL/SQL Procedure used by loader to handle table inserts for mgmt_ha_mttr.
PROCEDURE update_mgmt_ha_mttr(
    p_target_guid IN RAW,
    p_collection_timestamp IN DATE DEFAULT SYSDATE,
    p_estimated_mttr IN NUMBER DEFAULT NULL) IS
BEGIN

    -- Insert the entry into mgmt_ha_mttr table
    BEGIN
      INSERT INTO mgmt_ha_mttr
        (target_guid, collection_timestamp, estimated_mttr)
      VALUES
        (p_target_guid, p_collection_timestamp, p_estimated_mttr);
    EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
    -- Entry already exists, update the entry
      UPDATE mgmt_ha_mttr SET
        collection_timestamp = p_collection_timestamp, 
        estimated_mttr = p_estimated_mttr
      WHERE target_guid = p_target_guid;
    END;

EXCEPTION WHEN OTHERS THEN NULL;

END update_mgmt_ha_mttr;

-- Restartable backup job.
PROCEDURE retry_backup_job(
    p_reason IN NUMBER,
    p_status IN NUMBER,
    p_job_id IN RAW,
    p_execution_id IN RAW) IS

  -- Target version.
  l_dbversion_param VARCHAR2(64) := 'p_db_version';
  l_dbversion_value MGMT_JOB_VECTOR_PARAMS;
  l_versionGTE9i BOOLEAN := false;
  l_decimalPosition NUMBER;
  l_versionNum NUMBER;

  -- Job start time.
  l_job_utc_start_time DATE := NULL;
  l_target_timezone VARCHAR2(64);
  l_job_target_start_time_date DATE;
  l_job_target_start_time_string VARCHAR2(64);
  -- Any format we want it to be.
  l_job_target_start_time_fmt VARCHAR(64) := 'MMDDYYHHMISS';
  
  -- large input parameter: rman_perl_script
  l_script_param VARCHAR2(64) := 'rman_perl_script';
  l_script_value_update_clob CLOB;

  -- large input parameter: retry_rman_perl_script
  l_retryscript_param VARCHAR2(64) := 'retry_rman_perl_script';
  l_retryscript_param_found NUMBER := 1;
  l_retryscript_value_id RAW(16);
  l_retryscript_value_clob CLOB;

  -- parameter: exec_rman_perl_script
  l_execscript_param VARCHAR2(64) := 'exec_rman_perl_script';

  l_backup_index NUMBER;
  l_semicolon_index NUMBER;
  l_not_backed_up_index NUMBER;
  l_backup_clause VARCHAR2(512);

  l_chunk_size INTEGER;
  l_buffer VARCHAR2(32767);
  l_read_offset INTEGER;
  
BEGIN
    -- Determine the target version.
    SELECT vector_value INTO l_dbversion_value
    FROM MGMT_JOB_PARAMETER
    WHERE job_id=p_job_id
    AND execution_id=p_execution_id
    AND parameter_name=l_dbversion_param;

    IF ((l_dbversion_value IS NOT NULL) AND (l_dbversion_value.count > 0))
    THEN    
      l_decimalPosition := INSTR(l_dbversion_value(1), '.');
      IF (l_decimalPosition > 0)
      THEN
        l_versionNum := TO_NUMBER(SUBSTR(l_dbversion_value(1), 0, l_decimalPosition));
        IF (l_versionNum >= 9)
        THEN
          l_versionGTE9i := true;
        END IF;
      END IF;
    END IF;

    -- Restartable backups are only supported for db version 9i or higher.              
    IF (l_versionGTE9i)
    THEN
      -- Get the start time (UTC) of the first execution in this chain.
      l_job_utc_start_time := getStartTimeOfFirstInChain(p_job_id, p_execution_id);
      IF (l_job_utc_start_time IS NULL)
      THEN RETURN;
      END IF;

      -- Get the target's timezone region.
      SELECT timezone_region INTO l_target_timezone
      FROM MGMT_TARGETS
      WHERE target_guid=
        (SELECT target_guid FROM MGMT_JOB_TARGET WHERE job_id=p_job_id
         AND execution_id=p_execution_id);

      -- Convert the start time (UTC) to that of the target timezone region.
      l_job_target_start_time_date := MGMT_GLOBAL.FROM_UTC(l_job_utc_start_time, l_target_timezone);
      
      -- Convert the date to a VARCHAR2 for RMAN 'backup ... TO_DATE()'.
      l_job_target_start_time_string := TO_CHAR(l_job_target_start_time_date, l_job_target_start_time_fmt);
      IF EMDW_LOG.p_is_debug_set
      THEN
        EMDW_LOG.debug('retry_backup_job: l_job_target_start_time_string ' || l_job_target_start_time_string, LOG_NAME);
      END IF;

      -- Get the 'rman_perl_script' large parameter value.                                                                              
      l_script_value_update_clob := MGMT_JOB_ENGINE.GET_LARGE_PARAM(p_job_id,
        p_execution_id, l_script_param);
      
      -- Find the 'backup' command in the 'rman_perl_script' large parameter value.
      l_backup_index := DBMS_LOB.INSTR(l_script_value_update_clob, 'backup ');
      IF (l_backup_index > 0)
      THEN
        -- Find the trailing semicolon in the backup command.
        l_semicolon_index := DBMS_LOB.INSTR(l_script_value_update_clob, ';', l_backup_index);  
        IF (l_semicolon_index > 0)
        THEN
          -- Ensure that there's not already a 'not backed up' clause
          -- between the 'backup' and ';'.
          l_not_backed_up_index := DBMS_LOB.INSTR(l_script_value_update_clob, 'not backed up');
          -- Clause not found, or found after the semicolon.
          IF ((l_not_backed_up_index = 0) OR (l_not_backed_up_index > l_semicolon_index))
          THEN          
            -- Find the large input parameter 'retry_rman_perl_script' for this job.
            SELECT count(*) INTO l_retryscript_param_found
            FROM MGMT_JOB_PARAMETER
            WHERE job_id=p_job_id
            AND parameter_name=l_retryscript_param;

            -- CREATE parameter 'retry_rman_perl_script'.
            IF (l_retryscript_param_found = 0)
            THEN
              -- Create the parameter for this execution.
              INSERT INTO MGMT_JOB_PARAMETER(job_id, execution_id,
                parameter_name, parameter_type)
              VALUES (p_job_id, p_execution_id,
                l_retryscript_param, MGMT_JOB_ENGINE.PARAM_TYPE_LARGE)
              RETURNING large_value INTO l_retryscript_value_id;

              l_retryscript_value_clob := MGMT_JOB_ENGINE.GET_LARGE_PARAM(p_job_id,
                p_execution_id, l_retryscript_param, 1);

            -- UPDATE parameter 'retry_rman_perl_script'.
            ELSE
              -- Get the large value (id) for the existing 'retry_rman_perl_script' large parameter.
              SELECT large_value INTO l_retryscript_value_id
              FROM MGMT_JOB_PARAMETER
              WHERE job_id=p_job_id
              AND parameter_name=l_retryscript_param
              AND rownum=1;

              -- Associate the parameter to this execution.
              INSERT INTO MGMT_JOB_PARAMETER(job_id, execution_id,
                parameter_name, parameter_type, large_value)
              VALUES (p_job_id, p_execution_id,
                l_retryscript_param, MGMT_JOB_ENGINE.PARAM_TYPE_LARGE, 
                l_retryscript_value_id);
                
              -- Get the 'retry_rman_perl_script' large parameter value.                                                                              
              l_retryscript_value_clob := MGMT_JOB_ENGINE.GET_LARGE_PARAM(p_job_id,
                p_execution_id, l_retryscript_param, 1);

              -- Trim the original large parameter value.
              DBMS_LOB.TRIM(l_retryscript_value_clob, 0);
            END IF;
            
            -- Write to the 'retry_rman_perl_script' large parameter value.
            -- Copy 'rman_perl_script' data from the beginning up to the semicolon.
            DBMS_LOB.COPY(l_retryscript_value_clob, l_script_value_update_clob, l_semicolon_index-1, 1, 1);
            
            -- Use the chunk size for better read/write performance.
            l_chunk_size := DBMS_LOB.GETCHUNKSIZE(l_script_value_update_clob);

            -- Construct the 'not backed up' clause.
            l_backup_clause := ' not backed up since time \"TO_DATE(''' ||
              l_job_target_start_time_string || ''', ''' || 
              l_job_target_start_time_fmt || ''')\"';
            
            -- Append the 'not backed up' clause.        
            DBMS_LOB.WRITEAPPEND(l_retryscript_value_clob, LENGTH(l_backup_clause), l_backup_clause);
            -- Trim everything after the 'not backed up' clause.
            DBMS_LOB.TRIM(l_retryscript_value_clob, l_semicolon_index + LENGTH(l_backup_clause) - 1);
            
            BEGIN
              -- Start reading from the semicolon.
              l_read_offset := l_semicolon_index;            
              LOOP
                -- Read from the copied clob value.
                DBMS_LOB.READ(l_script_value_update_clob, l_chunk_size, l_read_offset, l_buffer);
                l_read_offset := l_read_offset + l_chunk_size;
                
                -- Write to the original clob value.
                DBMS_LOB.WRITEAPPEND(l_retryscript_value_clob, l_chunk_size, l_buffer);
              END LOOP;
            EXCEPTION WHEN OTHERS THEN NULL;
            END;

            -- For this execution, update parameter 'exec_rman_perl_script' to contain 'retry_rman_perl_script'.
            UPDATE MGMT_JOB_PARAMETER
            SET scalar_value=l_retryscript_param
            WHERE job_id=p_job_id
            AND execution_id=p_execution_id
            AND parameter_name=l_execscript_param;

            -- COMMMIT will be made by caller of retry_backup_job.

          END IF;
        END IF;
      END IF;
    END IF;

EXCEPTION WHEN OTHERS
THEN
  EMDW_LOG.error('retry_backup_job: Exception occured while retrying Backup job', LOG_NAME);
  EMDW_LOG.error('retry_backup_job: Job id ' || p_job_id || ' and execution id ' || p_execution_id ||
    ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM, LOG_NAME);
END retry_backup_job;

-- Function to return the start time of the first execution in the chain.
-- Job 123, Day 1, Execution 123-1, Execution 123-2
-- Job 123, Day 2, Execution 123-11, Execution 123-21
-- e.g. Give Execution 123-21, you want the start time of Execution 123-11.
FUNCTION getStartTimeOfFirstInChain(
    p_job_id IN RAW,
    p_execution_id IN RAW) RETURN DATE IS

  -- Job start time.
  l_utc_start_time DATE := NULL;
  -- Source execution id.
  l_source_execution_id MGMT_JOB_EXEC_SUMMARY.execution_id%TYPE := NULL;

BEGIN

  SELECT start_time, source_execution_id 
  INTO l_utc_start_time, l_source_execution_id
  FROM MGMT_JOB_EXEC_SUMMARY
  WHERE job_id=p_job_id
  AND execution_id=p_execution_id;

  IF ((l_source_execution_id IS NOT NULL) AND (p_execution_id != l_source_execution_id))
  THEN
    -- Recursive call.
    l_utc_start_time := getStartTimeOfFirstInChain(p_job_id, l_source_execution_id);
  END IF;

  RETURN l_utc_start_time;
  
EXCEPTION WHEN OTHERS
THEN
  EMDW_LOG.error('getStartTimeOfFirstInChain: Exception occured while getting job execution start time', LOG_NAME);
  EMDW_LOG.error('getStartTimeOfFirstInChain: Job id ' || p_job_id || ' and execution id ' || p_execution_id ||
    ', SQLCODE: ' || SQLCODE || ', SQLERRM: ' || SQLERRM, LOG_NAME);
  RETURN l_utc_start_time;
END getStartTimeOfFirstInChain;
    
END MGMT_REC;
/
SET DEFINE ON
show errors;