Rem
Rem $Header: jobs_audit_pkgbody.sql 04-mar-2005.07:42:24 kmanicka Exp $
Rem
Rem jobs_audit_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem jobs_audit_pkgbody.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem kmanicka 03/04/05 - remove DBMS_OUTPUT
Rem skini 10/20/04 - Versioning changes
Rem shianand 10/04/04 - shianand_shi_job_user_audit
Rem shianand 09/02/04 - Created
Rem
--This package generate and put the "audit" data into MGMT_JOB_AUDIT_HISTORY table
--job_name: The name of a specific job whose audit trail is required
--username : The the name of the user who submitted the job, username could be
--null in that case all the jobs with in the specified time interval will be scanned
--and stored inside the table
--job_owner: The job owner.
--job_type: The job type to get audit information for
--start_time, end_time: If specified, the time interval between which to look for jobs
--Coding Style
--variable prefix with l_ are local variables and variables prefix with p_ are
--ones that are passed to the corresponding procedures and the ones with prefix
--as p_ are ones that are passed to the corresponding functions
--General Terms
--command_input: The standard input to the command.
--command_output: The command output
--db_username: The database username
--src_os_username: The source O/S username
--dest_os_username: The destination O/S username
--dest_args: If the operation type is "pipedRemoteOp", these indicate the
--arguments on the destination agent
--dest_command: If the operation type is "pipedRemoteOp", these indicate the
--command on the destination agent
--source_args: If the operation type is "remoteOp", these indicate the
--arguments. If the operation type is "pipedRemoteOp", these indicate the
--arguments on the source agent
--source_command: If the operation type is "remoteOp", these indicate the
--command. If the operation type is "pipedRemoteOp", these indicate the command
--on the source agent
--exit_code: Exit Code of the Job
--host_name: Host Name which is directing the job (OMS)
--job_name: The job name
--job_owner: The job owner
--job_type: The job type of the respective job
--operation_type: The type of operation performed. Like remoteOp, putFile, getFile,
--fileTransfer, pipedRemoteOp.
--remoteOp: A remote operation on an agent
--putFile: A file transfered from an OMS to an agent
--getFile: A file transfered from an agent to an OMS
--fileTransfer: A file transfered between two agents
--pipedRemoteOp: A piped remote op (two OSCommands run on two agents, with the
--standard output of one streamed to the standard output of
--the other
--step_id: step_id of the step its unique
--dest_file: If the operation type is "putFile", this indicates the file that was
--transfered to the agent. If the operation type is "fileTransfer", this indicates
--the destination file
--source_file: If the operation type is "getFile", this indicates the file that
--was transfered from the agen. If the operation type is "fileTransfer", this
--indicates the source file
--start_time: The start time of the job
--destTarget_name: The destination target name
--destTarget_type: The target type of the destination target
--sourceTarget_name: The source target name
--sourceTarget_type: The target type of the source target
--General Terms END
CREATE OR REPLACE PACKAGE BODY MGMT_JOB_AUDIT_HISTORY_DATA IS
--Procedure insert_val_tables inserts the valuse in the MGMT_JOB_AUDIT_HISTORY
--Table
PROCEDURE insert_val_tables(p_command_input VARCHAR2,
p_output_id RAW,
p_db_username VARCHAR2,
p_src_os_username VARCHAR2,
p_dest_os_username VARCHAR2,
p_dest_args VARCHAR2,
p_dest_command VARCHAR2,
p_source_args VARCHAR2,
p_source_command VARCHAR2,
p_exit_code NUMBER,
p_host_name VARCHAR2,
p_job_name VARCHAR2,
p_job_owner VARCHAR2,
p_job_type VARCHAR2,
p_operation_type VARCHAR2,
p_step_id INTEGER,
p_dest_file VARCHAR2,
p_source_file VARCHAR2,
p_start_time DATE,
p_destTarget_name VARCHAR2,
p_destTarget_type VARCHAR2,
p_sourceTarget_name VARCHAR2,
p_sourceTarget_type VARCHAR2) IS
amt INTEGER := 32767;
des_offset INTEGER := 1;
src_offset INTEGER := 1;
src_CLOB CLOB;
emp_CLOB CLOB;
des_CLOB CLOB;
len NUMBER := 1;
BEGIN
--IF the output_id is null then source clob is null
IF p_output_id IS NULL THEN
src_CLOB := NULL;
ELSE
SELECT output
INTO src_CLOB
FROM mgmt_job_output
WHERE output_id = p_output_id;
END IF;
emp_CLOB := EMPTY_CLOB();
INSERT INTO MGMT_JOB_AUDIT_HISTORY (command_input,
command_output,
db_username,
src_os_username,
dest_os_username,
dest_args,
dest_command,
source_args,
source_command,
exit_code,
host_name,
job_name,
job_owner,
job_type,
operation_type,
step_id,
dest_file,
source_file,
start_time,
destTarget_name,
destTarget_type,
sourceTarget_name,
sourceTarget_type)
VALUES (p_command_input,
emp_CLOB,
p_db_username,
p_src_os_username,
p_dest_os_username,
p_dest_args,
p_dest_command,
p_source_args,
p_source_command,
p_exit_code,
p_host_name,
p_job_name,
p_job_owner,
p_job_type,
p_operation_type,
p_step_id,
p_dest_file,
p_source_file,
p_start_time,
p_destTarget_name,
p_destTarget_type,
p_sourceTarget_name,
p_sourceTarget_type)
RETURNING command_output INTO des_CLOB;
--IF the output_id is null then output shown in the MGMT_JOB_AUDIT TABLE is
--null
IF src_ClOB IS NULL THEN
des_clob := NULL;
ELSE
len := DBMS_LOB.GETLENGTH(src_clob);
LOOP
DBMS_LOB.COPY(des_clob,
src_clob,
amt,
des_offset,
src_offset);
EXIT WHEN src_offset > len;
src_offset := src_offset + amt;
des_offset := des_offset + amt;
END LOOP;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
COMMIT;
END insert_val_tables;
-- insert_val_tables END
--Function search_hostname takes in targetType and targetName and returns back
--hostname on which this particular target this particluar target is hosted it
--searches in the MGMT_TARGETS TABLE for the host name with the specified target
--type and target name
FUNCTION search_hostname(p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2)
RETURN VARCHAR2 IS
l_host_name VARCHAR2(64);
BEGIN
SELECT host_name
INTO l_host_name
FROM MGMT_TARGETS
WHERE target_name = p_target_name
AND target_type = p_target_type;
RETURN l_host_name;
END search_hostname;
--search_hostname Function END
--Function search_params takes the list and array as input and searches all the
--parameters that are there in the array and in the list and if matched
--paramaters are found in the list then it stores it in another list which is
--returned back, if the paramater in the array is not found in the list then it
--stores null in the returned list against the given the array paramater value
FUNCTION search_params(p_step_param_list IN MGMT_JOB_PARAM_LIST,
p_step_param_name IN MGMT_JOB_VECTOR_PARAMS)
RETURN MGMT_JOB_PARAM_LIST IS
k INTEGER := 1;
l_temp INTEGER := 1;
l_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_vector_value MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS();
BEGIN
FOR i IN p_step_param_name.FIRST..p_step_param_name.LAST LOOP
l_temp := k;
FOR j IN p_step_param_list.FIRST..p_step_param_list.LAST LOOP
IF p_step_param_name(i) = p_step_param_list(j).param_name THEN
l_list.extend(1);
l_list(k) := MGMT_JOB_PARAM_RECORD(p_step_param_list(j).param_name,
p_step_param_list(j).param_type,
p_step_param_list(j).scalar_value,
p_step_param_list(j).vector_value);
k := k+1;
END IF;
END LOOP;
--stores null into final list if given array parameter is not found in the
--list
IF l_temp = k THEN
l_list.extend(1);
l_list(k) := MGMT_JOB_PARAM_RECORD(p_step_param_name(i),
NULL,
NULL,
NULL);
k := k+1;
END IF;
END LOOP;
IF k = 1 THEN
RETURN NULL;
ELSE RETURN(l_list);
END IF;
END search_params;
--search_params FUNCTION END
--Function remoteOp_paramslooks for the job step parameters with remoteOP
--commandType and then it updates the corresponding columns in
--MGMT_JOB_AUDIT_HISTORY Table
FUNCTION remoteOp_params(p_command_name VARCHAR2,
p_job_name VARCHAR2,
p_job_owner VARCHAR2,
p_job_type VARCHAR2,
p_start_time DATE,
p_job_id RAW,
p_execution_id RAW,
p_step_name VARCHAR2,
p_step_id INTEGER,
p_all_params NUMBER,
p_iterate_param VARCHAR2,
p_iterate_param_index NUMBER,
p_output_id RAW)
RETURN NUMBER IS
l_host_name VARCHAR2(64);
l_fill_encrypt_info BOOLEAN := FALSE;
l_encrypt_info MGMT_JOB_INT_ARRAY := MGMT_JOB_INT_ARRAY();
l_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_job_step_param_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
--these are the parameters that are being searched for in the JOB PARAM STEPS
--for remoteOp command type
l_step_param_name MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS('args',
'input',
'remoteCommand',
'targetName',
'targetType',
'username');
BEGIN
--here all the step parameters are stored in the list,
--MGMT_JOB_ENGINE.get_job_step_params search through all the job parameters
--in the JOB_STEP_PARAMS TABLE for remoteOp command type
l_job_step_param_list := MGMT_JOB_ENGINE.get_job_step_params(p_job_id,
p_execution_id,
p_step_name,
p_step_id,
p_all_params,
p_iterate_param,
p_iterate_param_index,
l_fill_encrypt_info,
l_encrypt_info);
--stores the searched value in the list as returned by search_params for
--remoteOp command type
l_list := search_params(l_job_step_param_list,
l_step_param_name);
--stores the hostname in the list as returned by search_hostname
l_host_name := search_hostname(l_list(4).scalar_value,
l_list(5).scalar_value);
--inserts the valuses into the MGMT_JOB_AUDIT_HISTORY TABLE
insert_val_tables(l_list(2).scalar_value,--input
p_output_id,
NULL,
l_list(6).scalar_value,--username
NULL,
NULL,
NULL,
l_list(1).scalar_value,--args
l_list(3).scalar_value,--remoteCommand
NULL,
l_host_name,
p_job_name,
p_job_owner,
p_job_type,
p_command_name,--commandName
p_step_id,
NULL,
NULL,
p_start_time,
NULL,
NULL,
l_list(4).scalar_value,--targetName
l_list(5).scalar_value);--targetType
RETURN 1;
END remoteOp_params;
--Function remoteOp_params END
--Function putFile_paramslooks for the job step parameters with commandType =
--'putFile' and sourceType = 'file'then it updates the corresponding columns
--in MGMT_JOB_AUDIT_HISTORY Table
FUNCTION putFile_params(p_command_name VARCHAR2,
p_job_name VARCHAR2,
p_job_owner VARCHAR2,
p_job_type VARCHAR2,
p_start_time DATE,
p_job_id RAW,
p_execution_id RAW,
p_step_name VARCHAR2,
p_step_id INTEGER,
p_all_params NUMBER,
p_iterate_param VARCHAR2,
p_iterate_param_index NUMBER,
p_output_id RAW)
RETURN NUMBER IS
l_destFile VARCHAR2(64);
l_sourceFile VARCHAR2(64);
l_host_name VARCHAR2(64);
l_fill_encrypt_info BOOLEAN := FALSE;
l_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_encrypt_info MGMT_JOB_INT_ARRAY := MGMT_JOB_INT_ARRAY();
l_job_step_param_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
--these are the parameters that are being searched for in the JOB PARAM STEPS
--for putFile command type
l_step_param_name MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS('destFile',
'sourceFile',
'sourceType',
'targetName',
'targetType',
'username');
BEGIN
--here all the step parameters are stored in the list,
--MGMT_JOB_ENGINE.get_job_step_params search through all the job parameters
--in the JOB_STEP_PARAMS TABLE for the putFile command type
l_job_step_param_list := MGMT_JOB_ENGINE.get_job_step_params(p_job_id,
p_execution_id,
p_step_name,
p_step_id,
p_all_params,
p_iterate_param,
p_iterate_param_index,
l_fill_encrypt_info,
l_encrypt_info);
--stores the searched value in the list as returned by search_params for
--putFile command type
l_list := search_params(l_job_step_param_list,
l_step_param_name);
--checks if the sourceType of the job parameter is set to file then it puts
--the corrsponding path of the file in the MGMT_JOB_AUDIT_HISTORY Table else
--it put null against sourceFile, destFile column in the
--MGMT_JOB_AUDIT_HISTORY Table for putFile command type
IF l_list(3).scalar_value = 'file' THEN
l_destFile := l_list(1).scalar_value;
l_sourceFile := l_list(2).scalar_value;
ELSE
l_destFile := NULL;
l_sourceFile := NULL;
END IF;
--stores the hostname in the list as returned by search_hostname
l_host_name := search_hostname(l_list(4).scalar_value,
l_list(5).scalar_value);
--inserts the valuses into the MGMT_JOB_AUDIT_HISTORY TABLE
insert_val_tables(NULL,
p_output_id,
NULL,
l_list(6).scalar_value,--username
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_host_name,
p_job_name,
p_job_owner,
p_job_type,
p_command_name,--commandName
p_step_id,
l_destFile,--destFile
l_sourceFile,--sourceFile
p_start_time,
NULL,
NULL,
l_list(4).scalar_value,--targetName
l_list(5).scalar_value);--targetType
RETURN 1;
END putFile_params;
--Function putFile_params END
--Function getFile_params looks for the job step parameters with commandType =
--'getFile'and sourceType = 'file' and then it updates the corresponding columns
-- in MGMT_JOB_AUDIT_HISTORY Table
FUNCTION getFile_params(p_command_name VARCHAR2,
p_job_name VARCHAR2,
p_job_owner VARCHAR2,
p_job_type VARCHAR2,
p_start_time DATE,
p_job_id RAW,
p_execution_id RAW,
p_step_name VARCHAR2,
p_step_id INTEGER,
p_all_params NUMBER,
p_iterate_param VARCHAR2,
p_iterate_param_index NUMBER,
p_output_id RAW)
RETURN NUMBER IS
l_destFile VARCHAR2(64);
l_sourceFile VARCHAR2(64);
l_host_name VARCHAR2(64);
l_fill_encrypt_info BOOLEAN := FALSE;
l_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_encrypt_info MGMT_JOB_INT_ARRAY := MGMT_JOB_INT_ARRAY();
l_job_step_param_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
--these are the parameters that are being searched for in the JOB PARAM STEPS
--for getFile command type
l_step_param_name MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS('destFile',
'destType',
'sourceFile',
'targetName',
'targetType',
'username');
BEGIN
--here all the step parameters are stored in the list,
--MGMT_JOB_ENGINE.get_job_step_params search through all the job parameters
--in the JOB_STEP_PARAMS TABLE for the getFile command type
l_job_step_param_list := MGMT_JOB_ENGINE.get_job_step_params(p_job_id,
p_execution_id,
p_step_name,
p_step_id,
p_all_params,
p_iterate_param,
p_iterate_param_index,
l_fill_encrypt_info,
l_encrypt_info);
--stores the searched value in the list as returned by search_params for
--getFile command type
l_list := search_params(l_job_step_param_list,
l_step_param_name);
--checks if the destType of the job parameter is set to file then it puts
--the corrsponding path of the file in the MGMT_JOB_AUDIT_HISTORY Table else
--it put null against sourceFile, destFile column in the
--MGMT_JOB_AUDIT_HISTORY Table for getFile command type
IF l_list(2).scalar_value = 'file' THEN
l_destFile := l_list(1).scalar_value;
l_sourceFile := l_list(3).scalar_value;
ELSE
l_destFile := NULL;
l_sourceFile := NULL;
END IF;
--stores the hostname in the list as returned by search_hostname
l_host_name := search_hostname(l_list(4).scalar_value,
l_list(5).scalar_value);
--inserts the valuses into the MGMT_JOB_AUDIT_HISTORY TABLE
insert_val_tables(NULL,
p_output_id,
NULL,
l_list(6).scalar_value,--username
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
l_host_name,
p_job_name,
p_job_owner,
p_job_type,
p_command_name,
p_step_id,
l_destFile,--destFile
l_sourceFile,--sourceFile
p_start_time,
l_list(4).scalar_value,--targetName
l_list(5).scalar_value,--targetType
NULL,
NULL);
RETURN 1;
END getFile_params;
--Function getFile_params END
--Function fileTransfer_params looks for the job step parameters with commandType
--= 'fileTransfer' and sourceType = 'file' and for remote_pipe_op when
--sourcefile = null and destFile = null and then it updates the corresponding
--columns in MGMT_JOB_AUDIT_HISTORY Table
FUNCTION fileTransfer_params(p_command_name VARCHAR2,
p_job_name VARCHAR2,
p_job_owner VARCHAR2,
p_job_type VARCHAR2,
p_start_time DATE,
p_job_id RAW,
p_execution_id RAW,
p_step_name VARCHAR2,
p_step_id INTEGER,
p_all_params NUMBER,
p_iterate_param VARCHAR2,
p_iterate_param_index NUMBER,
p_output_id RAW)
RETURN NUMBER IS
l_destFile VARCHAR2(64);
l_sourceFile VARCHAR2(64);
l_host_name VARCHAR2(64);
l_fill_encrypt_info BOOLEAN := FALSE;
l_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_encrypt_info MGMT_JOB_INT_ARRAY := MGMT_JOB_INT_ARRAY();
l_list_pipeOp MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
l_job_step_param_list MGMT_JOB_PARAM_LIST := MGMT_JOB_PARAM_LIST();
--these are the parameters that are being searched for in the JOB PARAM STEPS
--for fileTransfer command type
l_step_param_name MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS('destFile',
'destTargetName',
'destTargetType',
'destType',
'destUsername',
'sourceFile',
'sourceTargetName',
'sourceTargetType',
'sourceType',
'sourceUsername');
--these are the additonal parameters that are being searched for in the
--JOB PARAM STEPS for remote_pipeOp command type
l_step_param_pipeOp MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS('destArgs',
'destCommand',
'sourceArgs',
'sourceCommand',
'sourceInput');
BEGIN
--here all the step parameters are stored in the list,
--MGMT_JOB_ENGINE.get_job_step_params search through all the job parameters
--in the JOB_STEP_PARAMS TABLE for the fileTransfer command type
l_job_step_param_list := MGMT_JOB_ENGINE.get_job_step_params(p_job_id,
p_execution_id,
p_step_name,
p_step_id,
p_all_params,
p_iterate_param,
p_iterate_param_index,
l_fill_encrypt_info,
l_encrypt_info);
--stores the searched value in the list as returned by search_params for
--fileTransfer command type
l_list := search_params(l_job_step_param_list,
l_step_param_name);
--checks if the either of sourceType or destType of the job parameter is set
--to file then it puts the corrsponding path of the file in the
--MGMT_JOB_AUDIT_HISTORY Table else it put null against sourceFile, destFile
--column in the MGMT_JOB_AUDIT_HISTORY Table for fileTransfer command type
--and additionally if the sourceFile and destFile is set to null then it
--searches additonal parameters for the remote_pipeOp command type
IF l_list(4).scalar_value IS NULL AND l_list(9).scalar_value IS NULL THEN
l_destFile := NULL;
l_sourceFile := NULL;
FOR i IN l_step_param_pipeOp.FIRST..l_step_param_pipeOp.LAST LOOP
l_list_pipeOp.extend(1);
l_list_pipeOp(i):= MGMT_JOB_PARAM_RECORD(l_step_param_pipeOp(i),
NULL,
NULL,
NULL);
END LOOP;
ELSE
l_destFile := l_list(1).scalar_value;
l_sourceFile := l_list(6).scalar_value;
-- searches for the remote_pipeOp parameters in the job step parameters
IF l_list(1).scalar_value IS NULL AND l_list(6).scalar_value IS NULL THEN
l_list_pipeOp := search_params(l_job_step_param_list,
l_step_param_pipeOp);
END IF;
END IF;
--stores the hostname in the list as returned by search_hostname
l_host_name := search_hostname(l_list(7).scalar_value,
l_list(8).scalar_value);
--inserts the valuses into the MGMT_JOB_AUDIT_HISTORY TABLE
insert_val_tables(l_list_pipeOp(5).scalar_value,--sourceInput
p_output_id,
NULL,
l_list(10).scalar_value,--sourceUsername
l_list(5).scalar_value,--destUsername
l_list_pipeOp(1).scalar_value,--destArgs
l_list_pipeOp(2).scalar_value,--destCommand
l_list_pipeOp(3).scalar_value,--sourceArgs
l_list_pipeOp(4).scalar_value,--sourceCommand
NULL,
l_host_name,
p_job_name,
p_job_owner,
p_job_type,
p_command_name,
p_step_id,
l_list(1).scalar_value,--destFile
l_list(6).scalar_value,--sourceFile
p_start_time,
l_list(2).scalar_value,--destTargetName
l_list(3).scalar_value,--destTargetType
l_list(7).scalar_value,--sourceTargetName
l_list(8).scalar_value);--sourceTargetType
RETURN 1;
END fileTransfer_params;
--Function fileTransfer_params END
--Procedure generate_job_audit_data(p_job_name, p_job_owner)
--This procedure takes in jobName and jobOwner and looks for the jobs of the
--specified jobName and jobOwner
PROCEDURE generate_job_audit_data_jname(p_job_name IN VARCHAR2,
p_job_owner IN VARCHAR2) IS
l_fun_stat NUMBER := 0;
--this cursor queries the data and stores the jobs with the specified job name
--and specified job owner with the condition that job status is not submiited
--and running and it looks only for the remoteOp, putFile, getFile, fileTransfer
--command type
CURSOR c_job_info IS
SELECT p.command_name,
j.job_name,
j.job_owner,
j.job_type,
h.start_time,
j.job_id,
h.execution_id,
h.step_name,
h.step_id,
p.all_params,
h.iterate_param,
h.iterate_param_index,
h.output_id
FROM MGMT_JOB j, MGMT_JOB_HISTORY h,
MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_EXECPLAN p
WHERE j.nested=0
AND j.job_name=p_job_name
AND j.job_owner=p_job_owner
AND e.job_id=j.job_id
AND e.job_type_id=p.job_type_id
AND e.execution_id=h.execution_id
AND h.step_name=p.step_name
AND h.step_type=1
AND h.step_status NOT IN (1, 2)
AND p.command_name IN ('remoteOp',
'putFile',
'getFile',
'fileTransfer');
BEGIN
--this loop looks into to the command type of the job and calls approriate
--function to store corresponding entries for that command type
FOR crec IN c_job_info LOOP
CASE
WHEN crec.command_name = 'remoteOp' THEN
l_fun_stat := remoteOp_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
WHEN crec.command_name = 'putFile' THEN
l_fun_stat := putFile_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
WHEN crec.command_name = 'getFile' THEN
l_fun_stat := getFile_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
WHEN crec.command_name = 'fileTransfer' THEN
l_fun_stat := fileTransfer_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
END CASE;
END LOOP;
END generate_job_audit_data_jname;
--generate_job_audit_data(p_job_name, p_job_owner) END
--Procedure generate_job_audit_data(p_job_type, p_start_time, p_end_time)
--This procedure takes in jobType and time interval and looks for jobs of the
--specified jobTypes which are submitted in given time interval
PROCEDURE generate_job_audit_data_jtype(p_job_type IN VARCHAR2,
p_start_time IN DATE,
p_end_time IN DATE) IS
l_fun_stat NUMBER := 0;
--this cursor queries the data and stores the jobs with the specified job type
--which are not in submitted and running state in the specified time interval
--that is between the start time and the end time and it looks only for the
--remoteOp, putFile, getFile, fileTransfer command type job only
CURSOR c_job_info IS
SELECT p.command_name,
j.job_name,
j.job_owner,
j.job_type,
h.start_time,
j.job_id,
h.execution_id,
h.step_name,
h.step_id,
p.all_params,
h.iterate_param,
h.iterate_param_index,
h.output_id
FROM MGMT_JOB j, MGMT_JOB_HISTORY h,
MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_EXECPLAN p
WHERE j.nested=0
AND j.job_type=p_job_type
AND e.job_id=j.job_id
AND j.job_type=p.job_type
AND e.execution_id=h.execution_id
AND h.step_name=p.step_name
AND h.step_type=1
AND h.step_status NOT IN (1, 2)
AND h.start_time BETWEEN p_start_time AND p_end_time
AND p.command_name IN ('remoteOp',
'putFile',
'getFile',
'fileTransfer');
BEGIN
--this loop looks into to the command type of the job and calls approriate
--function to store corresponding entries for that command type
FOR crec IN c_job_info LOOP
CASE
WHEN crec.command_name = 'remoteOp' THEN
l_fun_stat := remoteOp_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
WHEN crec.command_name = 'putFile' THEN
l_fun_stat := putFile_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
WHEN crec.command_name = 'getFile' THEN
l_fun_stat := getFile_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
WHEN crec.command_name = 'fileTransfer' THEN
l_fun_stat := fileTransfer_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
END CASE;
END LOOP;
END generate_job_audit_data_jtype;
--generate_job_audit_data(p_job_type, p_start_time, p_end_time) END
--PROCEDURE generate_job_audit_data(p_ref, p_username, p_start_time, p_end_time)
--This procedure accepts the ref, username and the time interval and finds the
--associated job submitted the that user. If the username is NULL then it looks
--for all the job between the specified time interval. p_ref is given just to
--overload the procedure its like a dummy variable
PROCEDURE generate_job_audit_data_juser(p_username IN VARCHAR2 DEFAULT NULL,
p_start_time IN DATE,
p_end_time IN DATE) IS
l_fun_stat NUMBER := 0;
--this cursor queries the data and stores the jobs with specified job owner or
--the em user which are not in submitted and running state in the specified time
--interval that is between the start time and the end time and it looks only for
--the remoteOp, putFile, getFile, fileTransfer command type job only
CURSOR c_job_info IS
SELECT p.command_name,
j.job_name,
j.job_owner,
j.job_type,
h.start_time,
j.job_id,
h.execution_id,
h.step_name,
h.step_id,
p.all_params,
h.iterate_param,
h.iterate_param_index,
h.output_id
FROM MGMT_JOB j, MGMT_JOB_HISTORY h,
MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_EXECPLAN p
WHERE j.nested=0
AND j.job_owner=p_username
AND j.job_id=e.job_id
AND j.job_type=p.job_type
AND e.execution_id=h.execution_id
AND h.step_name=p.step_name
AND h.step_type=1
AND h.step_status NOT IN (1, 2)
AND h.start_time BETWEEN p_start_time AND p_end_time
AND p.command_name IN ('remoteOp',
'putFile',
'getFile',
'fileTransfer');
BEGIN
--this loop looks into to the command type of the job and calls approriate
--function to store corresponding entries for that command type
FOR crec IN c_job_info LOOP
CASE
WHEN crec.command_name = 'remoteOp' THEN
l_fun_stat := remoteOp_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
WHEN crec.command_name = 'putFile' THEN
l_fun_stat := putFile_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
WHEN crec.command_name = 'getFile' THEN
l_fun_stat := getFile_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
WHEN crec.command_name = 'fileTransfer' THEN
l_fun_stat := fileTransfer_params(crec.command_name,
crec.job_name,
crec.job_owner,
crec.job_type,
crec.start_time,
crec.job_id,
crec.execution_id,
crec.step_name,
crec.step_id,
crec.all_params,
crec.iterate_param,
crec.iterate_param_index,
crec.output_id);
END CASE;
END LOOP;
END generate_job_audit_data_juser;
--generate_job_audit_data(p_ref, p_username, p_start_time, p_end_time) END
END MGMT_JOB_AUDIT_HISTORY_DATA;
/
show errors;