Home » RDBMS Server » Server Administration » Invalid Objects: SPACE_FREE & SPACE_AVAILABLE (RDBMS 10.2.0.4 win2003 x64)
|
|
|
|
| Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #349193 is a reply to message #348614] |
Fri, 19 September 2008 04:37   |
hristo
Messages: 258
Registered: May 2007
|
Senior Member |
|
|
Hi!
I found more of these, but for an ordinary user. So it was safe to drop them (the above objects).
I have one invalid object left. I ran the workaround found on metalink, that fixed one of the two objects but one remain.
note:414633.1
That fixed the package but not the package body:
SYSMAN.MGMT_JOB_UI
Line # = 72 Column # = 11 Error Text = PLS-00323: subprogram or cursor 'GET_LIBRARY_DATA' is declared in a package specification and must be defined in the package body
Line # = 576 Column # = 7 Error Text = PL/SQL: SQL Statement ignored
Line # = 620 Column # = 12 Error Text = PL/SQL: ORA-00902: invalid datatype
Line # = 629 Column # = 1 Error Text = PL/SQL: Item ignored
Line # = 644 Column # = 10 Error Text = PLS-00201: identifier 'JOBRUNTABLETYPE' must be declared
Line # = 1155 Column # = 7 Error Text = PL/SQL: SQL Statement ignored
Line # = 1189 Column # = 12 Error Text = PL/SQL: ORA-00902: invalid datatype
Line # = 1197 Column # = 1 Error Text = PL/SQL: Item ignored
Line # = 1217 Column # = 10 Error Text = PLS-00201: identifier 'JOBEXECTABLETYPE' must be declared
Im very new to PL/SQL...
Regards
Hristo
[Updated on: Fri, 19 September 2008 04:41] Report message to a moderator |
|
|
|
|
|
| Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #349222 is a reply to message #349197] |
Fri, 19 September 2008 06:30 |
hristo
Messages: 258
Registered: May 2007
|
Senior Member |
|
|
Hi!
Here is the code:
AS
---------------------------------------------
---------------------------------------------
-- Constants for URI use
URI_USE_CREATE CONSTANT NUMBER(2) := 7;
URI_USE_CREATE_LIKE CONSTANT NUMBER(2) := 8;
-- Constant for job step target paramger
PARAM_STEP_TARGET constant varchar2(32) := '%argetName%';
---------------------------------------------
--------------- helpers ------------------
-- Convert string array to delim separated string
FUNCTION concat_string_array ( p_arr SMP_EMD_STRING_ARRAY,
p_delim VARCHAR2 ) RETURN VARCHAR2 IS
l_concat VARCHAR2(32000) := '';
BEGIN
IF ( p_arr.count >= 1 ) THEN
l_concat := p_arr(1);
END IF;
FOR j IN 2..p_arr.count LOOP
l_concat := l_concat || p_delim || p_arr(j);
END LOOP;
RETURN l_concat;
END;
PROCEDURE add_to_array (
p_job_id VARCHAR2,
p_execution_id VARCHAR2,
p_job_name VARCHAR2,
p_job_owner VARCHAR2,
p_step_id NUMBER,
p_iterate_param VARCHAR2,
p_iterate_param_index NUMBER,
p_param_value VARCHAR2,
p_target_name_arr IN OUT SMP_EMD_STRING_ARRAY ) IS
l_target_name MGMT_TARGETS.TARGET_NAME%TYPE := '';
BEGIN
IF ( INSTR(p_param_value, '%') <= 0 ) THEN
-- Note: the value could be hard coded. Why: test? well known name?
l_target_name := p_param_value;
ELSE
l_target_name := MGMT_JOB_ENGINE.substitute_params(
p_job_id,
p_execution_id,
p_step_id,
p_iterate_param,
p_iterate_param_index,
p_job_name,
p_job_owner,
p_param_value );
END IF;
p_target_name_arr.extend(1);
p_target_name_arr(p_target_name_arr.count) := l_target_name;
END;
---------------------------------------------
------------ public functions ---------------
-- Get the targets for this step, if any
FUNCTION get_step_targets ( p_step_id NUMBER,
p_return_display_names BOOLEAN
) RETURN SMP_EMD_STRING_ARRAY IS
-- target arr info
l_target_name_arr SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
l_target_disp_name_arr SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
BEGIN
SELECT target_name, display_name
BULK COLLECT
INTO l_target_name_arr, l_target_disp_name_arr
FROM MGMT_JOB_STEP_TARGETS st,
MGMT_TARGETS t
WHERE st.target_guid = t.target_guid
AND st.step_id = p_step_id;
-- convert internal names to display names
IF p_return_display_names THEN
l_target_name_arr := l_target_disp_name_arr;
END IF;
RETURN l_target_name_arr;
END;
-- Get the targets for this step, if any
FUNCTION get_step_targets_orig ( p_step_id NUMBER,
p_return_display_names BOOLEAN
) RETURN SMP_EMD_STRING_ARRAY IS
-- target arr info
l_target_name_arr SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
-- step info for subst call
l_job_type MGMT_JOB_TYPE_INFO.job_type%TYPE;
l_job_id MGMT_JOB.job_id%TYPE;
l_exec_id MGMT_JOB_HISTORY.execution_id%TYPE;
l_step_name MGMT_JOB.job_name%TYPE;
l_iterate_param MGMT_JOB_HISTORY.iterate_param%TYPE;
l_iterate_param_index MGMT_JOB_HISTORY.iterate_param_index%TYPE;
-- display name temp
l_display_name MGMT_TARGETS.DISPLAY_NAME%TYPE := '';
l_job_name MGMT_JOB.job_name%TYPE;
l_job_owner MGMT_JOB.job_owner%TYPE;
BEGIN
SELECT job_id, execution_id, step_name, iterate_param, iterate_param_index
INTO l_job_id, l_exec_id, l_step_name, l_iterate_param, l_iterate_param_index
FROM MGMT_JOB_HISTORY
WHERE step_id = p_step_id;
SELECT job_type
INTO l_job_type
FROM MGMT_JOB
WHERE job_id = l_job_id;
SELECT job_name, job_owner INTO l_job_name, l_job_owner FROM
MGMT_JOB WHERE job_id=l_job_id;
FOR param IN (SELECT param_name, is_scalar, scalar_value, vector_value
FROM MGMT_JOB_STEP_PARAMS
WHERE
job_type=l_job_type AND
step_name=l_step_name AND
param_name like PARAM_STEP_TARGET)
LOOP
-- can have more than one e.g., srcTargetName, destTargetName
IF ( param.is_scalar=1 ) THEN
add_to_array(l_job_id,
l_exec_id,
l_job_name,
l_job_owner,
p_step_id,
l_iterate_param,
l_iterate_param_index,
param.scalar_value,
l_target_name_arr);
ELSE
FOR j IN 1..param.vector_value.count LOOP
add_to_array(l_job_id,
l_exec_id,
l_job_name,
l_job_owner,
p_step_id,
l_iterate_param,
l_iterate_param_index,
param.vector_value(j),
l_target_name_arr);
END LOOP;
END IF;
END LOOP;
-- convert internal names to display names
IF p_return_display_names THEN
FOR k IN 1..l_target_name_arr.count LOOP
SELECT target_name
INTO l_display_name
FROM MGMT_TARGETS
WHERE target_name = l_target_name_arr(k);
-- overwrite internal name with display name
l_target_name_arr(k) := l_display_name;
END LOOP;
END IF;
-- We're done, so reset parameters for this session
MGMT_JOB_ENGINE.reset_params();
RETURN l_target_name_arr;
END;
-- Get the targets for this step, if any
FUNCTION get_step_targets_str ( p_step_id NUMBER,
p_return_display_names BOOLEAN
) RETURN VARCHAR2 IS
BEGIN
RETURN concat_string_array (
get_step_targets ( p_step_id, p_return_display_names ),
', ' );
END;
-- Get the parameters for this job, filter as specified for this jobtype
PROCEDURE get_visible_params ( p_job_id RAW,
p_exec_id RAW,
p_params_out OUT CURSOR_TYPE) IS
l_job_type MGMT_JOB.job_type%TYPE;
l_show_params NUMBER;
l_specified NUMBER;
BEGIN
SELECT job_type
INTO l_job_type
FROM MGMT_JOB_EXEC_SUMMARY js, MGMT_JOB j
WHERE js.execution_id = p_exec_id
AND js.job_id = j.job_id;
dbms_output.put_line('l_job_type=' || l_job_type );
BEGIN
SELECT show_param
INTO l_show_params
FROM MGMT_JOB_TYPE_DISPLAY_INFO
WHERE job_type = l_job_type;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- default to true
l_show_params := 1;
END;
dbms_output.put_line('l_show_params=' || l_show_params );
-- is there at least one param_name specified?
SELECT count(param_name)
INTO l_specified
FROM MGMT_JOB_TYPE_DISPLAY_PARAM
WHERE job_type = l_job_type
AND rownum=1;
IF l_specified = 0 THEN
IF l_show_params = 0 THEN
-- hide all
OPEN p_params_out
FOR
SELECT 'p1' parameter_name, 'pt' parameter_type, 0 encrypted,
1 is_scalar, 'v1' scalar_value, NULL vector_value
, NULL large_value
FROM dual
WHERE rownum < 1;
ELSE
-- show all
OPEN p_params_out
FOR
SELECT parameter_name, encrypted,
parameter_type,
decode(encrypted, 0, scalar_value, '******') scalar_value,
vector_value, large_value
FROM MGMT_JOB_PARAMETER
WHERE execution_id = p_exec_id AND
job_id = p_job_id
ORDER BY parameter_name;
END IF;
ELSE
-- MGMT_JOB_TYPE_DISPLAY_PARAM.param_name has entries
IF l_show_params = 0 THEN
-- hide specified params
OPEN p_params_out
FOR
SELECT parameter_name, encrypted,
parameter_type,
decode(encrypted, 0, scalar_value, '******') scalar_value,
vector_value, large_value
FROM MGMT_JOB_PARAMETER
WHERE execution_id = p_exec_id AND
job_id = p_job_id AND
parameter_name NOT IN
( SELECT dp.param_name
FROM MGMT_JOB_TYPE_DISPLAY_PARAM dp
WHERE dp.job_type = l_job_type )
ORDER BY parameter_name;
ELSE
-- show specified params
OPEN p_params_out
FOR
SELECT jp.parameter_name, jp.encrypted,
jp.parameter_type,
decode(jp.encrypted, 0, jp.scalar_value, '******') scalar_value,
jp.vector_value, jp.large_value
FROM MGMT_JOB_PARAMETER jp, MGMT_JOB_TYPE_DISPLAY_PARAM dp
WHERE jp.execution_id = p_exec_id AND
jp.job_id = p_job_id AND
jp.parameter_name = dp.param_name AND
dp.job_type = l_job_type
ORDER BY dp.param_order;
END IF;
END IF;
END;
-- Get the URI for this uri_use
-- see emSDK/job/dtd/UriSource.java for uri_use constants
FUNCTION get_display_uri ( p_job_type IN VARCHAR2,
p_uri_use IN NUMBER
) RETURN VARCHAR2 IS
l_uri MGMT_JOB_TYPE_URI_INFO.uri%TYPE;
BEGIN
IF p_uri_use IN (URI_USE_CREATE, URI_USE_CREATE_LIKE)
AND p_job_type IN ('OSCommand', 'SQLScript', 'RMANScript') THEN
IF p_job_type='RMANScript' THEN
RETURN 'database/rman/jobScheduleDef';
ELSE
RETURN 'jobs/jobsCreateDef';
END IF;
END IF;
BEGIN
SELECT uri
INTO l_uri
FROM MGMT_JOB_TYPE_URI_INFO
WHERE job_type = p_job_type
AND uri_use = p_uri_use;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
RETURN l_uri;
END;
-- --------------------------------------------------------------------------------
-- FUNCTION: one_run_query() - returns the query for obtaining one job run or
-- occurrence. Statement is dynamically generated given input filters.
-- --------------------------------------------------------------------------------
FUNCTION one_run_query(
job_name_in IN VARCHAR2,
job_type_in IN VARCHAR2,
job_owner_in IN VARCHAR2,
job_status_in IN INTEGER,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
target_guid_in IN RAW,
timeframe_in IN INTEGER,
active_flag_in IN INTEGER,
sort_by_in IN VARCHAR2,
sort_order_in IN VARCHAR2,
show_forMembers_in IN INTEGER)
RETURN VARCHAR2 IS
run_sql VARCHAR(10000) := '';
add_tgt_tables_to_from_clause NUMBER := 0;
select_clause VARCHAR(2000) := 'SELECT DISTINCT JobSumm.job_id, ' ||
' Job.job_name, Job.job_type, Job.job_owner, ' ||
-- ' TO_CHAR(JobSumm.scheduled_time, ''YYYY-MM-DD HH24:MI:SS'') start_time_str,' ||
' JobSumm.scheduled_time, JobSumm.timezone_region, ' ||
' JobSched.frequency_code ';
from_clause VARCHAR(2000) := ' FROM MGMT_JOB Job, ' ||
' MGMT_JOB_EXEC_SUMMARY JobSumm,' ||
' MGMT_JOB_SCHEDULE JobSched ';
tgt_tables VARCHAR(100) := ' , MGMT_JOB_TARGET JobTargets,' ||
' MGMT_TARGETS Targets ';
where_clause VARCHAR(3000) := ' WHERE JobSumm.job_id=Job.job_id' ||
' and Job.is_library = 0' ||
' and JobSched.schedule_id = Job.schedule_id ';
where_targets VARCHAR(100) := ' and JobTargets.target_guid = Targets.target_guid ';
BEGIN
-- Add job name filter to where clause
--
-- DBMS_OUTPUT.PUT_LINE('job_name_in = '|| job_name_in);
IF job_name_in IS NOT NULL
THEN
where_clause := where_clause || ' and Job.job_name like ''' || job_name_in || ''' ';
END IF;
-- Add job type filter to where clause
--
IF job_type_in IS NOT NULL
THEN
where_clause := where_clause || ' and Job.job_type like ''' || job_type_in || '''';
END IF;
-- Add job owner filter to where clause
--
IF job_owner_in IS NOT NULL
THEN
where_clause := where_clause || ' and Job.job_owner like ''' || job_owner_in || '''';
END IF;
DBMS_OUTPUT.PUT_LINE('show_forMembers_in = '|| show_forMembers_in);
IF show_forMembers_in = 0
THEN
-- Add target type filter to where clause
--
IF target_type_in IS NOT NULL
THEN
where_clause := where_clause || ' and Job.job_id in ( select job_id from MGMT_JOB_TARGET JobTargets, MGMT_TARGETS Targets ' ||
'where JobTargets.target_guid = Targets.target_guid ' ||
'and Targets.target_type = ''' || target_type_in || '''' ||
') ';
add_tgt_tables_to_from_clause := 1;
END IF;
-- Add target name filter to where clause
--
IF target_name_in IS NOT NULL
THEN
where_clause := where_clause || ' and Job.job_id in ( select job_id from MGMT_JOB_TARGET JobTargets, MGMT_TARGETS Targets ' ||
'where JobTargets.target_guid = Targets.target_guid ' ||
'and Targets.target_name like ''' || target_name_in || '''' ||
') ';
add_tgt_tables_to_from_clause := 1;
END IF;
-- Add target GUID filter to where clause
--
IF target_guid_in IS NOT NULL
THEN
where_clause := where_clause || ' and Job.job_id in ( select job_id from MGMT_JOB_TARGET JobTargets, MGMT_TARGETS Targets ' ||
'where JobTargets.target_guid = Targets.target_guid ' ||
'and Targets.target_guid = ''' || target_guid_in || '''' ||
') ';
add_tgt_tables_to_from_clause := 1;
END IF;
-- Get runs for MEMBERS of a group (as opposed to runs directly submitted to group)
ELSE
from_clause := from_clause || ', MGMT_TARGET_MEMBERSHIPS members, MGMT_JOB_TARGET JobTargets ';
where_clause := where_clause ||
' and JobTargets.execution_id = jobSumm.execution_id ' ||
' and exists (SELECT member_target_guid ' ||
' FROM mgmt$group_flat_memberships members, ' ||
' MGMT_JOB_TARGET JobTargets ' ||
' WHERE member_target_guid = JobTargets.target_guid ' ||
' AND composite_target_guid = HEXTORAW(''' || target_guid_in || ''') )' ||
' AND members.member_target_guid = JobTargets.target_guid ' ||
' AND members.composite_target_guid = HEXTORAW(''' || target_guid_in || ''')';
END IF;
-- Add timeframe filter to where clause
--
IF timeframe_in IS NOT NULL AND timeframe_in != 0
THEN
where_clause := where_clause || ' and JobSumm.scheduled_time > SYSDATE - ' || timeframe_in;
END IF;
-- Add job status filter to where clause
--
IF job_status_in IS NOT NULL AND job_status_in != 0
THEN
-- All status codes for status-buckets are negative numbers. With status
-- buckets many status codes are "OR"ed.
--
IF job_status_in < 0
THEN
-- Problem status bucket
IF job_status_in = -1
THEN
where_clause := where_clause || ' and Job.job_id in ' ||
' (select job_id from MGMT_JOB_EXEC_SUMMARY JobSumm ' ||
' where (JobSumm.status = ' || MGMT_JOBS.ABORTED_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.FAILED_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.STOPPED_STATUS || ') ' ||
') ';
END IF;
-- Active status bucket
IF job_status_in = -2
THEN
where_clause := where_clause || ' and Job.job_id in ' ||
' (select job_id from MGMT_JOB_EXEC_SUMMARY JobSumm ' ||
' where (JobSumm.status = ' || MGMT_JOBS.SCHEDULED_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.EXECUTING_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.AGENTDOWN_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_LOCK_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_EVENT_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS || ') ' ||
') ';
END IF;
-- Suspended status bucket
IF job_status_in = -3
THEN
where_clause := where_clause || ' and Job.job_id in ' ||
' (select job_id from MGMT_JOB_EXEC_SUMMARY JobSumm ' ||
'where (JobSumm.status = ' || MGMT_JOBS.AGENTDOWN_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_LOCK_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_EVENT_STATUS || ' OR ' ||
'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS || ') '||
') ';
END IF;
ELSE
where_clause := where_clause || ' and Job.job_id in ' ||
' (select job_id from MGMT_JOB_EXEC_SUMMARY JobSumm ' ||
'where JobSumm.status = ' || job_status_in || ')';
END IF;
END IF;
-- If a target clause is necessary, add the target tables MGMT_JOB_TARGETS and MGMT_TARGETS to the
-- FROM tables list and add the join to the where clause. ARU jobs (which do not have targets)
-- do not get returned if the join is there and there are ONLY update ARU job runs to be returned.
--
IF add_tgt_tables_to_from_clause = 1
THEN
from_clause := from_clause || tgt_tables;
where_clause := where_clause || where_targets;
END IF;
-- Add sorting to where clause
--
IF sort_by_in IS NOT NULL
THEN
where_clause := where_clause || ' order by ' || sort_by_in || ' ' || sort_order_in;
END IF;
run_sql := run_sql || select_clause || from_clause || where_clause;
return run_sql;
END one_run_query;
-- --------------------------------------------------------------------------------
-- get_run_data() - returns data for one job run or occurrence (for Job Console UI).
-- data is returned via a cursor output parameter
-- --------------------------------------------------------------------------------
PROCEDURE get_run_data(
job_name_in IN VARCHAR2,
job_type_in IN VARCHAR2,
job_owner_in IN VARCHAR2,
job_status_in IN INTEGER,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
target_guid_in IN VARCHAR2,
timeframe_in IN INTEGER,
active_flag_in IN INTEGER,
current_user_in IN VARCHAR2,
show_forMembers_in IN INTEGER,
ignore_access_flag_in IN INTEGER,
sort_by_in IN VARCHAR2,
sort_order_in IN VARCHAR2,
run_tbl_data_out OUT CURSOR_TYPE)
IS
BEGIN
OPEN run_tbl_data_out FOR
SELECT job_id,
execution_id,
job_name,
job_type,
owner,
target_guid,
target_name,
target_type,
target_count,
group_flag,
scheduled_time,
-- scheduled_start,
timezone_region,
next_scheduled,
repeating,
job_access,
stat_bucket_problem,
stat_bucket_suspended,
stat_bucket_active,
stat_scheduled,
stat_executing,
stat_aborted,
stat_failed,
stat_completed,
stat_agent_down,
stat_stopped,
stat_suspended,
stat_suspended_lock,
stat_suspended_event,
stat_suspended_blackout
FROM TABLE(CAST(get_runs_as_table(job_name_in,
job_type_in,
job_owner_in,
job_status_in,
target_name_in,
target_type_in,
target_guid_in,
timeframe_in,
active_flag_in,
current_user_in,
show_forMembers_in,
ignore_access_flag_in,
sort_by_in,
sort_order_in)
AS jobRunTableType));
END get_run_data;
-- --------------------------------------------------------------------------------
-- get_runs_as_table() - Calls get_run_data() which gathers the job run data
-- and returns a table type. The table type is then returned to the java caller
-- as a cursor.
-- --------------------------------------------------------------------------------
FUNCTION get_runs_as_table(
job_name_in IN VARCHAR2,
job_type_in IN VARCHAR2,
job_owner_in IN VARCHAR2,
job_status_in IN INTEGER,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
target_guid_in IN VARCHAR2,
timeframe_in IN INTEGER,
active_flag_in IN INTEGER,
current_user_in IN VARCHAR2,
show_forMembers_in IN INTEGER,
ignore_access_flag_in IN INTEGER,
sort_by_in IN VARCHAR2,
sort_order_in IN VARCHAR2)
RETURN jobRunTableType IS
run_sql VARCHAR2(3000) := '';
recCount NUMBER := 0;
run_data_cursor CURSOR_TYPE;
status_cursor CURSOR_TYPE;
problem_bucket NUMBER := 0;
suspended_bucket NUMBER := 0;
active_bucket NUMBER := 0;
status_code NUMBER := 0;
status_count NUMBER := 0;
target_count NUMBER := 0;
target_name VARCHAR2(256) := '';
target_type VARCHAR2(128) := '';
internal_type VARCHAR2(128) := '';
group_flag NUMBER(1) := 0;
target_guid RAW(16);
execution_id RAW(16);
tmp_current_user VARCHAR2(32);
-- job_access VARCHAR(30);
run_row runBasicRowType := runBasicRowType(NULL, NULL, NULL,
NULL, NULL, NULL, 0);
detailed_row runDetailedRowType := runDetailedRowType (NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, 0,
0, NULL, NULL, NULL,
0, 0, 0, 0,
0, 0, 0, 0,
0, 0, 0, 0,
0, 0, 0, 0);
run_table jobRunTableType := jobRunTableType();
CURSOR getStatusCodeCursor (job_id_in RAW,
scheduled_time_in DATE)
IS
SELECT status, count (*)
FROM mgmt_job_exec_summary
WHERE job_id = job_id_in
AND scheduled_time = scheduled_time_in
GROUP BY status;
CURSOR getTargetInfoCursor (job_id_in RAW)
IS
SELECT t.target_guid, t.display_name, t.type_display_name, t.target_type,
NVL( (select property_value from mgmt_type_properties tp
where tp.property_name = 'is_group'
and tp.target_type = t.target_type and rownum = 1
),
0) is_group
FROM mgmt_job_target j,
mgmt_targets t,
mgmt_target_memberships m
WHERE j.job_id = job_id_in
--- Exclude executions that haven't run yet
and j.execution_id != '0000000000000000'
AND j.target_guid = t.target_guid
GROUP BY t.target_guid, t.display_name, t.type_display_name, t.target_type;
-- CURSOR getJobAccess (job_id_in RAW)
-- IS
-- SELECT nvl(priv_name, 'NONE')
-- FROM mgmt_priv_grants
-- where guid = job_id_in;
-- ------------------------------------------------------------------------------------
-- Cursor for retrieveal of distinct run data (minus status and target info)
-- ------------------------------------------------------------------------------------
BEGIN
run_sql := one_run_query(job_name_in, job_type_in, job_owner_in, job_status_in,
target_name_in, target_type_in, target_guid_in,
timeframe_in, active_flag_in, sort_by_in, sort_order_in,
show_forMembers_in);
DBMS_OUTPUT.PUT_LINE(SUBSTR(run_sql, 0, 254));
DBMS_OUTPUT.PUT_LINE(SUBSTR(run_sql, 255, 254));
DBMS_OUTPUT.PUT_LINE(SUBSTR(run_sql, 509, 254));
DBMS_OUTPUT.PUT_LINE(SUBSTR(run_sql, 763, 254));
DBMS_OUTPUT.PUT_LINE(SUBSTR(run_sql, 1017, 254));
DBMS_OUTPUT.PUT_LINE('IGNORE ACCESS FLAG = ' || ignore_access_flag_in);
IF ignore_access_flag_in = 1
THEN
MGMT_USER.enter_super_user_mode (tmp_current_user );
END IF;
OPEN run_data_cursor FOR run_sql;
LOOP
FETCH run_data_cursor INTO run_row.job_id,
run_row.job_name, run_row.job_type,
run_row.owner, run_row.scheduled_time,
run_row.timezone_region,
run_row.repeating;
EXIT WHEN run_data_cursor%NOTFOUND;
run_table.EXTEND;
detailed_row.job_id := run_row.job_id;
detailed_row.job_name := run_row.job_name;
detailed_row.job_type := run_row.job_type;
detailed_row.owner := run_row.owner;
detailed_row.repeating := run_row.repeating;
detailed_row.scheduled_time := run_row.scheduled_time;
detailed_row.timezone_region := run_row.timezone_region;
-- Get one execution id from the job
--
SELECT JobSumm.execution_id into execution_id
FROM mgmt_job_exec_summary JobSumm
WHERE JobSumm.job_id = detailed_row.job_id
AND JobSumm.scheduled_time = detailed_row.scheduled_time
AND rownum = 1;
detailed_row.execution_id := execution_id;
-- Job Access
--
-- IF current_user_in = detailed_row.owner
-- THEN
-- detailed_row.job_access := 'FULL_JOB';
-- ELSE
-- WHAT ABOUT CHECKING FOR THE CURRENT USER BEING A SUPER-USER?
-- IF SO DOES THAT IMPLIES VIEW_JOB FOR ALL JOBS.
-- OPEN getJobAccess (detailed_row.job_id);
-- FETCH getJobAccess INTO job_access;
-- detailed_row.access := job_access;
-- CLOSE getJobAccess;
-- END IF;
-- Target cursor
--
OPEN getTargetInfoCursor (detailed_row.job_id);
LOOP
FETCH getTargetInfoCursor INTO target_guid, target_name, target_type, internal_type, group_flag;
EXIT WHEN getTargetInfoCursor%NOTFOUND;
target_count := target_count + 1;
detailed_row.target_type := target_type;
-- Only get the target name, type and guid for the first target. If there
-- are more than one targets in the job the UI shows the count. The link can
-- take the user to all executions at which time the targets can be seen.
IF target_count = 1
THEN
detailed_row.target_name := target_name;
detailed_row.target_type := target_type;
detailed_row.target_guid := target_guid;
detailed_row.group_flag := group_flag;
END IF;
END LOOP;
detailed_row.target_count := target_count;
CLOSE getTargetInfoCursor;
OPEN getStatusCodeCursor (detailed_row.job_id, detailed_row.scheduled_time);
LOOP
-- Status cursor
--
FETCH getStatusCodeCursor INTO status_code, status_count;
EXIT WHEN getStatusCodeCursor%NOTFOUND;
-- Scheduled
--
IF status_code = MGMT_JOBS.SCHEDULED_STATUS
THEN
detailed_row.stat_scheduled := status_count;
END IF;
-- Executing (running)
--
IF status_code = MGMT_JOBS.EXECUTING_STATUS
THEN
detailed_row.stat_executing := status_count;
active_bucket := active_bucket + status_count;
END IF;
-- Aborted
--
IF status_code = MGMT_JOBS.ABORTED_STATUS
THEN
detailed_row.stat_aborted := status_count;
problem_bucket := problem_bucket + status_count;
END IF;
-- Failed
--
IF status_code = MGMT_JOBS.FAILED_STATUS
THEN
detailed_row.stat_failed := status_count;
problem_bucket := problem_bucket + status_count;
END IF;
-- Completed
--
IF status_code = MGMT_JOBS.COMPLETED_STATUS
THEN
detailed_row.stat_completed := status_count;
END IF;
-- Agent Down
--
IF status_code = MGMT_JOBS.AGENTDOWN_STATUS
THEN
detailed_row.stat_agent_down := status_count;
suspended_bucket := suspended_bucket + status_count;
END IF;
-- Stopped
--
IF status_code = MGMT_JOBS.STOPPED_STATUS
THEN
detailed_row.stat_stopped := status_count;
problem_bucket := problem_bucket + status_count;
END IF;
-- Suspended
--
IF status_code = MGMT_JOBS.SUSPENDED_STATUS
THEN
detailed_row.stat_suspended := status_count;
suspended_bucket := suspended_bucket + status_count;
END IF;
-- Suspended - lock
--
IF status_code = MGMT_JOBS.SUSPENDED_LOCK_STATUS
THEN
detailed_row.stat_suspended_lock := status_count;
suspended_bucket := suspended_bucket + status_count;
END IF;
-- Suspended - event (resource unavailable)
--
IF status_code = MGMT_JOBS.SUSPENDED_EVENT_STATUS
THEN
detailed_row.stat_suspended_event := status_count;
suspended_bucket := suspended_bucket + status_count;
END IF;
-- Suspended - blackout
--
IF status_code = MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS
THEN
detailed_row.stat_suspended_blackout := status_count;
suspended_bucket := suspended_bucket + status_count;
END IF;
-- TO DO: Add support for the following status codes when added to the
-- backend (also add to detailed_row reset code):
--
-- RESTARTED status (add to active bucket as well)
-- INACTIVE status
-- FAILED_INITIALIZATION status (add to problem bucket as well)
--
END LOOP;
CLOSE getStatusCodeCursor;
-- Set the status bucket values into the return table row
--
detailed_row.stat_bucket_problem := problem_bucket;
detailed_row.stat_bucket_suspended := suspended_bucket;
detailed_row.stat_bucket_active := active_bucket;
-- Set the run record into the return table
--
recCount := recCount + 1;
run_table(recCount) := detailed_row;
-- Reset values for next Job Run
active_bucket := 0;
suspended_bucket := 0;
problem_bucket := 0;
detailed_row.stat_failed := 0;
detailed_row.stat_completed := 0;
detailed_row.stat_scheduled := 0;
detailed_row.stat_executing := 0;
detailed_row.stat_aborted := 0;
detailed_row.stat_agent_down := 0;
detailed_row.stat_stopped := 0;
detailed_row.stat_suspended := 0;
detailed_row.stat_suspended_blackout := 0;
detailed_row.stat_suspended_event := 0;
detailed_row.stat_suspended_lock := 0;
detailed_row.stat_bucket_problem := 0;
detailed_row.stat_bucket_suspended := 0;
detailed_row.stat_bucket_active := 0;
target_count := 0;
detailed_row.group_flag := 0;
detailed_row.target_name := NULL;
detailed_row.target_type := NULL;
detailed_row.target_guid := NULL;
detailed_row.target_count := 0;
detailed_row.scheduled_time := NULL;
detailed_row.next_scheduled := NULL;
detailed_row.timezone_region := NULL;
-- detailed_row.job_access := 0;
END LOOP;
-- Revert to actual current user if this query was executed as a super-user
--
IF ignore_access_flag_in = 1
THEN
MGMT_USER.leave_super_user_mode (tmp_current_user );
END IF;
CLOSE run_data_cursor;
RETURN run_table;
END get_runs_as_table;
FUNCTION exec_query (job_name_in IN VARCHAR2,
job_type_in IN VARCHAR2,
job_owner_in IN VARCHAR2,
-- job_status_in IN INTEGER,
status_clause_in IN VARCHAR2,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
target_guid_in IN RAW,
-- target_clause_in IN VARCHAR2,
timeframe_in IN INTEGER,
-- for_members_in IN VARCHAR2,
for_members_clause_in IN VARCHAR2,
sort_by_in IN VARCHAR2,
sort_order_in IN VARCHAR2,
is_group_flag_in IN INTEGER,
show_sys_jobs_in IN INTEGER
)
RETURN VARCHAR2 IS
exec_sql VARCHAR(5000) := '';
select_clause VARCHAR(1000) := 'SELECT Job.job_type, Job.job_name, ' ||
' Job.job_owner, ' ||
' JobSumm.scheduled_time, JobSumm.timezone_region, ' ||
' JobSumm.status, ' ||
' JobSumm.job_id, JobSumm.execution_id, ' ||
' JobSched.frequency_code ';
from_clause VARCHAR(500) := ' FROM MGMT_JOB Job, MGMT_JOB_EXEC_SUMMARY JobSumm, MGMT_JOB_SCHEDULE JobSched ';
-- || ', MGMT_JOB_TARGET JobTarg ';
where_clause VARCHAR(3000) := ' WHERE JobSumm.job_id = Job.job_id ' ||
' AND Job.schedule_id = JobSched.schedule_id ' ||
' AND Job.is_library = 0';
where_clause_nonGroup VARCHAR(200) := ' AND JobSumm.execution_id = JobTarg.execution_id ';
target_clause VARCHAR (1000) := '';
BEGIN
-- Add execution join to where clause for non-groups
-- If it is a group the where clause execution joins differ depending on selection
-- against the group itself vs. members
--
-- IF is_group_flag_in = 0
-- THEN
-- where_clause := where_clause || where_clause_nonGroup;
-- END IF;
-- Add system job filter to where clause
--
IF show_sys_jobs_in = 0
THEN
where_clause := where_clause || ' AND Job.system_job = 0 ';
ELSE
where_clause := where_clause || ' AND Job.system_job = 1 ';
END IF;
-- Add job name filter to where clause
--
IF job_name_in IS NOT NULL
THEN
where_clause := where_clause || ' and Job.job_name like ''' || job_name_in || ''' ';
END IF;
-- Add job type filter to where clause
--
IF job_type_in IS NOT NULL
THEN
where_clause := where_clause || ' and Job.job_type = ''' || job_type_in || '''';
END IF;
-- Add job owner filter to where clause
--
IF job_owner_in IS NOT NULL
THEN
where_clause := where_clause || ' and Job.job_owner like ''' || job_owner_in || '''';
END IF;
-- Add target clause
--
IF target_name_in IS NOT NULL or target_type_in IS NOT NULL
THEN
-- This is the targets clause for non-group targets. It can take a target name
-- and/or type
IF is_group_flag_in = 0
THEN
target_clause := target_clause || ' AND JobSumm.EXECUTION_ID IN ' ||
' ( SELECT EXECUTION_ID ' ||
' FROM MGMT_JOB_TARGET jobTgt, MGMT_TARGETS tgt ' ||
' WHERE jobTgt.target_guid = tgt.target_guid ';
IF target_name_in IS NOT NULL
THEN
target_clause := target_clause || ' AND tgt.display_name like ''' || target_name_in || '''';
END IF;
IF target_type_in IS NOT NULL
THEN
target_clause := target_clause || ' AND tgt.target_type = ''' || target_type_in || '''';
END IF;
target_clause := target_clause || ')';
ELSE -- is Group = 1
IF for_members_clause_in IS NULL
THEN
from_clause := from_clause || ', MGMT_JOB_TARGET JobTarg ';
target_clause := target_clause || ' AND JobTarg.execution_id = ''' || MGMT_JOB_ENGINE.NO_EXECUTION || '''' ||
' AND JobSumm.job_id = JobTarg.job_id ' ||
' AND JobTarg.target_guid = HEXTORAW(''' || target_guid_in || ''')';
END IF;
END IF;
-- Add the target clause to the where clause
--
where_clause := where_clause || target_clause;
END IF;
-- Add status clause
--
IF status_clause_in IS NOT NULL
THEN
where_clause := where_clause || status_clause_in;
END IF;
-- Add forMembers clause
-- (for when coming from "Job Group Members" link in Group job activity table link)
-- need to add mgmt_target_memberships to from clause to join with that table
--
IF for_members_clause_in IS NOT NULL
THEN
from_clause := from_clause || ' , MGMT_TARGET_MEMBERSHIPS members, MGMT_JOB_TARGET JobTarg ';
where_clause := where_clause || for_members_clause_in;
END IF;
-- Add timeframe filter to where clause
--
IF timeframe_in IS NOT NULL AND timeframe_in != 0
THEN
where_clause := where_clause || ' and JobSumm.scheduled_time > SYSDATE - ' || timeframe_in;
END IF;
-- Add sorting to statement
--
IF sort_by_in IS NOT NULL
THEN
where_clause := where_clause || ' order by ' || sort_by_in || ' ' || sort_order_in;
END IF;
exec_sql := exec_sql || select_clause || from_clause || where_clause;
return exec_sql;
END;
-- --------------------------------------------------------------------------------
-- get_exec_data() - returns data for job executions
-- data is returned via a cursor output parameter
-- --------------------------------------------------------------------------------
PROCEDURE get_exec_data(
job_name_in IN VARCHAR2,
job_type_in IN VARCHAR2,
job_owner_in IN VARCHAR2,
-- job_status_in IN INTEGER,
status_clause_in IN VARCHAR2,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
target_guid_in IN RAW,
-- target_clause_in IN VARCHAR2,
timeframe_in IN INTEGER,
show_forMembers_in IN INTEGER,
ignore_access_flag_in IN INTEGER,
for_members_clause_in IN VARCHAR2,
sort_by_in IN VARCHAR2,
sort_order_in IN VARCHAR2,
is_group_flag_in IN INTEGER,
show_sys_jobs_in IN INTEGER,
exec_tbl_data_out OUT CURSOR_TYPE)
IS
BEGIN
OPEN exec_tbl_data_out FOR
SELECT job_id,
execution_id,
job_name,
job_type,
owner,
target_guid,
target_name,
target_type,
target_count,
-- submitted_to_name,
-- submitted_to_type,
-- submitted_to_guid,
-- submitted_to_count,
scheduled_time,
timezone_region,
repeating,
status
FROM TABLE(CAST(get_execs_as_table(job_name_in,
job_type_in,
job_owner_in,
-- job_status_in,
status_clause_in,
target_name_in,
target_type_in,
target_guid_in,
-- target_clause_in,
timeframe_in,
show_forMembers_in,
ignore_access_flag_in,
for_members_clause_in,
sort_by_in,
sort_order_in,
is_group_flag_in,
show_sys_jobs_in)
AS jobExecTableType));
END get_exec_data;
-- --------------------------------------------------------------------------------
-- get_execs_as_table() - Cals get_exec_data() which gathers the job execution data
-- and returns a table type.
-- --------------------------------------------------------------------------------
FUNCTION get_execs_as_table(
job_name_in IN VARCHAR2,
job_type_in IN VARCHAR2,
job_owner_in IN VARCHAR2,
-- job_status_in IN INTEGER,
status_clause_in IN VARCHAR2,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
target_guid_in IN RAW,
-- target_clause_in IN VARCHAR2,
timeframe_in IN INTEGER,
show_forMembers_in IN INTEGER,
ignore_access_flag_in IN INTEGER,
-- group_name_in IN VARCHAR2,
-- group_type_in IN VARCHAR2,
for_members_clause_in IN VARCHAR2,
sort_by_in IN VARCHAR2,
sort_order_in IN VARCHAR2,
is_group_flag_in IN INTEGER,
show_sys_jobs_in IN INTEGER)
RETURN jobExecTableType
IS
exec_sql VARCHAR2(3000) := '';
recCount NUMBER := 0;
exec_data_cursor CURSOR_TYPE;
target_count NUMBER := 0;
target_name VARCHAR2(256) := '';
target_type VARCHAR2(128) := '';
target_guid RAW(16);
tmp_current_user VARCHAR2(32);
is_group NUMBER := 0;
exec_row executionRowType := executionRowType (NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
0, 0, NULL, NULL,
NULL, 0);
exec_table jobExecTableType := jobExecTableType();
CURSOR getTargetInfoCursor (job_id_in RAW, execution_id_in RAW)
IS
SELECT jobTarget.target_guid, Targets.display_name, Targets.type_display_name
FROM mgmt_job_target jobTarget, mgmt_targets Targets
WHERE jobTarget.job_id = job_id_in
AND jobTarget.execution_id = execution_id_in
AND jobTarget.target_guid = Targets.target_guid;
-- CURSOR getExecTargetSubmittedToCursor (job_id_in RAW)
-- IS
-- SELECT Targets.target_guid, Targets.display_name, Targets.type_display_name
-- FROM MGMT_JOB_TARGET jobTargets,
-- MGMT_TARGETS Targets,
-- MGMT_TARGET_MEMBERSHIPS tm
-- WHERE jobTargets.job_id = job_id_in
-- AND jobTargets.execution_id = '0000000000000000'
-- AND jobTargets.target_guid = Targets.target_guid
-- GROUP BY Targets.target_guid, Targets.display_name, Targets.type_display_name;
BEGIN
exec_sql := exec_query(job_name_in, job_type_in, job_owner_in, status_clause_in,
target_name_in, target_type_in, target_guid_in,
timeframe_in, for_members_clause_in,
sort_by_in, sort_order_in, is_group_flag_in, show_sys_jobs_in);
DBMS_OUTPUT.PUT_LINE('EXECUTIONS QUERY -----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(SUBSTR(exec_sql, 0, 254));
DBMS_OUTPUT.PUT_LINE(SUBSTR(exec_sql, 255, 254));
DBMS_OUTPUT.PUT_LINE(SUBSTR(exec_sql, 509, 254));
DBMS_OUTPUT.PUT_LINE(SUBSTR(exec_sql, 763, 254));
DBMS_OUTPUT.PUT_LINE('IGNORE ACCESS FLAG = ' || ignore_access_flag_in);
IF ignore_access_flag_in = 1
THEN
MGMT_USER.enter_super_user_mode (tmp_current_user );
END IF;
OPEN exec_data_cursor FOR exec_sql;
LOOP
FETCH exec_data_cursor INTO exec_row.job_type, exec_row.job_name,
exec_row.owner, exec_row.scheduled_time,
exec_row.timezone_region, exec_row.status,
exec_row.job_id, exec_row.execution_id,
exec_row.repeating;
EXIT WHEN exec_data_cursor%NOTFOUND;
exec_table.EXTEND;
-- Target cursor (loop through records and obtain target info)
--
OPEN getTargetInfoCursor (exec_row.job_id, exec_row.execution_id);
LOOP
FETCH getTargetInfoCursor INTO target_guid, target_name, target_type;
EXIT WHEN getTargetInfoCursor%NOTFOUND;
target_count := target_count + 1;
exec_row.target_type := target_type;
-- Only get the target name, type and guid for the first target. If there
-- are more than one targets in the job the UI shows the count. The link can
-- take the user to all executions at which time the targets can be seen.
-- IN THE EXEC JAVA CODE IT SETS THE NAME, TYPE, GUID EACH ROW RETURNED - ONLY FIRST (IF 1) OR LAST IS USED
-- IF target_count = 1
-- THEN
exec_row.target_name := target_name;
exec_row.target_type := target_type;
exec_row.target_guid := target_guid;
-- END IF;
END LOOP;
exec_row.target_count := target_count;
CLOSE getTargetInfoCursor;
-- Clear for re-use in submittedTo cursor
--
target_count := 0;
target_name := '';
target_type := '';
target_guid := '';
-- Set the execution record into the return table
--
recCount := recCount + 1;
exec_table(recCount) := exec_row;
target_count := 0;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Record Count: ' || recCount);
-- Revert to actual current user if this query was executed as a super-user
--
IF ignore_access_flag_in = 1
THEN
MGMT_USER.leave_super_user_mode (tmp_current_user );
END IF;
CLOSE exec_data_cursor;
RETURN exec_table;
END get_execs_as_table;
------------------------------------------------
-- Job Status Intg -> Target Home Pages Calls --
------------------------------------------------
PROCEDURE get_status_rollup_counts( p_target_name IN VARCHAR2,
p_target_type IN VARCHAR2,
p_for_composite_members IN NUMBER DEFAULT 0,
p_ignore_access IN NUMBER DEFAULT 1,
p_timeframe IN NUMBER DEFAULT 7,
p_status_cursor OUT CURSOR_TYPE )
IS
l_is_group MGMT_TARGETS.IS_GROUP%TYPE := 0;
l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
l_tmp_current_user VARCHAR2(32);
BEGIN
-- Not composite members
IF ( p_for_composite_members = 0 ) THEN
-- -------------------------------------------------------- --
-- Single Target Home Page or Group Home Page Group Column --
-- Show all jobs regardless of access for the job as long --
-- as user can VIEW the target --
-- -------------------------------------------------------- --
-- Target name present: single target or composite target
IF ( p_target_name IS NOT NULL AND LENGTH( p_target_name ) > 0 ) THEN
-- !!! Enter super user mode to leave VPD layer !!! --
IF ( p_ignore_access = 1 ) THEN
MGMT_USER.ENTER_SUPER_USER_MODE( l_tmp_current_user );
END IF ;
-- First look at if this is a group target
SELECT target_guid, is_group INTO l_target_guid, l_is_group
FROM mgmt_targets
WHERE target_name = p_target_name
AND target_type = p_target_type;
IF ( l_is_group = 0 ) THEN
-- -------------------------------------------------- --
-- Non-Group single target INCLUDING RAC and Clusters --
-- -------------------------------------------------- --
OPEN p_status_cursor FOR
SELECT /*+ INDEX(JobTgt) */
COUNT(JobSumm.execution_id) as status_count,
JobSumm.status_bucket as status
FROM MGMT_JOB_EXT_TARGETS JobTgt,
MGMT_JOB_EXEC_SUMMARY JobSumm
WHERE JobSumm.scheduled_time > SYSDATE - 7
AND JobSumm.status_bucket IN
( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
MGMT_JOBS.STATUS_BUCKET_RUNNING,
MGMT_JOBS.STATUS_BUCKET_SCHEDULED
)
AND JobTgt.target_guid = HEXTORAW( l_target_guid )
AND JobTgt.execution_id = JobSumm.execution_id
AND JobTgt.job_id = JobSumm.job_id
GROUP BY JobSumm.status_bucket;
-- SELECT COUNT(JobSumm.execution_id) as status_count,
-- JobSumm.status_bucket as status
-- FROM MGMT_JOB_EXEC_SUMMARY JobSumm
-- WHERE JobSumm.scheduled_time > SYSDATE - 7
-- AND JobSumm.status_bucket IN
-- ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
-- MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
-- MGMT_JOBS.STATUS_BUCKET_RUNNING,
-- MGMT_JOBS.STATUS_BUCKET_SCHEDULED
-- )
-- AND EXISTS
-- ( SELECT *
-- FROM MGMT_JOB_TARGET JobTgt
-- WHERE JobTgt.target_guid = HEXTORAW( l_target_guid )
-- AND JobTgt.execution_id = JobSumm.execution_id )
-- GROUP BY JobSumm.status_bucket;
-- ------------------------------------------------------ --
-- End Non-Group single target INCLUDING RAC and Clusters --
-- ------------------------------------------------------ --
ELSE
-- ----------------------------- --
-- Group Home Page Group Column --
-- ----------------------------- --
OPEN p_status_cursor FOR
SELECT COUNT(*) as status_count,
JobSumm.status_bucket as status
FROM MGMT_JOB_EXEC_SUMMARY JobSumm
WHERE JobSumm.scheduled_time > SYSDATE - 7
AND JobSumm.status_bucket IN
( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
MGMT_JOBS.STATUS_BUCKET_RUNNING,
MGMT_JOBS.STATUS_BUCKET_SCHEDULED
)
AND EXISTS
( SELECT *
FROM MGMT_JOB_TARGET JobTgt,
MGMT_JOB Job
WHERE JobTgt.execution_id = HEXTORAW( MGMT_JOB_ENGINE.NO_EXECUTION )
AND JobTgt.target_guid = HEXTORAW( l_target_guid )
AND JobTgt.job_id = JobSumm.job_id
AND Job.job_id = JobSumm.job_id )
GROUP BY JobSumm.status_bucket;
-- SELECT COUNT(*) as status_count,
-- JobSumm.status_bucket as status
-- FROM MGMT_JOB_EXEC_SUMMARY JobSumm
-- WHERE JobSumm.scheduled_time > SYSDATE - 7
-- AND JobSumm.status_bucket IN
-- ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
-- MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
-- MGMT_JOBS.STATUS_BUCKET_RUNNING,
-- MGMT_JOBS.STATUS_BUCKET_SCHEDULED
-- )
-- AND EXISTS
-- ( SELECT *
-- FROM MGMT_JOB_EXT_TARGETS ExecTargets,
-- MGMT_JOB_TARGET JobTgt,
-- MGMT_JOB Job
-- WHERE JobSumm.job_id = Job.job_id
-- AND Job.is_library = 0
-- AND JobTgt.execution_id = HEXTORAW( MGMT_JOB_ENGINE.NO_EXECUTION )
-- AND JobTgt.target_guid = HEXTORAW( l_target_guid )
-- AND JobTgt.job_id = Job.job_id
-- AND ExecTargets.job_id = JobSumm.job_id
-- AND ExecTargets.execution_id = JobSumm.execution_id
-- )
-- GROUP BY JobSumm.status_bucket;
-- --------------------------------- --
-- END Group Home Page Group Column --
-- --------------------------------- --
END IF;
-- !!! Leave super user mode !!! --
IF ( p_ignore_access = 1 ) THEN
MGMT_USER.LEAVE_SUPER_USER_MODE( l_tmp_current_user );
END IF ;
-- -------------------------------------------------------- --
-- End Single Target Home Page or Group HP Group Column --
-- -------------------------------------------------------- --
ELSE
-- ---------------------------------------------------------- --
-- Used by Console Home Page: Show jobs on the targets --
-- on which user has VIEW privillege. Use VPD layer --
-- for both MGMT_JOB and MGMT_TARGETS --
-- ---------------------------------------------------------- --
-- Target type on Console Home
IF ( p_target_type IS NOT NULL AND LENGTH( p_target_type ) > 0 )
THEN
OPEN p_status_cursor FOR
SELECT COUNT(JobSumm.execution_id) as status_count,
status_bucket as status
FROM MGMT_JOB_EXEC_SUMMARY JobSumm
WHERE scheduled_time > SYSDATE - 7
AND ( status_bucket = MGMT_JOBS.STATUS_BUCKET_PROBLEM
or status_bucket = MGMT_JOBS.STATUS_BUCKET_SUSPENDED
)
AND EXISTS
( SELECT *
FROM MGMT_JOB_EXT_TARGETS jt,
MGMT_TARGETS t,
MGMT_JOB j
WHERE j.job_id = jt.job_id
AND j.job_id = JobSumm.job_id
AND t.target_guid = jt.target_guid
AND t.target_type = p_target_type
AND jt.execution_id = JobSumm.execution_id
)
GROUP BY status_bucket;
ELSE
-- All targets
-- Exclude target-less jobs
OPEN p_status_cursor FOR
SELECT COUNT(JobSumm.execution_id) as status_count,
JobSumm.status_bucket as status
FROM MGMT_JOB_EXEC_SUMMARY JobSumm
WHERE JobSumm.scheduled_time > SYSDATE - 7
AND ( JobSumm.status_bucket = MGMT_JOBS.STATUS_BUCKET_PROBLEM
or JobSumm.status_bucket = MGMT_JOBS.STATUS_BUCKET_SUSPENDED
)
AND EXISTS
( SELECT *
FROM MGMT_JOB_EXT_TARGETS jt,
MGMT_TARGETS t,
MGMT_JOB j
WHERE j.job_id = jt.job_id
AND j.job_id = JobSumm.job_id
AND t.target_guid = jt.target_guid
AND jt.execution_id = JobSumm.execution_id
)
GROUP BY status_bucket;
END IF;
-- ---------------------------------------------------------- --
-- End of Console Home Page --
-- ---------------------------------------------------------- --
END IF ;
-- Composite members
ELSE
-- !!! Enter super user mode to leave VPD layer !!! --
IF ( p_ignore_access = 1 ) THEN
MGMT_USER.ENTER_SUPER_USER_MODE( l_tmp_current_user );
END IF ;
-- Obtain composite guid for the group target
SELECT target_guid INTO l_target_guid
FROM mgmt_targets
WHERE target_name = p_target_name
AND target_type = p_target_type;
OPEN p_status_cursor FOR
SELECT COUNT(JobSumm.execution_id) as status_count,
JobSumm.status_bucket as status
FROM MGMT_JOB_EXEC_SUMMARY JobSumm
WHERE JobSumm.scheduled_time > SYSDATE - 7
AND JobSumm.status_bucket IN
( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
MGMT_JOBS.STATUS_BUCKET_RUNNING,
MGMT_JOBS.STATUS_BUCKET_SCHEDULED
)
AND EXISTS
( SELECT *
FROM mgmt$group_flat_memberships,
mgmt_job_ext_targets JobTgt
WHERE composite_target_guid = HEXTORAW( l_target_guid )
AND member_target_guid = JobTgt.target_guid
AND JobTgt.job_id = JobSumm.job_id
AND JobTgt.execution_id = JobSumm.execution_id
)
GROUP BY JobSumm.status_bucket;
-- !!! Leave super user mode !!! --
IF ( p_ignore_access = 1 ) THEN
MGMT_USER.LEAVE_SUPER_USER_MODE( l_tmp_current_user );
END IF ;
END IF;
END get_status_rollup_counts;
----------------------------------------------------
-- End Job Status Intg -> Target Home Pages Calls --
----------------------------------------------------
------------------------------------------------
-- Preferred Credential Validation Calls --
------------------------------------------------
-- Get a list of targets that do NOT
-- have preferred credential set
-- For 4.1 this only handles:
-- A single group/composite target
-- A homogenous type of basic targets
PROCEDURE get_targets_prefcreds_not_set
( p_target_list IN OUT MGMT_JOB_TARGET_LIST,
p_target_type IN VARCHAR2 DEFAULT NULL,
p_cred_set_name IN VARCHAR2 )
IS
l_em_user VARCHAR2(32) := MGMT_USER.get_current_em_user();
l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE;
l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
l_target_list MGMT_JOB_TARGET_LIST; -- Needed to store targets have creds
l_cred_guid MGMT_ENTERPRISE_CREDENTIALS.CREDENTIAL_GUID%TYPE;
BEGIN
-- Call flattening function
-- Filter on p_target_type
get_flattened_member_targets( p_target_list, p_target_type );
-- dbms_output.put_line( ' Flattened target list: ' || p_target_list.COUNT );
IF ( p_target_list IS NOT NULL AND p_target_list.COUNT > 0 ) THEN
-- By this time it would be guaranteed that we
-- have a list of oracle_database,host,or cluster targets
-- Look at enterprise level first
-- Only deal with a type here
l_target_type := p_target_list(1).target_type;
BEGIN
SELECT credential_guid INTO l_cred_guid
FROM MGMT_ENTERPRISE_CREDENTIALS
WHERE target_type = l_target_type
AND credential_set_name = p_cred_set_name
AND user_name = l_em_user;
EXCEPTION WHEN no_data_found THEN NULL;
END;
IF ( l_cred_guid IS NOT NULL ) THEN
-- dbms_output.put_line( ' Enterprise creds found ' );
p_target_list := NULL;
ELSE
-- dbms_output.put_line( ' No enterprise creds found ' );
-- Not on enterprise level
-- Check if target if on target-level (if contains cluster on cluster level)
-- For 4.1 we enforce cluster level credentials for jobs
-- Obtain all targets that have pref cred set
BEGIN
SELECT MGMT_JOB_TARGET_RECORD( t.target_name, t.target_type )
BULK COLLECT INTO l_target_list
FROM MGMT_TARGETS t, MGMT_TARGET_CREDENTIALS c
WHERE c.target_guid = t.target_guid
AND c.credential_set_name = p_cred_set_name
AND c.user_name = l_em_user;
EXCEPTION WHEN NO_DATA_FOUND THEN
l_target_list := NULL;
NULL;
END;
IF l_target_list IS NOT NULL AND l_target_list.count > 0 THEN
-- Filter out any good targets from p_target_list
FOR i IN l_target_list.FIRST..l_target_list.LAST LOOP
IF ( p_target_list IS NULL OR -- not expected
p_target_list.FIRST IS NULL OR
p_target_list.COUNT <= 0
) THEN
EXIT; -- already all pruned.
END IF;
FOR j IN p_target_list.FIRST..p_target_list.LAST LOOP
IF ( p_target_list.EXISTS(j) AND
l_target_list(i).target_name = p_target_list(j).target_name AND
l_target_list(i).target_type = p_target_list(j).target_type ) THEN
p_target_list.DELETE(j);
EXIT;
END IF;
END LOOP;
END LOOP;
END IF;
END IF; -- End validate at Enterprise Level
END IF;
END get_targets_prefcreds_not_set;
-- Private helper that 'flattens' a group
-- into a member list.
-- For 4.1 this only handles:
-- Single 'true' group target
PROCEDURE get_flattened_member_targets
( p_target_list IN OUT MGMT_JOB_TARGET_LIST,
p_target_type IN VARCHAR2 )
IS
l_target MGMT_JOB_TARGET_RECORD;
l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
l_target_type MGMT_TARGETS.target_type%TYPE; -- to store rac or cluster type
BEGIN
IF p_target_list IS NULL THEN
RETURN;
END IF;
FOR i IN p_target_list.FIRST..p_target_list.LAST LOOP
IF ( p_target_list(i).target_type = MGMT_GLOBAL.G_DATABASE_GROUP_TARGET_TYPE OR
p_target_list(i).target_type = MGMT_GLOBAL.G_HOST_GROUP_TARGET_TYPE OR
p_target_list(i).target_type = MGMT_GLOBAL.G_COMPOSITE_TARGET_TYPE ) THEN
IF ( p_target_list.COUNT > 1 ) THEN
-- Either not a single group, or a list of non-group with group target
RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_FLATTENING_INPUT_ERR,
'A Group must be the only target in the list.');
END IF ;
l_target := p_target_list(i);
l_target_guid :=
MGMT_TARGET.get_target_guid( l_target.target_name, l_target.target_type );
-- dbms_output.put_line( ' Composite Guid ' || l_target_guid );
p_target_list := MGMT_JOB_TARGET_LIST();
-- -------------------------------------------------------- --
-- For composite targets: the fact that they can contain --
-- rac or clusters requires special treatment. E.g. --
-- A composite A that contains --
-- db1, db2, rac1 where rac1 contains db1, db3 --
-- The following query returns only db1, db2 and rac1: i.e.,--
-- We only get RAC / Cluster level target, and prune any --
-- targets that are derived members of the Group --
-- -------------------------------------------------------- --
IF ( p_target_type IS NOT NULL AND
l_target.target_type = MGMT_GLOBAL.G_COMPOSITE_TARGET_TYPE ) THEN
-- Allowing rac or cluster based on override target type
IF ( p_target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE ) THEN
l_target_type := MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE;
ELSE
l_target_type := MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE;
END IF;
BEGIN
SELECT MGMT_JOB_TARGET_RECORD( member_target_name, member_target_type )
BULK COLLECT INTO p_target_list
FROM mgmt$group_derived_memberships
WHERE ( member_target_type = p_target_type -- basic type
OR
member_target_type = l_target_type -- rac type
)
AND composite_target_guid = l_target_guid;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
ELSE
-- -------------------------------------------------------- --
-- A single database or host group case --
-- -------------------------------------------------------- --
BEGIN
SELECT MGMT_JOB_TARGET_RECORD( member_target_name, member_target_type )
BULK COLLECT INTO p_target_list
FROM mgmt$group_flat_memberships
WHERE ( member_target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE
OR
member_target_type = MGMT_GLOBAL.G_HOST_TARGET_type
)
AND composite_target_guid = l_target_guid;
EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
END;
END IF;
END IF;
END LOOP;
END get_flattened_member_targets;
----------------------------------------------------
-- End Preferred Credential Validation Calls --
----------------------------------------------------
--------------------------------------------------------------
-- Library Calls --
-- p_system_job: show system job or not default not shown --
-- p_job_type: job type filter if null show all jobs --
-- p_job_name: job name filter if null show all jobs --
-- p_job_owner: job owner filter if null show all jobs --
-- p_ascending: order by ASC or DESC default ASC --
-- p_order_by: column name order by is operated on --
-- This call would get a cursor of library jobs. If the --
-- current em user is the super user, all jobs matching --
-- search filters will be returned. Otherwise only jobs --
-- that the user owns or has at least VIEW access to are --
-- returned. The 'access' value could be one of 'OWNER' --
-- 'VIEW_JOB' or 'FULL_JOB' that should be nlsed by the --
-- caller. (These are IDs as well) --
--------------------------------------------------------------
PROCEDURE get_library_data( p_system_job IN NUMBER DEFAULT 0,
p_job_type IN VARCHAR2,
p_job_name IN VARCHAR2,
p_job_owner IN VARCHAR2,
p_ascending IN NUMBER DEFAULT 1,
p_order_by IN VARCHAR2,
p_library_cursor OUT CURSOR_TYPE )
IS
l_em_user VARCHAR2(32) := MGMT_USER.get_current_em_user();
l_job_name VARCHAR2(64);
l_job_order_clause VARCHAR2(128);
l_job_where_clause VARCHAR2(2000);
l_job_library_stmt VARCHAR2(4000);
l_job_groupby_clause1 VARCHAR2(128) :=
'GROUP BY Job.job_name, Job.job_type, Job.job_description, ' ||
'Job.job_owner, Job.job_id, tgt.type_display_name ' ;
l_job_groupby_clause2 VARCHAR2(128) :=
'GROUP BY Job.job_name, Job.job_type, Job.job_description, ' ||
'up.priv_name, Job.job_owner, Job.job_id, tgt.type_display_name ';
BEGIN
-- System Job
IF ( p_system_job = 0 ) THEN
l_job_where_clause := ' AND Job.system_job = 0 ';
ELSE
l_job_where_clause := ' AND Job.system_job > 0 ';
END IF;
-- Job Type
IF ( p_job_type IS NOT NULL AND LENGTH( p_job_type ) > 0 ) THEN
l_job_where_clause := l_job_where_clause || ' AND Job.job_type = ''' || p_job_type || ''' ';
END IF;
-- Job Name
IF ( p_job_name IS NOT NULL AND LENGTH( p_job_name ) > 0 ) THEN
l_job_name := '%' || UPPER(p_job_name) || '%';
l_job_where_clause := l_job_where_clause || ' AND Job.job_name LIKE ''' || l_job_name || ''' ';
END IF;
-- Job Owner
IF ( p_job_owner IS NOT NULL AND LENGTH( p_job_owner ) > 0 ) THEN
l_job_where_clause := l_job_where_clause || ' AND Job.job_owner = ''' || p_job_owner || ''' ';
END IF;
IF ( p_order_by IS NOT NULL AND LENGTH( p_order_by ) > 0 ) THEN
IF ( p_ascending = 1 ) THEN
l_job_order_clause := ' ORDER BY ' || p_order_by || ' ';
ELSE
l_job_order_clause := ' ORDER BY ' || p_order_by || ' DESC ';
END IF;
END IF;
-- Common statement body: bind l_em_user
l_job_library_stmt :=
'SELECT Job.job_type as job_type, ' || -- Start owned by user
'Job.job_name as job_name, ' ||
'Job.job_description as job_description, ' ||
'''OWNER'' as job_access, ' ||
'Job.job_owner as job_owner, ' ||
'COUNT(*) as target_count, ' ||
'tgt.type_display_name as target_type, ' ||
'Job.job_id as job_id '||
'FROM MGMT_JOb Job, ' ||
'MGMT_JOB_TARGET jobTgt, ' ||
'MGMT_TARGETS tgt ' ||
'WHERE Job.is_library = 1 ' ||
'AND Job.job_id = jobTgt.job_id (+) ' || -- Target-less jobs
'AND jobTgt.target_guid = tgt.target_guid (+) ' ||
'AND Job.job_owner = :em_user ' ||
l_job_where_clause || l_job_groupby_clause1;
IF MGMT_USER.has_priv( l_em_user, MGMT_USER.SUPER_USER ) = 1 THEN
-- Super user, all jobs should be returned.
-- Access is OWNER if super user owns a job, otherwise
-- See if granted FULL, if not return VIEW
-- Bind VIEW_JOB, l_em_user, FULL_JOB, l_em_user
l_job_library_stmt := l_job_library_stmt ||
'UNION ' || -- Start FULL_JOB, if not found, VIEW
'SELECT Job.job_type as job_type, ' ||
'Job.job_name as job_name, ' ||
'Job.job_description as job_description, ' ||
'NVL(up.priv_name, ''VIEW_JOB'') as job_access, ' ||
'Job.job_owner as job_owner, ' ||
'COUNT(*) as target_count, ' ||
'tgt.type_display_name as target_type, ' ||
'Job.job_id as job_id ' ||
'FROM MGMT_JOB Job, ' ||
'MGMT_JOB_TARGET jobTgt, ' ||
'MGMT_TARGETS tgt, ' ||
'MGMT_PRIV_GRANTS up ' ||
'WHERE Job.is_library = 1 ' ||
'AND Job.job_id = jobTgt.job_id (+) ' ||
'AND jobTgt.target_guid = tgt.target_guid (+) ' ||
'AND up.grantee (+) = :em_user ' ||
'AND up.priv_name (+) = :full ' ||
'AND up.guid (+) = Job.job_id ' ||
'AND Job.job_owner != :em_user ' || -- Only for jobs not owned by current user
l_job_where_clause || l_job_groupby_clause2 || l_job_order_clause ;
OPEN p_library_cursor FOR l_job_library_stmt
USING l_em_user, l_em_user, mgmt_user.full_job, l_em_user;
ELSE
-- Not a Super User
-- Bind: FULL_JOB, l_em_user, FULL_JOB; VIEW_JOB, l_em_user, VIEW_JOB, l_em_user, FULL_JOB
l_job_library_stmt := l_job_library_stmt ||
'UNION ' || -- Start FULL_JOB access: Provided by Anila.Holser
'SELECT Job.job_type as job_type, ' ||
'Job.job_name as job_name, ' ||
'Job.job_description as job_description, ' ||
'''FULL_JOB'' as job_access, ' ||
'Job.job_owner as job_owner, ' ||
'COUNT(*) as target_count, ' ||
'tgt.type_display_name as target_type, ' ||
'Job.job_id as job_id ' ||
'FROM MGMT_JOB Job, ' ||
'MGMT_JOB_TARGET jobTgt, ' ||
'MGMT_TARGETS tgt, ' ||
'MGMT_PRIV_GRANTS up ' ||
'WHERE Job.is_library = 1 ' ||
'AND Job.job_id = jobTgt.job_id (+) '||
'AND jobTgt.target_guid = tgt.target_guid (+) ' ||
'AND up.grantee = :em_user ' ||
'AND up.priv_name = :full ' ||
'AND up.guid = Job.job_id ' ||
'AND Job.job_owner != :em_user ' || -- Necessary because FULL is granted to owner now!
l_job_where_clause || l_job_groupby_clause2 ||
'UNION ' || -- Start VIEW_JOB access
'SELECT Job.job_type as job_type, ' ||
'Job.job_name as job_name, ' ||
'Job.job_description as job_description, ' ||
'''VIEW_JOB'' as job_access, ' ||
'Job.job_owner as job_owner, ' ||
'COUNT(*) as target_count, ' ||
'tgt.type_display_name as target_type, ' ||
'Job.job_id as job_id ' ||
'FROM MGMT_JOB Job, ' ||
'MGMT_JOB_TARGET jobTgt, ' ||
'MGMT_TARGETS tgt, ' ||
'MGMT_PRIV_GRANTS up ' ||
'WHERE Job.is_library = 1 ' ||
'AND Job.job_id = jobTgt.job_id (+) ' ||
'AND jobTgt.target_guid = tgt.target_guid (+) ' ||
'AND up.grantee = :em_user ' ||
'AND up.priv_name = :view_string ' ||
'AND up.guid = Job.job_id ' ||
'AND Job.job_owner != :em_user ' ||
'AND up.guid NOT IN ' || -- Excluding FULL_JOB entry
' ( SELECT guid ' ||
'FROM mgmt_priv_grants ' ||
'WHERE grantee = :em_user ' ||
'AND priv_name = :full ) ' ||
l_job_where_clause || l_job_groupby_clause2 || l_job_order_clause;
OPEN p_library_cursor FOR l_job_library_stmt
USING l_em_user,
l_em_user, mgmt_user.full_job, l_em_user,
l_em_user, mgmt_user.view_job, l_em_user, l_em_user, mgmt_user.full_job;
END IF;
END get_library_data;
----------------------------
-- End Library Calls --
----------------------------
-- End package
--
END MGMT_JOB_UI;
Regards
Hristo
|
|
|
|
|
|
| Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #350213 is a reply to message #348602] |
Wed, 24 September 2008 07:23 |
hristo
Messages: 258
Registered: May 2007
|
Senior Member |
|
|
Is the edit button gone?
---------- C:\TEST.TXT
[4]-- Constants for URI use
[5]URI_USE_CREATE CONSTANT NUMBER(2) := 7;
[6]URI_USE_CREATE_LIKE CONSTANT NUMBER(2) := 8;
[8]-- Constant for job step target paramger
[9]PARAM_STEP_TARGET constant varchar2(32) := '%argetName%';
[14]--------------- helpers ------------------
[16]-- Convert string array to delim separated string
[17]FUNCTION concat_string_array ( p_arr SMP_EMD_STRING_ARRAY,
[18] p_delim VARCHAR2 ) RETURN VARCHAR2 IS
[20]l_concat VARCHAR2(32000) := '';
[23] IF ( p_arr.count >= 1 ) THEN
[24] l_concat := p_arr(1);
[25] END IF;
[27] FOR j IN 2..p_arr.count LOOP
[28] l_concat := l_concat || p_delim || p_arr(j);
[29] END LOOP;
[31] RETURN l_concat;
[36]PROCEDURE add_to_array (
[37] p_job_id VARCHAR2,
[38] p_execution_id VARCHAR2,
[39] p_job_name VARCHAR2,
[40] p_job_owner VARCHAR2,
[41] p_step_id NUMBER,
[42] p_iterate_param VARCHAR2,
[43] p_iterate_param_index NUMBER,
[44] p_param_value VARCHAR2,
[45] p_target_name_arr IN OUT SMP_EMD_STRING_ARRAY ) IS
[47]l_target_name MGMT_TARGETS.TARGET_NAME%TYPE := '';
[50] IF ( INSTR(p_param_value, '%') <= 0 ) THEN
[51] -- Note: the value could be hard coded. Why: test? well known name?
[52] l_target_name := p_param_value;
[53] ELSE
[54] l_target_name := MGMT_JOB_ENGINE.substitute_params(
[55] p_job_id,
[56] p_execution_id,
[57] p_step_id,
[58] p_iterate_param,
[59] p_iterate_param_index,
[60] p_job_name,
[61] p_job_owner,
[62] p_param_value );
[64] END IF;
[66] p_target_name_arr.extend(1);
[67] p_target_name_arr(p_target_name_arr.count) := l_target_name;
[72]------------ public functions ---------------
[75]-- Get the targets for this step, if any
[76]FUNCTION get_step_targets ( p_step_id NUMBER,
[77] p_return_display_names BOOLEAN
[78] ) RETURN SMP_EMD_STRING_ARRAY IS
[80]-- target arr info
[81]l_target_name_arr SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
[82]l_target_disp_name_arr SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
[85] SELECT target_name, display_name
[86] BULK COLLECT
[87] INTO l_target_name_arr, l_target_disp_name_arr
[88] FROM MGMT_JOB_STEP_TARGETS st,
[89] MGMT_TARGETS t
[90] WHERE st.target_guid = t.target_guid
[91] AND st.step_id = p_step_id;
[93] -- convert internal names to display names
[94] IF p_return_display_names THEN
[95] l_target_name_arr := l_target_disp_name_arr;
[96] END IF;
[98] RETURN l_target_name_arr;
[102]-- Get the targets for this step, if any
[103]FUNCTION get_step_targets_orig ( p_step_id NUMBER,
[104] p_return_display_names BOOLEAN
[105] ) RETURN SMP_EMD_STRING_ARRAY IS
[107]-- target arr info
[108]l_target_name_arr SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY();
[110]-- step info for subst call
[111]l_job_type MGMT_JOB_TYPE_INFO.job_type%TYPE;
[112]l_job_id MGMT_JOB.job_id%TYPE;
[113]l_exec_id MGMT_JOB_HISTORY.execution_id%TYPE;
[114]l_step_name MGMT_JOB.job_name%TYPE;
[115]l_iterate_param MGMT_JOB_HISTORY.iterate_param%TYPE;
[116]l_iterate_param_index MGMT_JOB_HISTORY.iterate_param_index%TYPE;
[118]-- display name temp
[119]l_display_name MGMT_TARGETS.DISPLAY_NAME%TYPE := '';
[121]l_job_name MGMT_JOB.job_name%TYPE;
[122]l_job_owner MGMT_JOB.job_owner%TYPE;
[125] SELECT job_id, execution_id, step_name, iterate_param, iterate_param_index
[126] INTO l_job_id, l_exec_id, l_step_name, l_iterate_param, l_iterate_param_index
[127] FROM MGMT_JOB_HISTORY
[128] WHERE step_id = p_step_id;
[130] SELECT job_type
[131] INTO l_job_type
[132] FROM MGMT_JOB
[133] WHERE job_id = l_job_id;
[135] SELECT job_name, job_owner INTO l_job_name, l_job_owner FROM
[136] MGMT_JOB WHERE job_id=l_job_id;
[138] FOR param IN (SELECT param_name, is_scalar, scalar_value, vector_value
[139] FROM MGMT_JOB_STEP_PARAMS
[140] WHERE
[141] job_type=l_job_type AND
[142] step_name=l_step_name AND
[143] param_name like PARAM_STEP_TARGET)
[144] LOOP
[145] -- can have more than one e.g., srcTargetName, destTargetName
[147] IF ( param.is_scalar=1 ) THEN
[148] add_to_array(l_job_id,
[149] l_exec_id,
[150] l_job_name,
[151] l_job_owner,
[152] p_step_id,
[153] l_iterate_param,
[154] l_iterate_param_index,
[155] param.scalar_value,
[156] l_target_name_arr);
[157] ELSE
[158] FOR j IN 1..param.vector_value.count LOOP
[159] add_to_array(l_job_id,
[160] l_exec_id,
[161] l_job_name,
[162] l_job_owner,
[163] p_step_id,
[164] l_iterate_param,
[165] l_iterate_param_index,
[166] param.vector_value(j),
[167] l_target_name_arr);
[168] END LOOP;
[169] END IF;
[171] END LOOP;
[173] -- convert internal names to display names
[174] IF p_return_display_names THEN
[176] FOR k IN 1..l_target_name_arr.count LOOP
[178] SELECT target_name
[179] INTO l_display_name
[180] FROM MGMT_TARGETS
[181] WHERE target_name = l_target_name_arr(k);
[183] -- overwrite internal name with display name
[184] l_target_name_arr(k) := l_display_name;
[186] END LOOP;
[188] END IF;
[190] -- We're done, so reset parameters for this session
[191] MGMT_JOB_ENGINE.reset_params();
[193] RETURN l_target_name_arr;
[196]-- Get the targets for this step, if any
[197]FUNCTION get_step_targets_str ( p_step_id NUMBER,
[198] p_return_display_names BOOLEAN
[199] ) RETURN VARCHAR2 IS
[201] RETURN concat_string_array (
[202] get_step_targets ( p_step_id, p_return_display_names ),
[203] ', ' );
[207]-- Get the parameters for this job, filter as specified for this jobtype
[208]PROCEDURE get_visible_params ( p_job_id RAW,
[209] p_exec_id RAW,
[210] p_params_out OUT CURSOR_TYPE) IS
[212]l_job_type MGMT_JOB.job_type%TYPE;
[213]l_show_params NUMBER;
[214]l_specified NUMBER;
[217] SELECT job_type
[218] INTO l_job_type
[219] FROM MGMT_JOB_EXEC_SUMMARY js, MGMT_JOB j
[220] WHERE js.execution_id = p_exec_id
[221] AND js.job_id = j.job_id;
[223] dbms_output.put_line('l_job_type=' || l_job_type );
[225] BEGIN
[226] SELECT show_param
[227] INTO l_show_params
[228] FROM MGMT_JOB_TYPE_DISPLAY_INFO
[229] WHERE job_type = l_job_type;
[231] EXCEPTION
[232] WHEN NO_DATA_FOUND THEN
[233] -- default to true
[234] l_show_params := 1;
[235] END;
[237] dbms_output.put_line('l_show_params=' || l_show_params );
[239] -- is there at least one param_name specified?
[240] SELECT count(param_name)
[241] INTO l_specified
[242] FROM MGMT_JOB_TYPE_DISPLAY_PARAM
[243] WHERE job_type = l_job_type
[244] AND rownum=1;
[247] IF l_specified = 0 THEN
[249] IF l_show_params = 0 THEN
[251] -- hide all
[252] OPEN p_params_out
[253] FOR
[254] SELECT 'p1' parameter_name, 'pt' parameter_type, 0 encrypted,
[255] 1 is_scalar, 'v1' scalar_value, NULL vector_value
[256] , NULL large_value
[257] FROM dual
[258] WHERE rownum < 1;
[260] ELSE
[262] -- show all
[263] OPEN p_params_out
[264] FOR
[265] SELECT parameter_name, encrypted,
[266] parameter_type,
[267] decode(encrypted, 0, scalar_value, '******') scalar_value,
[268] vector_value, large_value
[269] FROM MGMT_JOB_PARAMETER
[270] WHERE execution_id = p_exec_id AND
[271] job_id = p_job_id
[272] ORDER BY parameter_name;
[274] END IF;
[276] ELSE
[277] -- MGMT_JOB_TYPE_DISPLAY_PARAM.param_name has entries
[279] IF l_show_params = 0 THEN
[281] -- hide specified params
[282] OPEN p_params_out
[283] FOR
[284] SELECT parameter_name, encrypted,
[285] parameter_type,
[286] decode(encrypted, 0, scalar_value, '******') scalar_value,
[287] vector_value, large_value
[288] FROM MGMT_JOB_PARAMETER
[289] WHERE execution_id = p_exec_id AND
[290] job_id = p_job_id AND
[291] parameter_name NOT IN
[292] ( SELECT dp.param_name
[293] FROM MGMT_JOB_TYPE_DISPLAY_PARAM dp
[294] WHERE dp.job_type = l_job_type )
[295] ORDER BY parameter_name;
[297] ELSE
[299] -- show specified params
[300] OPEN p_params_out
[301] FOR
[302] SELECT jp.parameter_name, jp.encrypted,
[303] jp.parameter_type,
[304] decode(jp.encrypted, 0, jp.scalar_value, '******') scalar_value,
[305] jp.vector_value, jp.large_value
[306] FROM MGMT_JOB_PARAMETER jp, MGMT_JOB_TYPE_DISPLAY_PARAM dp
[307] WHERE jp.execution_id = p_exec_id AND
[308] jp.job_id = p_job_id AND
[309] jp.parameter_name = dp.param_name AND
[310] dp.job_type = l_job_type
[311] ORDER BY dp.param_order;
[313] END IF;
[315] END IF;
[320]-- Get the URI for this uri_use
[321]-- see emSDK/job/dtd/UriSource.java for uri_use constants
[322]FUNCTION get_display_uri ( p_job_type IN VARCHAR2,
[323] p_uri_use IN NUMBER
[324] ) RETURN VARCHAR2 IS
[325]l_uri MGMT_JOB_TYPE_URI_INFO.uri%TYPE;
[329] IF p_uri_use IN (URI_USE_CREATE, URI_USE_CREATE_LIKE)
[330] AND p_job_type IN ('OSCommand', 'SQLScript', 'RMANScript') THEN
[331] IF p_job_type='RMANScript' THEN
[332] RETURN 'database/rman/jobScheduleDef';
[333] ELSE
[334] RETURN 'jobs/jobsCreateDef';
[335] END IF;
[336] END IF;
[338] BEGIN
[339] SELECT uri
[340] INTO l_uri
[341] FROM MGMT_JOB_TYPE_URI_INFO
[342] WHERE job_type = p_job_type
[343] AND uri_use = p_uri_use;
[344] EXCEPTION
[345] WHEN NO_DATA_FOUND THEN
[346] NULL;
[347] END;
[349] RETURN l_uri;
[352]-- --------------------------------------------------------------------------------
[353]-- FUNCTION: one_run_query() - returns the query for obtaining one job run or
[354]-- occurrence. Statement is dynamically generated given input filters.
[355]-- --------------------------------------------------------------------------------
[357]FUNCTION one_run_query(
[358] job_name_in IN VARCHAR2,
[359] job_type_in IN VARCHAR2,
[360] job_owner_in IN VARCHAR2,
[361] job_status_in IN INTEGER,
[362] target_name_in IN VARCHAR2,
[363] target_type_in IN VARCHAR2,
[364] target_guid_in IN RAW,
[365] timeframe_in IN INTEGER,
[366] active_flag_in IN INTEGER,
[367] sort_by_in IN VARCHAR2,
[368] sort_order_in IN VARCHAR2,
[369] show_forMembers_in IN INTEGER)
[371] RETURN VARCHAR2 IS
[373] run_sql VARCHAR(10000) := '';
[374] add_tgt_tables_to_from_clause NUMBER := 0;
[376] select_clause VARCHAR(2000) := 'SELECT DISTINCT JobSumm.job_id, ' ||
[377] ' Job.job_name, Job.job_type, Job.job_owner, ' ||
[378]-- ' TO_CHAR(JobSumm.scheduled_time, ''YYYY-MM-DD HH24:MI:SS'') start_time_str,' ||
[379] ' JobSumm.scheduled_time, JobSumm.timezone_region, ' ||
[380] ' JobSched.frequency_code ';
[381] from_clause VARCHAR(2000) := ' FROM MGMT_JOB Job, ' ||
[382] ' MGMT_JOB_EXEC_SUMMARY JobSumm,' ||
[383] ' MGMT_JOB_SCHEDULE JobSched ';
[385] tgt_tables VARCHAR(100) := ' , MGMT_JOB_TARGET JobTargets,' ||
[386] ' MGMT_TARGETS Targets ';
[388] where_clause VARCHAR(3000) := ' WHERE JobSumm.job_id=Job.job_id' ||
[389] ' and Job.is_library = 0' ||
[390] ' and JobSched.schedule_id = Job.schedule_id ';
[391] where_targets VARCHAR(100) := ' and JobTargets.target_guid = Targets.target_guid ';
[395] -- Add job name filter to where clause
[396] --
[397]-- DBMS_OUTPUT.PUT_LINE('job_name_in = '|| job_name_in);
[398] IF job_name_in IS NOT NULL
[399] THEN
[400] where_clause := where_clause || ' and Job.job_name like ''' || job_name_in || ''' ';
[401] END IF;
[403] -- Add job type filter to where clause
[404] --
[405] IF job_type_in IS NOT NULL
[406] THEN
[407] where_clause := where_clause || ' and Job.job_type like ''' || job_type_in || '''';
[408] END IF;
[410] -- Add job owner filter to where clause
[411] --
[412] IF job_owner_in IS NOT NULL
[413] THEN
[414] where_clause := where_clause || ' and Job.job_owner like ''' || job_owner_in || '''';
[415] END IF;
[417]DBMS_OUTPUT.PUT_LINE('show_forMembers_in = '|| show_forMembers_in);
[418] IF show_forMembers_in = 0
[419] THEN
[420] -- Add target type filter to where clause
[421] --
[422] IF target_type_in IS NOT NULL
[423] THEN
[424] where_clause := where_clause || ' and Job.job_id in ( select job_id from MGMT_JOB_TARGET JobTargets, MGMT_TARGETS Targets ' ||
[425] 'where JobTargets.target_guid = Targets.target_guid ' ||
[426] 'and Targets.target_type = ''' || target_type_in || '''' ||
[427] ') ';
[428] add_tgt_tables_to_from_clause := 1;
[429] END IF;
[431] -- Add target name filter to where clause
[432] --
[433] IF target_name_in IS NOT NULL
[434] THEN
[435] where_clause := where_clause || ' and Job.job_id in ( select job_id from MGMT_JOB_TARGET JobTargets, MGMT_TARGETS Targets ' ||
[436] 'where JobTargets.target_guid = Targets.target_guid ' ||
[437] 'and Targets.target_name like ''' || target_name_in || '''' ||
[438] ') ';
[439] add_tgt_tables_to_from_clause := 1;
[440] END IF;
[442] -- Add target GUID filter to where clause
[443] --
[444] IF target_guid_in IS NOT NULL
[445] THEN
[446] where_clause := where_clause || ' and Job.job_id in ( select job_id from MGMT_JOB_TARGET JobTargets, MGMT_TARGETS Targets ' ||
[447] 'where JobTargets.target_guid = Targets.target_guid ' ||
[448] 'and Targets.target_guid = ''' || target_guid_in || '''' ||
[449] ') ';
[450] add_tgt_tables_to_from_clause := 1;
[451] END IF;
[452] -- Get runs for MEMBERS of a group (as opposed to runs directly submitted to group)
[453] ELSE
[455] from_clause := from_clause || ', MGMT_TARGET_MEMBERSHIPS members, MGMT_JOB_TARGET JobTargets ';
[456] where_clause := where_clause ||
[457] ' and JobTargets.execution_id = jobSumm.execution_id ' ||
[458] ' and exists (SELECT member_target_guid ' ||
[459] ' FROM mgmt$group_flat_memberships members, ' ||
[460] ' MGMT_JOB_TARGET JobTargets ' ||
[461] ' WHERE member_target_guid = JobTargets.target_guid ' ||
[462] ' AND composite_target_guid = HEXTORAW(''' || target_guid_in || ''') )' ||
[463] ' AND members.member_target_guid = JobTargets.target_guid ' ||
[464] ' AND members.composite_target_guid = HEXTORAW(''' || target_guid_in || ''')';
[465] END IF;
[467] -- Add timeframe filter to where clause
[468] --
[469] IF timeframe_in IS NOT NULL AND timeframe_in != 0
[470] THEN
[471] where_clause := where_clause || ' and JobSumm.scheduled_time > SYSDATE - ' || timeframe_in;
[472] END IF;
[474] -- Add job status filter to where clause
[475] --
[476] IF job_status_in IS NOT NULL AND job_status_in != 0
[477] THEN
[478] -- All status codes for status-buckets are negative numbers. With status
[479] -- buckets many status codes are "OR"ed.
[480] --
[481] IF job_status_in < 0
[482] THEN
[483] -- Problem status bucket
[484] IF job_status_in = -1
[485] THEN
[486] where_clause := where_clause || ' and Job.job_id in ' ||
[487] ' (select job_id from MGMT_JOB_EXEC_SUMMARY JobSumm ' ||
[488] ' where (JobSumm.status = ' || MGMT_JOBS.ABORTED_STATUS || ' OR ' ||
[489] 'JobSumm.status = ' || MGMT_JOBS.FAILED_STATUS || ' OR ' ||
[490] 'JobSumm.status = ' || MGMT_JOBS.STOPPED_STATUS || ') ' ||
[491] ') ';
[492] END IF;
[494] -- Active status bucket
[495] IF job_status_in = -2
[496] THEN
[497] where_clause := where_clause || ' and Job.job_id in ' ||
[498] ' (select job_id from MGMT_JOB_EXEC_SUMMARY JobSumm ' ||
[499] ' where (JobSumm.status = ' || MGMT_JOBS.SCHEDULED_STATUS || ' OR ' ||
[500] 'JobSumm.status = ' || MGMT_JOBS.EXECUTING_STATUS || ' OR ' ||
[501] 'JobSumm.status = ' || MGMT_JOBS.AGENTDOWN_STATUS || ' OR ' ||
[502] 'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_STATUS || ' OR ' ||
[503] 'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_LOCK_STATUS || ' OR ' ||
[504] 'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_EVENT_STATUS || ' OR ' ||
[505] 'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS || ') ' ||
[506] ') ';
[507] END IF;
[509] -- Suspended status bucket
[510] IF job_status_in = -3
[511] THEN
[512] where_clause := where_clause || ' and Job.job_id in ' ||
[513] ' (select job_id from MGMT_JOB_EXEC_SUMMARY JobSumm ' ||
[514] 'where (JobSumm.status = ' || MGMT_JOBS.AGENTDOWN_STATUS || ' OR ' ||
[515] 'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_STATUS || ' OR ' ||
[516] 'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_LOCK_STATUS || ' OR ' ||
[517] 'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_EVENT_STATUS || ' OR ' ||
[518] 'JobSumm.status = ' || MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS || ') '||
[519] ') ';
[520] END IF;
[522] ELSE
[523] where_clause := where_clause || ' and Job.job_id in ' ||
[524] ' (select job_id from MGMT_JOB_EXEC_SUMMARY JobSumm ' ||
[525] 'where JobSumm.status = ' || job_status_in || ')';
[526] END IF;
[527] END IF;
[529] -- If a target clause is necessary, add the target tables MGMT_JOB_TARGETS and MGMT_TARGETS to the
[530] -- FROM tables list and add the join to the where clause. ARU jobs (which do not have targets)
[531] -- do not get returned if the join is there and there are ONLY update ARU job runs to be returned.
[532] --
[533] IF add_tgt_tables_to_from_clause = 1
[534] THEN
[535] from_clause := from_clause || tgt_tables;
[536] where_clause := where_clause || where_targets;
[537] END IF;
[539] -- Add sorting to where clause
[540] --
[541] IF sort_by_in IS NOT NULL
[542] THEN
[543] where_clause := where_clause || ' order by ' || sort_by_in || ' ' || sort_order_in;
[544] END IF;
[547] run_sql := run_sql || select_clause || from_clause || where_clause;
[549] return run_sql;
[551]END one_run_query;
[553]-- --------------------------------------------------------------------------------
[554]-- get_run_data() - returns data for one job run or occurrence (for Job Console UI).
[555]-- data is returned via a cursor output parameter
[556]-- --------------------------------------------------------------------------------
[557]PROCEDURE get_run_data(
[558] job_name_in IN VARCHAR2,
[559] job_type_in IN VARCHAR2,
[560] job_owner_in IN VARCHAR2,
[561] job_status_in IN INTEGER,
[562] target_name_in IN VARCHAR2,
[563] target_type_in IN VARCHAR2,
[564] target_guid_in IN VARCHAR2,
[565] timeframe_in IN INTEGER,
[566] active_flag_in IN INTEGER,
[567] current_user_in IN VARCHAR2,
[568] show_forMembers_in IN INTEGER,
[569] ignore_access_flag_in IN INTEGER,
[570] sort_by_in IN VARCHAR2,
[571] sort_order_in IN VARCHAR2,
[572] run_tbl_data_out OUT CURSOR_TYPE)
[573] IS
[575] OPEN run_tbl_data_out FOR
[576] SELECT job_id,
[577] execution_id,
[578] job_name,
[579] job_type,
[580] owner,
[581] target_guid,
[582] target_name,
[583] target_type,
[584] target_count,
[585] group_flag,
[586] scheduled_time,
[587]-- scheduled_start,
[588] timezone_region,
[589] next_scheduled,
[590] repeating,
[591] job_access,
[592] stat_bucket_problem,
[593] stat_bucket_suspended,
[594] stat_bucket_active,
[595] stat_scheduled,
[596] stat_executing,
[597] stat_aborted,
[598] stat_failed,
[599] stat_completed,
[600] stat_agent_down,
[601] stat_stopped,
[602] stat_suspended,
[603] stat_suspended_lock,
[604] stat_suspended_event,
[605] stat_suspended_blackout
[606] FROM TABLE(CAST(get_runs_as_table(job_name_in,
[607] job_type_in,
[608] job_owner_in,
[609] job_status_in,
[610] target_name_in,
[611] target_type_in,
[612] target_guid_in,
[613] timeframe_in,
[614] active_flag_in,
[615] current_user_in,
[616] show_forMembers_in,
[617] ignore_access_flag_in,
[618] sort_by_in,
[619] sort_order_in)
[620] AS jobRunTableType));
[622]END get_run_data;
[624]-- --------------------------------------------------------------------------------
[625]-- get_runs_as_table() - Calls get_run_data() which gathers the job run data
[626]-- and returns a table type. The table type is then returned to the java caller
[627]-- as a cursor.
[628]-- --------------------------------------------------------------------------------
[629]FUNCTION get_runs_as_table(
[630] job_name_in IN VARCHAR2,
[631] job_type_in IN VARCHAR2,
[632] job_owner_in IN VARCHAR2,
[633] job_status_in IN INTEGER,
[634] target_name_in IN VARCHAR2,
[635] target_type_in IN VARCHAR2,
[636] target_guid_in IN VARCHAR2,
[637] timeframe_in IN INTEGER,
[638] active_flag_in IN INTEGER,
[639] current_user_in IN VARCHAR2,
[640] show_forMembers_in IN INTEGER,
[641] ignore_access_flag_in IN INTEGER,
[642] sort_by_in IN VARCHAR2,
[643] sort_order_in IN VARCHAR2)
[644] RETURN jobRunTableType IS
[646] run_sql VARCHAR2(3000) := '';
[647] recCount NUMBER := 0;
[648] run_data_cursor CURSOR_TYPE;
[649] status_cursor CURSOR_TYPE;
[650] problem_bucket NUMBER := 0;
[651] suspended_bucket NUMBER := 0;
[652] active_bucket NUMBER := 0;
[653] status_code NUMBER := 0;
[654] status_count NUMBER := 0;
[655] target_count NUMBER := 0;
[656] target_name VARCHAR2(256) := '';
[657] target_type VARCHAR2(128) := '';
[658] internal_type VARCHAR2(128) := '';
[659] group_flag NUMBER(1) := 0;
[660] target_guid RAW(16);
[661] execution_id RAW(16);
[662] tmp_current_user VARCHAR2(32);
[664]-- job_access VARCHAR(30);
[667] run_row runBasicRowType := runBasicRowType(NULL, NULL, NULL,
[668] NULL, NULL, NULL, 0);
[669] detailed_row runDetailedRowType := runDetailedRowType (NULL, NULL, NULL, NULL, NULL,
[670] NULL, NULL, NULL, 0,
[671] 0, NULL, NULL, NULL,
[672] 0, 0, 0, 0,
[673] 0, 0, 0, 0,
[674] 0, 0, 0, 0,
[675] 0, 0, 0, 0);
[676] run_table jobRunTableType := jobRunTableType();
[678] CURSOR getStatusCodeCursor (job_id_in RAW,
[679] scheduled_time_in DATE)
[680] IS
[681] SELECT status, count (*)
[682] FROM mgmt_job_exec_summary
[683] WHERE job_id = job_id_in
[684] AND scheduled_time = scheduled_time_in
[685] GROUP BY status;
[687] CURSOR getTargetInfoCursor (job_id_in RAW)
[688] IS
[689] SELECT t.target_guid, t.display_name, t.type_display_name, t.target_type,
[690] NVL( (select property_value from mgmt_type_properties tp
[691] where tp.property_name = 'is_group'
[692] and tp.target_type = t.target_type and rownum = 1
[693] ),
[694] 0) is_group
[695] FROM mgmt_job_target j,
[696] mgmt_targets t,
[697] mgmt_target_memberships m
[698] WHERE j.job_id = job_id_in
[699] --- Exclude executions that haven't run yet
[700] and j.execution_id != '0000000000000000'
[701] AND j.target_guid = t.target_guid
[702] GROUP BY t.target_guid, t.display_name, t.type_display_name, t.target_type;
[704]-- CURSOR getJobAccess (job_id_in RAW)
[705]-- IS
[706]-- SELECT nvl(priv_name, 'NONE')
[707]-- FROM mgmt_priv_grants
[708]-- where guid = job_id_in;
[710]-- ------------------------------------------------------------------------------------
[711]-- Cursor for retrieveal of distinct run data (minus status and target info)
[712]-- ------------------------------------------------------------------------------------
[716] run_sql := one_run_query(job_name_in, job_type_in, job_owner_in, job_status_in,
[717] target_name_in, target_type_in, target_guid_in,
[718] timeframe_in, active_flag_in, sort_by_in, sort_order_in,
[719] show_forMembers_in);
[721]DBMS_OUTPUT.PUT_LINE(SUBSTR(run_sql, 0, 254));
[722]DBMS_OUTPUT.PUT_LINE(SUBSTR(run_sql, 255, 254));
[723]DBMS_OUTPUT.PUT_LINE(SUBSTR(run_sql, 509, 254));
[724]DBMS_OUTPUT.PUT_LINE(SUBSTR(run_sql, 763, 254));
[725]DBMS_OUTPUT.PUT_LINE(SUBSTR(run_sql, 1017, 254));
[727]DBMS_OUTPUT.PUT_LINE('IGNORE ACCESS FLAG = ' || ignore_access_flag_in);
[729] IF ignore_access_flag_in = 1
[730] THEN
[731] MGMT_USER.enter_super_user_mode (tmp_current_user );
[732] END IF;
[734] OPEN run_data_cursor FOR run_sql;
[735] LOOP
[736] FETCH run_data_cursor INTO run_row.job_id,
[737] run_row.job_name, run_row.job_type,
[738] run_row.owner, run_row.scheduled_time,
[739] run_row.timezone_region,
[740] run_row.repeating;
[741] EXIT WHEN run_data_cursor%NOTFOUND;
[743] run_table.EXTEND;
[745] detailed_row.job_id := run_row.job_id;
[746] detailed_row.job_name := run_row.job_name;
[747] detailed_row.job_type := run_row.job_type;
[748] detailed_row.owner := run_row.owner;
[749] detailed_row.repeating := run_row.repeating;
[750] detailed_row.scheduled_time := run_row.scheduled_time;
[751] detailed_row.timezone_region := run_row.timezone_region;
[754] -- Get one execution id from the job
[755] --
[756] SELECT JobSumm.execution_id into execution_id
[757] FROM mgmt_job_exec_summary JobSumm
[758] WHERE JobSumm.job_id = detailed_row.job_id
[759] AND JobSumm.scheduled_time = detailed_row.scheduled_time
[760] AND rownum = 1;
[762] detailed_row.execution_id := execution_id;
[764] -- Job Access
[765] --
[766]-- IF current_user_in = detailed_row.owner
[767]-- THEN
[768]-- detailed_row.job_access := 'FULL_JOB';
[769]-- ELSE
[770]-- WHAT ABOUT CHECKING FOR THE CURRENT USER BEING A SUPER-USER?
[771]-- IF SO DOES THAT IMPLIES VIEW_JOB FOR ALL JOBS.
[772]-- OPEN getJobAccess (detailed_row.job_id);
[773]-- FETCH getJobAccess INTO job_access;
[774]-- detailed_row.access := job_access;
[775]-- CLOSE getJobAccess;
[776]-- END IF;
[778] -- Target cursor
[779] --
[780] OPEN getTargetInfoCursor (detailed_row.job_id);
[781] LOOP
[783] FETCH getTargetInfoCursor INTO target_guid, target_name, target_type, internal_type, group_flag;
[784] EXIT WHEN getTargetInfoCursor%NOTFOUND;
[785] target_count := target_count + 1;
[786] detailed_row.target_type := target_type;
[788] -- Only get the target name, type and guid for the first target. If there
[789] -- are more than one targets in the job the UI shows the count. The link can
[790] -- take the user to all executions at which time the targets can be seen.
[791] IF target_count = 1
[792] THEN
[793] detailed_row.target_name := target_name;
[794] detailed_row.target_type := target_type;
[795] detailed_row.target_guid := target_guid;
[796] detailed_row.group_flag := group_flag;
[797] END IF;
[799] END LOOP;
[801] detailed_row.target_count := target_count;
[802] CLOSE getTargetInfoCursor;
[804] OPEN getStatusCodeCursor (detailed_row.job_id, detailed_row.scheduled_time);
[805] LOOP
[806] -- Status cursor
[807] --
[808] FETCH getStatusCodeCursor INTO status_code, status_count;
[809] EXIT WHEN getStatusCodeCursor%NOTFOUND;
[811] -- Scheduled
[812] --
[813] IF status_code = MGMT_JOBS.SCHEDULED_STATUS
[814] THEN
[815] detailed_row.stat_scheduled := status_count;
[816] END IF;
[818] -- Executing (running)
[819] --
[820] IF status_code = MGMT_JOBS.EXECUTING_STATUS
[821] THEN
[822] detailed_row.stat_executing := status_count;
[823] active_bucket := active_bucket + status_count;
[824] END IF;
[826] -- Aborted
[827] --
[828] IF status_code = MGMT_JOBS.ABORTED_STATUS
[829] THEN
[830] detailed_row.stat_aborted := status_count;
[831] problem_bucket := problem_bucket + status_count;
[832] END IF;
[834] -- Failed
[835] --
[836] IF status_code = MGMT_JOBS.FAILED_STATUS
[837] THEN
[838] detailed_row.stat_failed := status_count;
[839] problem_bucket := problem_bucket + status_count;
[840] END IF;
[842] -- Completed
[843] --
[844] IF status_code = MGMT_JOBS.COMPLETED_STATUS
[845] THEN
[846] detailed_row.stat_completed := status_count;
[847] END IF;
[849] -- Agent Down
[850] --
[851] IF status_code = MGMT_JOBS.AGENTDOWN_STATUS
[852] THEN
[853] detailed_row.stat_agent_down := status_count;
[854] suspended_bucket := suspended_bucket + status_count;
[856] END IF;
[858] -- Stopped
[859] --
[860] IF status_code = MGMT_JOBS.STOPPED_STATUS
[861] THEN
[862] detailed_row.stat_stopped := status_count;
[863] problem_bucket := problem_bucket + status_count;
[864] END IF;
[866] -- Suspended
[867] --
[868] IF status_code = MGMT_JOBS.SUSPENDED_STATUS
[869] THEN
[870] detailed_row.stat_suspended := status_count;
[871] suspended_bucket := suspended_bucket + status_count;
[872] END IF;
[874] -- Suspended - lock
[875] --
[876] IF status_code = MGMT_JOBS.SUSPENDED_LOCK_STATUS
[877] THEN
[878] detailed_row.stat_suspended_lock := status_count;
[879] suspended_bucket := suspended_bucket + status_count;
[880] END IF;
[882] -- Suspended - event (resource unavailable)
[883] --
[884] IF status_code = MGMT_JOBS.SUSPENDED_EVENT_STATUS
[885] THEN
[886] detailed_row.stat_suspended_event := status_count;
[887] suspended_bucket := suspended_bucket + status_count;
[888] END IF;
[890] -- Suspended - blackout
[891] --
[892] IF status_code = MGMT_JOBS.SUSPENDED_BLACKOUT_STATUS
[893] THEN
[894] detailed_row.stat_suspended_blackout := status_count;
[895] suspended_bucket := suspended_bucket + status_count;
[896] END IF;
[898] -- TO DO: Add support for the following status codes when added to the
[899] -- backend (also add to detailed_row reset code):
[900] --
[901] -- RESTARTED status (add to active bucket as well)
[902] -- INACTIVE status
[903] -- FAILED_INITIALIZATION status (add to problem bucket as well)
[904] --
[906] END LOOP;
[907] CLOSE getStatusCodeCursor;
[909] -- Set the status bucket values into the return table row
[910] --
[911] detailed_row.stat_bucket_problem := problem_bucket;
[912] detailed_row.stat_bucket_suspended := suspended_bucket;
[913] detailed_row.stat_bucket_active := active_bucket;
[915] -- Set the run record into the return table
[916] --
[917] recCount := recCount + 1;
[918] run_table(recCount) := detailed_row;
[920] -- Reset values for next Job Run
[921] active_bucket := 0;
[922] suspended_bucket := 0;
[923] problem_bucket := 0;
[925] detailed_row.stat_failed := 0;
[926] detailed_row.stat_completed := 0;
[927] detailed_row.stat_scheduled := 0;
[928] detailed_row.stat_executing := 0;
[929] detailed_row.stat_aborted := 0;
[930] detailed_row.stat_agent_down := 0;
[931] detailed_row.stat_stopped := 0;
[933] detailed_row.stat_suspended := 0;
[934] detailed_row.stat_suspended_blackout := 0;
[935] detailed_row.stat_suspended_event := 0;
[936] detailed_row.stat_suspended_lock := 0;
[938] detailed_row.stat_bucket_problem := 0;
[939] detailed_row.stat_bucket_suspended := 0;
[940] detailed_row.stat_bucket_active := 0;
[942] target_count := 0;
[943] detailed_row.group_flag := 0;
[944] detailed_row.target_name := NULL;
[945] detailed_row.target_type := NULL;
[946] detailed_row.target_guid := NULL;
[947] detailed_row.target_count := 0;
[949] detailed_row.scheduled_time := NULL;
[950] detailed_row.next_scheduled := NULL;
[951] detailed_row.timezone_region := NULL;
[953]-- detailed_row.job_access := 0;
[955] END LOOP;
[957] -- Revert to actual current user if this query was executed as a super-user
[958] --
[959] IF ignore_access_flag_in = 1
[960] THEN
[961] MGMT_USER.leave_super_user_mode (tmp_current_user );
[962] END IF;
[964] CLOSE run_data_cursor;
[966] RETURN run_table;
[967]END get_runs_as_table;
[970]FUNCTION exec_query (job_name_in IN VARCHAR2,
[971] job_type_in IN VARCHAR2,
[972] job_owner_in IN VARCHAR2,
[973]-- job_status_in IN INTEGER,
[974] status_clause_in IN VARCHAR2,
[975] target_name_in IN VARCHAR2,
[976] target_type_in IN VARCHAR2,
[977] target_guid_in IN RAW,
[978]-- target_clause_in IN VARCHAR2,
[979] timeframe_in IN INTEGER,
[980]-- for_members_in IN VARCHAR2,
[981] for_members_clause_in IN VARCHAR2,
[982] sort_by_in IN VARCHAR2,
[983] sort_order_in IN VARCHAR2,
[984] is_group_flag_in IN INTEGER,
[985] show_sys_jobs_in IN INTEGER
[986] )
[988] RETURN VARCHAR2 IS
[990] exec_sql VARCHAR(5000) := '';
[992] select_clause VARCHAR(1000) := 'SELECT Job.job_type, Job.job_name, ' ||
[993] ' Job.job_owner, ' ||
[994] ' JobSumm.scheduled_time, JobSumm.timezone_region, ' ||
[995] ' JobSumm.status, ' ||
[996] ' JobSumm.job_id, JobSumm.execution_id, ' ||
[997] ' JobSched.frequency_code ';
[999] from_clause VARCHAR(500) := ' FROM MGMT_JOB Job, MGMT_JOB_EXEC_SUMMARY JobSumm, MGMT_JOB_SCHEDULE JobSched ';
[1000]-- || ', MGMT_JOB_TARGET JobTarg ';
[1002] where_clause VARCHAR(3000) := ' WHERE JobSumm.job_id = Job.job_id ' ||
[1003] ' AND Job.schedule_id = JobSched.schedule_id ' ||
[1004] ' AND Job.is_library = 0';
[1006] where_clause_nonGroup VARCHAR(200) := ' AND JobSumm.execution_id = JobTarg.execution_id ';
[1009] target_clause VARCHAR (1000) := '';
[1012] -- Add execution join to where clause for non-groups
[1013] -- If it is a group the where clause execution joins differ depending on selection
[1014] -- against the group itself vs. members
[1015] --
[1016]-- IF is_group_flag_in = 0
[1017]-- THEN
[1018]-- where_clause := where_clause || where_clause_nonGroup;
[1019]-- END IF;
[1021] -- Add system job filter to where clause
[1022] --
[1023] IF show_sys_jobs_in = 0
[1024] THEN
[1025] where_clause := where_clause || ' AND Job.system_job = 0 ';
[1026] ELSE
[1027] where_clause := where_clause || ' AND Job.system_job = 1 ';
[1028] END IF;
[1030] -- Add job name filter to where clause
[1031] --
[1032] IF job_name_in IS NOT NULL
[1033] THEN
[1034] where_clause := where_clause || ' and Job.job_name like ''' || job_name_in || ''' ';
[1035] END IF;
[1037] -- Add job type filter to where clause
[1038] --
[1039] IF job_type_in IS NOT NULL
[1040] THEN
[1041] where_clause := where_clause || ' and Job.job_type = ''' || job_type_in || '''';
[1042] END IF;
[1044] -- Add job owner filter to where clause
[1045] --
[1046] IF job_owner_in IS NOT NULL
[1047] THEN
[1048] where_clause := where_clause || ' and Job.job_owner like ''' || job_owner_in || '''';
[1049] END IF;
[1051] -- Add target clause
[1052] --
[1053] IF target_name_in IS NOT NULL or target_type_in IS NOT NULL
[1054] THEN
[1055] -- This is the targets clause for non-group targets. It can take a target name
[1056] -- and/or type
[1057] IF is_group_flag_in = 0
[1058] THEN
[1059] target_clause := target_clause || ' AND JobSumm.EXECUTION_ID IN ' ||
[1060] ' ( SELECT EXECUTION_ID ' ||
[1061] ' FROM MGMT_JOB_TARGET jobTgt, MGMT_TARGETS tgt ' ||
[1062] ' WHERE jobTgt.target_guid = tgt.target_guid ';
[1063] IF target_name_in IS NOT NULL
[1064] THEN
[1065] target_clause := target_clause || ' AND tgt.display_name like ''' || target_name_in || '''';
[1066] END IF;
[1068] IF target_type_in IS NOT NULL
[1069] THEN
[1070] target_clause := target_clause || ' AND tgt.target_type = ''' || target_type_in || '''';
[1071] END IF;
[1073] target_clause := target_clause || ')';
[1075] ELSE -- is Group = 1
[1076] IF for_members_clause_in IS NULL
[1077] THEN
[1078] from_clause := from_clause || ', MGMT_JOB_TARGET JobTarg ';
[1080] target_clause := target_clause || ' AND JobTarg.execution_id = ''' || MGMT_JOB_ENGINE.NO_EXECUTION || '''' ||
[1081] ' AND JobSumm.job_id = JobTarg.job_id ' ||
[1082] ' AND JobTarg.target_guid = HEXTORAW(''' || target_guid_in || ''')';
[1084] END IF;
[1085] END IF;
[1086] -- Add the target clause to the where clause
[1087] --
[1088] where_clause := where_clause || target_clause;
[1089] END IF;
[1091] -- Add status clause
[1092] --
[1093] IF status_clause_in IS NOT NULL
[1094] THEN
[1095] where_clause := where_clause || status_clause_in;
[1096] END IF;
[1098] -- Add forMembers clause
[1099] -- (for when coming from "Job Group Members" link in Group job activity table link)
[1100] -- need to add mgmt_target_memberships to from clause to join with that table
[1101] --
[1102] IF for_members_clause_in IS NOT NULL
[1103] THEN
[1104] from_clause := from_clause || ' , MGMT_TARGET_MEMBERSHIPS members, MGMT_JOB_TARGET JobTarg ';
[1105] where_clause := where_clause || for_members_clause_in;
[1106] END IF;
[1108] -- Add timeframe filter to where clause
[1109] --
[1110] IF timeframe_in IS NOT NULL AND timeframe_in != 0
[1111] THEN
[1112] where_clause := where_clause || ' and JobSumm.scheduled_time > SYSDATE - ' || timeframe_in;
[1113] END IF;
[1115] -- Add sorting to statement
[1116] --
[1117] IF sort_by_in IS NOT NULL
[1118] THEN
[1119] where_clause := where_clause || ' order by ' || sort_by_in || ' ' || sort_order_in;
[1120] END IF;
[1123] exec_sql := exec_sql || select_clause || from_clause || where_clause;
[1125] return exec_sql;
[1129]-- --------------------------------------------------------------------------------
[1130]-- get_exec_data() - returns data for job executions
[1131]-- data is returned via a cursor output parameter
[1132]-- --------------------------------------------------------------------------------
[1133]PROCEDURE get_exec_data(
[1134] job_name_in IN VARCHAR2,
[1135] job_type_in IN VARCHAR2,
[1136] job_owner_in IN VARCHAR2,
[1137]-- job_status_in IN INTEGER,
[1138] status_clause_in IN VARCHAR2,
[1139] target_name_in IN VARCHAR2,
[1140] target_type_in IN VARCHAR2,
[1141] target_guid_in IN RAW,
[1142]-- target_clause_in IN VARCHAR2,
[1143] timeframe_in IN INTEGER,
[1144] show_forMembers_in IN INTEGER,
[1145] ignore_access_flag_in IN INTEGER,
[1146] for_members_clause_in IN VARCHAR2,
[1147] sort_by_in IN VARCHAR2,
[1148] sort_order_in IN VARCHAR2,
[1149] is_group_flag_in IN INTEGER,
[1150] show_sys_jobs_in IN INTEGER,
[1151] exec_tbl_data_out OUT CURSOR_TYPE)
[1152] IS
[1154] OPEN exec_tbl_data_out FOR
[1155] SELECT job_id,
[1156] execution_id,
[1157] job_name,
[1158] job_type,
[1159] owner,
[1160] target_guid,
[1161] target_name,
[1162] target_type,
[1163] target_count,
[1164]-- submitted_to_name,
[1165]-- submitted_to_type,
[1166]-- submitted_to_guid,
[1167]-- submitted_to_count,
[1168] scheduled_time,
[1169] timezone_region,
[1170] repeating,
[1171] status
[1172] FROM TABLE(CAST(get_execs_as_table(job_name_in,
[1173] job_type_in,
[1174] job_owner_in,
[1175]-- job_status_in,
[1176] status_clause_in,
[1177] target_name_in,
[1178] target_type_in,
[1179] target_guid_in,
[1180]-- target_clause_in,
[1181] timeframe_in,
[1182] show_forMembers_in,
[1183] ignore_access_flag_in,
[1184] for_members_clause_in,
[1185] sort_by_in,
[1186] sort_order_in,
[1187] is_group_flag_in,
[1188] show_sys_jobs_in)
[1189] AS jobExecTableType));
[1191]END get_exec_data;
[1193]-- --------------------------------------------------------------------------------
[1194]-- get_execs_as_table() - Cals get_exec_data() which gathers the job execution data
[1195]-- and returns a table type.
[1196]-- --------------------------------------------------------------------------------
[1197]FUNCTION get_execs_as_table(
[1198] job_name_in IN VARCHAR2,
[1199] job_type_in IN VARCHAR2,
[1200] job_owner_in IN VARCHAR2,
[1201]-- job_status_in IN INTEGER,
[1202] status_clause_in IN VARCHAR2,
[1203] target_name_in IN VARCHAR2,
[1204] target_type_in IN VARCHAR2,
[1205] target_guid_in IN RAW,
[1206]-- target_clause_in IN VARCHAR2,
[1207] timeframe_in IN INTEGER,
[1208] show_forMembers_in IN INTEGER,
[1209] ignore_access_flag_in IN INTEGER,
[1210]-- group_name_in IN VARCHAR2,
[1211]-- group_type_in IN VARCHAR2,
[1212] for_members_clause_in IN VARCHAR2,
[1213] sort_by_in IN VARCHAR2,
[1214] sort_order_in IN VARCHAR2,
[1215] is_group_flag_in IN INTEGER,
[1216] show_sys_jobs_in IN INTEGER)
[1217] RETURN jobExecTableType
[1218] IS
[1220] exec_sql VARCHAR2(3000) := '';
[1221] recCount NUMBER := 0;
[1222] exec_data_cursor CURSOR_TYPE;
[1223] target_count NUMBER := 0;
[1224] target_name VARCHAR2(256) := '';
[1225] target_type VARCHAR2(128) := '';
[1226] target_guid RAW(16);
[1227] tmp_current_user VARCHAR2(32);
[1228] is_group NUMBER := 0;
[1230] exec_row executionRowType := executionRowType (NULL, NULL, NULL, NULL,
[1231] NULL, NULL, NULL, NULL,
[1232] 0, 0, NULL, NULL,
[1233] NULL, 0);
[1234] exec_table jobExecTableType := jobExecTableType();
[1236] CURSOR getTargetInfoCursor (job_id_in RAW, execution_id_in RAW)
[1237] IS
[1238] SELECT jobTarget.target_guid, Targets.display_name, Targets.type_display_name
[1239] FROM mgmt_job_target jobTarget, mgmt_targets Targets
[1240] WHERE jobTarget.job_id = job_id_in
[1241] AND jobTarget.execution_id = execution_id_in
[1242] AND jobTarget.target_guid = Targets.target_guid;
[1245]-- CURSOR getExecTargetSubmittedToCursor (job_id_in RAW)
[1246]-- IS
[1247]-- SELECT Targets.target_guid, Targets.display_name, Targets.type_display_name
[1248]-- FROM MGMT_JOB_TARGET jobTargets,
[1249]-- MGMT_TARGETS Targets,
[1250]-- MGMT_TARGET_MEMBERSHIPS tm
[1251]-- WHERE jobTargets.job_id = job_id_in
[1252]-- AND jobTargets.execution_id = '0000000000000000'
[1253]-- AND jobTargets.target_guid = Targets.target_guid
[1254]-- GROUP BY Targets.target_guid, Targets.display_name, Targets.type_display_name;
[1259] exec_sql := exec_query(job_name_in, job_type_in, job_owner_in, status_clause_in,
[1260] target_name_in, target_type_in, target_guid_in,
[1261] timeframe_in, for_members_clause_in,
[1262] sort_by_in, sort_order_in, is_group_flag_in, show_sys_jobs_in);
[1264]DBMS_OUTPUT.PUT_LINE('EXECUTIONS QUERY -----------------------------------------------------');
[1265]DBMS_OUTPUT.PUT_LINE(SUBSTR(exec_sql, 0, 254));
[1266]DBMS_OUTPUT.PUT_LINE(SUBSTR(exec_sql, 255, 254));
[1267]DBMS_OUTPUT.PUT_LINE(SUBSTR(exec_sql, 509, 254));
[1268]DBMS_OUTPUT.PUT_LINE(SUBSTR(exec_sql, 763, 254));
[1271]DBMS_OUTPUT.PUT_LINE('IGNORE ACCESS FLAG = ' || ignore_access_flag_in);
[1273] IF ignore_access_flag_in = 1
[1274] THEN
[1275] MGMT_USER.enter_super_user_mode (tmp_current_user );
[1276] END IF;
[1279] OPEN exec_data_cursor FOR exec_sql;
[1280] LOOP
[1281] FETCH exec_data_cursor INTO exec_row.job_type, exec_row.job_name,
[1282] exec_row.owner, exec_row.scheduled_time,
[1283] exec_row.timezone_region, exec_row.status,
[1284] exec_row.job_id, exec_row.execution_id,
[1285] exec_row.repeating;
[1286] EXIT WHEN exec_data_cursor%NOTFOUND;
[1288] exec_table.EXTEND;
[1290] -- Target cursor (loop through records and obtain target info)
[1291] --
[1292] OPEN getTargetInfoCursor (exec_row.job_id, exec_row.execution_id);
[1293] LOOP
[1294] FETCH getTargetInfoCursor INTO target_guid, target_name, target_type;
[1295] EXIT WHEN getTargetInfoCursor%NOTFOUND;
[1297] target_count := target_count + 1;
[1298] exec_row.target_type := target_type;
[1300] -- Only get the target name, type and guid for the first target. If there
[1301] -- are more than one targets in the job the UI shows the count. The link can
[1302] -- take the user to all executions at which time the targets can be seen.
[1303] -- IN THE EXEC JAVA CODE IT SETS THE NAME, TYPE, GUID EACH ROW RETURNED - ONLY FIRST (IF 1) OR LAST IS USED
[1304]-- IF target_count = 1
[1305]-- THEN
[1306] exec_row.target_name := target_name;
[1307] exec_row.target_type := target_type;
[1308] exec_row.target_guid := target_guid;
[1309]-- END IF;
[1311] END LOOP;
[1312] exec_row.target_count := target_count;
[1313] CLOSE getTargetInfoCursor;
[1315] -- Clear for re-use in submittedTo cursor
[1316] --
[1317] target_count := 0;
[1318] target_name := '';
[1319] target_type := '';
[1320] target_guid := '';
[1322] -- Set the execution record into the return table
[1323] --
[1324] recCount := recCount + 1;
[1325] exec_table(recCount) := exec_row;
[1327] target_count := 0;
[1329] END LOOP;
[1331] DBMS_OUTPUT.PUT_LINE('Record Count: ' || recCount);
[1333] -- Revert to actual current user if this query was executed as a super-user
[1334] --
[1335] IF ignore_access_flag_in = 1
[1336] THEN
[1337] MGMT_USER.leave_super_user_mode (tmp_current_user );
[1338] END IF;
[1340] CLOSE exec_data_cursor;
[1342] RETURN exec_table;
[1344]END get_execs_as_table;
[1347]-- Job Status Intg -> Target Home Pages Calls --
[1349]PROCEDURE get_status_rollup_counts( p_target_name IN VARCHAR2,
[1350] p_target_type IN VARCHAR2,
[1351] p_for_composite_members IN NUMBER DEFAULT 0,
[1352] p_ignore_access IN NUMBER DEFAULT 1,
[1353] p_timeframe IN NUMBER DEFAULT 7,
[1354] p_status_cursor OUT CURSOR_TYPE )
[1356] l_is_group MGMT_TARGETS.IS_GROUP%TYPE := 0;
[1357] l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
[1358] l_tmp_current_user VARCHAR2(32);
[1361] -- Not composite members
[1362] IF ( p_for_composite_members = 0 ) THEN
[1364] -- -------------------------------------------------------- --
[1365] -- Single Target Home Page or Group Home Page Group Column --
[1366] -- Show all jobs regardless of access for the job as long --
[1367] -- as user can VIEW the target --
[1368] -- -------------------------------------------------------- --
[1369] -- Target name present: single target or composite target
[1370] IF ( p_target_name IS NOT NULL AND LENGTH( p_target_name ) > 0 ) THEN
[1372] -- !!! Enter super user mode to leave VPD layer !!! --
[1373] IF ( p_ignore_access = 1 ) THEN
[1374] MGMT_USER.ENTER_SUPER_USER_MODE( l_tmp_current_user );
[1375] END IF ;
[1377] -- First look at if this is a group target
[1378] SELECT target_guid, is_group INTO l_target_guid, l_is_group
[1379] FROM mgmt_targets
[1380] WHERE target_name = p_target_name
[1381] AND target_type = p_target_type;
[1383] IF ( l_is_group = 0 ) THEN
[1384] -- -------------------------------------------------- --
[1385] -- Non-Group single target INCLUDING RAC and Clusters --
[1386] -- -------------------------------------------------- --
[1387] OPEN p_status_cursor FOR
[1388] SELECT /*+ INDEX(JobTgt) */
[1389] COUNT(JobSumm.execution_id) as status_count,
[1390] JobSumm.status_bucket as status
[1391] FROM MGMT_JOB_EXT_TARGETS JobTgt,
[1392] MGMT_JOB_EXEC_SUMMARY JobSumm
[1393] WHERE JobSumm.scheduled_time > SYSDATE - 7
[1394] AND JobSumm.status_bucket IN
[1395] ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
[1396] MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
[1397] MGMT_JOBS.STATUS_BUCKET_RUNNING,
[1398] MGMT_JOBS.STATUS_BUCKET_SCHEDULED
[1399] )
[1400] AND JobTgt.target_guid = HEXTORAW( l_target_guid )
[1401] AND JobTgt.execution_id = JobSumm.execution_id
[1402] AND JobTgt.job_id = JobSumm.job_id
[1403] GROUP BY JobSumm.status_bucket;
[1405]-- SELECT COUNT(JobSumm.execution_id) as status_count,
[1406]-- JobSumm.status_bucket as status
[1407]-- FROM MGMT_JOB_EXEC_SUMMARY JobSumm
[1408]-- WHERE JobSumm.scheduled_time > SYSDATE - 7
[1409]-- AND JobSumm.status_bucket IN
[1410]-- ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
[1411]-- MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
[1412]-- MGMT_JOBS.STATUS_BUCKET_RUNNING,
[1413]-- MGMT_JOBS.STATUS_BUCKET_SCHEDULED
[1414]-- )
[1415]-- AND EXISTS
[1416]-- ( SELECT *
[1417]-- FROM MGMT_JOB_TARGET JobTgt
[1418]-- WHERE JobTgt.target_guid = HEXTORAW( l_target_guid )
[1419]-- AND JobTgt.execution_id = JobSumm.execution_id )
[1420]-- GROUP BY JobSumm.status_bucket;
[1422] -- ------------------------------------------------------ --
[1423] -- End Non-Group single target INCLUDING RAC and Clusters --
[1424] -- ------------------------------------------------------ --
[1425] ELSE
[1426] -- ----------------------------- --
[1427] -- Group Home Page Group Column --
[1428] -- ----------------------------- --
[1430] OPEN p_status_cursor FOR
[1431] SELECT COUNT(*) as status_count,
[1432] JobSumm.status_bucket as status
[1433] FROM MGMT_JOB_EXEC_SUMMARY JobSumm
[1434] WHERE JobSumm.scheduled_time > SYSDATE - 7
[1435] AND JobSumm.status_bucket IN
[1436] ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
[1437] MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
[1438] MGMT_JOBS.STATUS_BUCKET_RUNNING,
[1439] MGMT_JOBS.STATUS_BUCKET_SCHEDULED
[1440] )
[1441] AND EXISTS
[1442] ( SELECT *
[1443] FROM MGMT_JOB_TARGET JobTgt,
[1444] MGMT_JOB Job
[1445] WHERE JobTgt.execution_id = HEXTORAW( MGMT_JOB_ENGINE.NO_EXECUTION )
[1446] AND JobTgt.target_guid = HEXTORAW( l_target_guid )
[1447] AND JobTgt.job_id = JobSumm.job_id
[1448] AND Job.job_id = JobSumm.job_id )
[1449] GROUP BY JobSumm.status_bucket;
[1451]-- SELECT COUNT(*) as status_count,
[1452]-- JobSumm.status_bucket as status
[1453]-- FROM MGMT_JOB_EXEC_SUMMARY JobSumm
[1454]-- WHERE JobSumm.scheduled_time > SYSDATE - 7
[1455]-- AND JobSumm.status_bucket IN
[1456]-- ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
[1457]-- MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
[1458]-- MGMT_JOBS.STATUS_BUCKET_RUNNING,
[1459]-- MGMT_JOBS.STATUS_BUCKET_SCHEDULED
[1460]-- )
[1461]-- AND EXISTS
[1462]-- ( SELECT *
[1463]-- FROM MGMT_JOB_EXT_TARGETS ExecTargets,
[1464]-- MGMT_JOB_TARGET JobTgt,
[1465]-- MGMT_JOB Job
[1466]-- WHERE JobSumm.job_id = Job.job_id
[1467]-- AND Job.is_library = 0
[1468]-- AND JobTgt.execution_id = HEXTORAW( MGMT_JOB_ENGINE.NO_EXECUTION )
[1469]-- AND JobTgt.target_guid = HEXTORAW( l_target_guid )
[1470]-- AND JobTgt.job_id = Job.job_id
[1471]-- AND ExecTargets.job_id = JobSumm.job_id
[1472]-- AND ExecTargets.execution_id = JobSumm.execution_id
[1473]-- )
[1474]-- GROUP BY JobSumm.status_bucket;
[1476] -- --------------------------------- --
[1477] -- END Group Home Page Group Column --
[1478] -- --------------------------------- --
[1480] END IF;
[1482] -- !!! Leave super user mode !!! --
[1483] IF ( p_ignore_access = 1 ) THEN
[1484] MGMT_USER.LEAVE_SUPER_USER_MODE( l_tmp_current_user );
[1485] END IF ;
[1486] -- -------------------------------------------------------- --
[1487] -- End Single Target Home Page or Group HP Group Column --
[1488] -- -------------------------------------------------------- --
[1490] ELSE
[1492] -- ---------------------------------------------------------- --
[1493] -- Used by Console Home Page: Show jobs on the targets --
[1494] -- on which user has VIEW privillege. Use VPD layer --
[1495] -- for both MGMT_JOB and MGMT_TARGETS --
[1496] -- ---------------------------------------------------------- --
[1497] -- Target type on Console Home
[1498] IF ( p_target_type IS NOT NULL AND LENGTH( p_target_type ) > 0 )
[1499] THEN
[1500] OPEN p_status_cursor FOR
[1501] SELECT COUNT(JobSumm.execution_id) as status_count,
[1502] status_bucket as status
[1503] FROM MGMT_JOB_EXEC_SUMMARY JobSumm
[1504] WHERE scheduled_time > SYSDATE - 7
[1505] AND ( status_bucket = MGMT_JOBS.STATUS_BUCKET_PROBLEM
[1506] or status_bucket = MGMT_JOBS.STATUS_BUCKET_SUSPENDED
[1507] )
[1508] AND EXISTS
[1509] ( SELECT *
[1510] FROM MGMT_JOB_EXT_TARGETS jt,
[1511] MGMT_TARGETS t,
[1512] MGMT_JOB j
[1513] WHERE j.job_id = jt.job_id
[1514] AND j.job_id = JobSumm.job_id
[1515] AND t.target_guid = jt.target_guid
[1516] AND t.target_type = p_target_type
[1517] AND jt.execution_id = JobSumm.execution_id
[1518] )
[1519] GROUP BY status_bucket;
[1520] ELSE
[1521] -- All targets
[1522] -- Exclude target-less jobs
[1524] OPEN p_status_cursor FOR
[1525] SELECT COUNT(JobSumm.execution_id) as status_count,
[1526] JobSumm.status_bucket as status
[1527] FROM MGMT_JOB_EXEC_SUMMARY JobSumm
[1528] WHERE JobSumm.scheduled_time > SYSDATE - 7
[1529] AND ( JobSumm.status_bucket = MGMT_JOBS.STATUS_BUCKET_PROBLEM
[1530] or JobSumm.status_bucket = MGMT_JOBS.STATUS_BUCKET_SUSPENDED
[1531] )
[1532] AND EXISTS
[1533] ( SELECT *
[1534] FROM MGMT_JOB_EXT_TARGETS jt,
[1535] MGMT_TARGETS t,
[1536] MGMT_JOB j
[1537] WHERE j.job_id = jt.job_id
[1538] AND j.job_id = JobSumm.job_id
[1539] AND t.target_guid = jt.target_guid
[1540] AND jt.execution_id = JobSumm.execution_id
[1541] )
[1542] GROUP BY status_bucket;
[1543] END IF;
[1545] -- ---------------------------------------------------------- --
[1546] -- End of Console Home Page --
[1547] -- ---------------------------------------------------------- --
[1549] END IF ;
[1551] -- Composite members
[1552] ELSE
[1554] -- !!! Enter super user mode to leave VPD layer !!! --
[1555] IF ( p_ignore_access = 1 ) THEN
[1556] MGMT_USER.ENTER_SUPER_USER_MODE( l_tmp_current_user );
[1557] END IF ;
[1559] -- Obtain composite guid for the group target
[1560] SELECT target_guid INTO l_target_guid
[1561] FROM mgmt_targets
[1562] WHERE target_name = p_target_name
[1563] AND target_type = p_target_type;
[1565] OPEN p_status_cursor FOR
[1566] SELECT COUNT(JobSumm.execution_id) as status_count,
[1567] JobSumm.status_bucket as status
[1568] FROM MGMT_JOB_EXEC_SUMMARY JobSumm
[1569] WHERE JobSumm.scheduled_time > SYSDATE - 7
[1570] AND JobSumm.status_bucket IN
[1571] ( MGMT_JOBS.STATUS_BUCKET_PROBLEM,
[1572] MGMT_JOBS.STATUS_BUCKET_SUSPENDED,
[1573] MGMT_JOBS.STATUS_BUCKET_RUNNING,
[1574] MGMT_JOBS.STATUS_BUCKET_SCHEDULED
[1575] )
[1576] AND EXISTS
[1577] ( SELECT *
[1578] FROM mgmt$group_flat_memberships,
[1579] mgmt_job_ext_targets JobTgt
[1580] WHERE composite_target_guid = HEXTORAW( l_target_guid )
[1581] AND member_target_guid = JobTgt.target_guid
[1582] AND JobTgt.job_id = JobSumm.job_id
[1583] AND JobTgt.execution_id = JobSumm.execution_id
[1584] )
[1585] GROUP BY JobSumm.status_bucket;
[1587] -- !!! Leave super user mode !!! --
[1588] IF ( p_ignore_access = 1 ) THEN
[1589] MGMT_USER.LEAVE_SUPER_USER_MODE( l_tmp_current_user );
[1590] END IF ;
[1592] END IF;
[1594]END get_status_rollup_counts;
[1597]-- End Job Status Intg -> Target Home Pages Calls --
[1602]-- Preferred Credential Validation Calls --
[1604]-- Get a list of targets that do NOT
[1605]-- have preferred credential set
[1606]-- For 4.1 this only handles:
[1607]-- A single group/composite target
[1608]-- A homogenous type of basic targets
[1609]PROCEDURE get_targets_prefcreds_not_set
[1610] ( p_target_list IN OUT MGMT_JOB_TARGET_LIST,
[1611] p_target_type IN VARCHAR2 DEFAULT NULL,
[1612] p_cred_set_name IN VARCHAR2 )
[1614] l_em_user VARCHAR2(32) := MGMT_USER.get_current_em_user();
[1615] l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE;
[1616] l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
[1617] l_target_list MGMT_JOB_TARGET_LIST; -- Needed to store targets have creds
[1618] l_cred_guid MGMT_ENTERPRISE_CREDENTIALS.CREDENTIAL_GUID%TYPE;
[1620] -- Call flattening function
[1621] -- Filter on p_target_type
[1622] get_flattened_member_targets( p_target_list, p_target_type );
[1624] -- dbms_output.put_line( ' Flattened target list: ' || p_target_list.COUNT );
[1626] IF ( p_target_list IS NOT NULL AND p_target_list.COUNT > 0 ) THEN
[1628] -- By this time it would be guaranteed that we
[1629] -- have a list of oracle_database,host,or cluster targets
[1630] -- Look at enterprise level first
[1631] -- Only deal with a type here
[1632] l_target_type := p_target_list(1).target_type;
[1634] BEGIN
[1635] SELECT credential_guid INTO l_cred_guid
[1636] FROM MGMT_ENTERPRISE_CREDENTIALS
[1637] WHERE target_type = l_target_type
[1638] AND credential_set_name = p_cred_set_name
[1639] AND user_name = l_em_user;
[1640] EXCEPTION WHEN no_data_found THEN NULL;
[1641] END;
[1643] IF ( l_cred_guid IS NOT NULL ) THEN
[1644]-- dbms_output.put_line( ' Enterprise creds found ' );
[1645] p_target_list := NULL;
[1646] ELSE
[1647]-- dbms_output.put_line( ' No enterprise creds found ' );
[1649] -- Not on enterprise level
[1650] -- Check if target if on target-level (if contains cluster on cluster level)
[1651] -- For 4.1 we enforce cluster level credentials for jobs
[1652] -- Obtain all targets that have pref cred set
[1653] BEGIN
[1654] SELECT MGMT_JOB_TARGET_RECORD( t.target_name, t.target_type )
[1655] BULK COLLECT INTO l_target_list
[1656] FROM MGMT_TARGETS t, MGMT_TARGET_CREDENTIALS c
[1657] WHERE c.target_guid = t.target_guid
[1658] AND c.credential_set_name = p_cred_set_name
[1659] AND c.user_name = l_em_user;
[1660] EXCEPTION WHEN NO_DATA_FOUND THEN
[1661] l_target_list := NULL;
[1662] NULL;
[1663] END;
[1665] IF l_target_list IS NOT NULL AND l_target_list.count > 0 THEN
[1666] -- Filter out any good targets from p_target_list
[1667] FOR i IN l_target_list.FIRST..l_target_list.LAST LOOP
[1669] IF ( p_target_list IS NULL OR -- not expected
[1670] p_target_list.FIRST IS NULL OR
[1671] p_target_list.COUNT <= 0
[1672] ) THEN
[1673] EXIT; -- already all pruned.
[1674] END IF;
[1676] FOR j IN p_target_list.FIRST..p_target_list.LAST LOOP
[1677] IF ( p_target_list.EXISTS(j) AND
[1678] l_target_list(i).target_name = p_target_list(j).target_name AND
[1679] l_target_list(i).target_type = p_target_list(j).target_type ) THEN
[1680] p_target_list.DELETE(j);
[1681] EXIT;
[1682] END IF;
[1683] END LOOP;
[1684] END LOOP;
[1685] END IF;
[1687] END IF; -- End validate at Enterprise Level
[1688] END IF;
[1690]END get_targets_prefcreds_not_set;
[1693]-- Private helper that 'flattens' a group
[1694]-- into a member list.
[1695]-- For 4.1 this only handles:
[1696]-- Single 'true' group target
[1697]PROCEDURE get_flattened_member_targets
[1698] ( p_target_list IN OUT MGMT_JOB_TARGET_LIST,
[1699] p_target_type IN VARCHAR2 )
[1701] l_target MGMT_JOB_TARGET_RECORD;
[1702] l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
[1703] l_target_type MGMT_TARGETS.target_type%TYPE; -- to store rac or cluster type
[1706] IF p_target_list IS NULL THEN
[1707] RETURN;
[1708] END IF;
[1710] FOR i IN p_target_list.FIRST..p_target_list.LAST LOOP
[1712] IF ( p_target_list(i).target_type = MGMT_GLOBAL.G_DATABASE_GROUP_TARGET_TYPE OR
[1713] p_target_list(i).target_type = MGMT_GLOBAL.G_HOST_GROUP_TARGET_TYPE OR
[1714] p_target_list(i).target_type = MGMT_GLOBAL.G_COMPOSITE_TARGET_TYPE ) THEN
[1716] IF ( p_target_list.COUNT > 1 ) THEN
[1717] -- Either not a single group, or a list of non-group with group target
[1718] RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_FLATTENING_INPUT_ERR,
[1719] 'A Group must be the only target in the list.');
[1720] END IF ;
[1722] l_target := p_target_list(i);
[1723] l_target_guid :=
[1724] MGMT_TARGET.get_target_guid( l_target.target_name, l_target.target_type );
[1726]-- dbms_output.put_line( ' Composite Guid ' || l_target_guid );
[1728] p_target_list := MGMT_JOB_TARGET_LIST();
[1730] -- -------------------------------------------------------- --
[1731] -- For composite targets: the fact that they can contain --
[1732] -- rac or clusters requires special treatment. E.g. --
[1733] -- A composite A that contains --
[1734] -- db1, db2, rac1 where rac1 contains db1, db3 --
[1735] -- The following query returns only db1, db2 and rac1: i.e.,--
[1736] -- We only get RAC / Cluster level target, and prune any --
[1737] -- targets that are derived members of the Group --
[1738] -- -------------------------------------------------------- --
[1739] IF ( p_target_type IS NOT NULL AND
[1740] l_target.target_type = MGMT_GLOBAL.G_COMPOSITE_TARGET_TYPE ) THEN
[1742] -- Allowing rac or cluster based on override target type
[1743] IF ( p_target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE ) THEN
[1744] l_target_type := MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE;
[1745] ELSE
[1746] l_target_type := MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE;
[1747] END IF;
[1749] BEGIN
[1751] SELECT MGMT_JOB_TARGET_RECORD( member_target_name, member_target_type )
[1752] BULK COLLECT INTO p_target_list
[1753] FROM mgmt$group_derived_memberships
[1754] WHERE ( member_target_type = p_target_type -- basic type
[1755] OR
[1756] member_target_type = l_target_type -- rac type
[1757] )
[1758] AND composite_target_guid = l_target_guid;
[1760] EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
[1761] END;
[1763] ELSE
[1764] -- -------------------------------------------------------- --
[1765] -- A single database or host group case --
[1766] -- -------------------------------------------------------- --
[1767] BEGIN
[1768] SELECT MGMT_JOB_TARGET_RECORD( member_target_name, member_target_type )
[1769] BULK COLLECT INTO p_target_list
[1770] FROM mgmt$group_flat_memberships
[1771] WHERE ( member_target_type = MGMT_GLOBAL.G_DATABASE_TARGET_TYPE
[1772] OR
[1773] member_target_type = MGMT_GLOBAL.G_HOST_TARGET_type
[1774] )
[1775] AND composite_target_guid = l_target_guid;
[1776] EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
[1777] END;
[1778] END IF;
[1780] END IF;
[1781] END LOOP;
[1783]END get_flattened_member_targets;
[1785]-- End Preferred Credential Validation Calls --
[1789]-- Library Calls --
[1790]-- p_system_job: show system job or not default not shown --
[1791]-- p_job_type: job type filter if null show all jobs --
[1792]-- p_job_name: job name filter if null show all jobs --
[1793]-- p_job_owner: job owner filter if null show all jobs --
[1794]-- p_ascending: order by ASC or DESC default ASC --
[1795]-- p_order_by: column name order by is operated on --
[1796]-- This call would get a cursor of library jobs. If the --
[1797]-- current em user is the super user, all jobs matching --
[1798]-- search filters will be returned. Otherwise only jobs --
[1799]-- that the user owns or has at least VIEW access to are --
[1800]-- returned. The 'access' value could be one of 'OWNER' --
[1801]-- 'VIEW_JOB' or 'FULL_JOB' that should be nlsed by the --
[1802]-- caller. (These are IDs as well) --
[1804]PROCEDURE get_library_data( p_system_job IN NUMBER DEFAULT 0,
[1805] p_job_type IN VARCHAR2,
[1806] p_job_name IN VARCHAR2,
[1807] p_job_owner IN VARCHAR2,
[1808] p_ascending IN NUMBER DEFAULT 1,
[1809] p_order_by IN VARCHAR2,
[1810] p_library_cursor OUT CURSOR_TYPE )
[1812] l_em_user VARCHAR2(32) := MGMT_USER.get_current_em_user();
[1813] l_job_name VARCHAR2(64);
[1814] l_job_order_clause VARCHAR2(128);
[1815] l_job_where_clause VARCHAR2(2000);
[1816] l_job_library_stmt VARCHAR2(4000);
[1817] l_job_groupby_clause1 VARCHAR2(128) :=
[1818] 'GROUP BY Job.job_name, Job.job_type, Job.job_description, ' ||
[1819] 'Job.job_owner, Job.job_id, tgt.type_display_name ' ;
[1820] l_job_groupby_clause2 VARCHAR2(128) :=
[1821] 'GROUP BY Job.job_name, Job.job_type, Job.job_description, ' ||
[1822] 'up.priv_name, Job.job_owner, Job.job_id, tgt.type_display_name ';
[1825] -- System Job
[1826] IF ( p_system_job = 0 ) THEN
[1827] l_job_where_clause := ' AND Job.system_job = 0 ';
[1828] ELSE
[1829] l_job_where_clause := ' AND Job.system_job > 0 ';
[1830] END IF;
[1832] -- Job Type
[1833] IF ( p_job_type IS NOT NULL AND LENGTH( p_job_type ) > 0 ) THEN
[1834] l_job_where_clause := l_job_where_clause || ' AND Job.job_type = ''' || p_job_type || ''' ';
[1835] END IF;
[1837] -- Job Name
[1838] IF ( p_job_name IS NOT NULL AND LENGTH( p_job_name ) > 0 ) THEN
[1839] l_job_name := '%' || UPPER(p_job_name) || '%';
[1840] l_job_where_clause := l_job_where_clause || ' AND Job.job_name LIKE ''' || l_job_name || ''' ';
[1841] END IF;
[1843] -- Job Owner
[1844] IF ( p_job_owner IS NOT NULL AND LENGTH( p_job_owner ) > 0 ) THEN
[1845] l_job_where_clause := l_job_where_clause || ' AND Job.job_owner = ''' || p_job_owner || ''' ';
[1846] END IF;
[1848] IF ( p_order_by IS NOT NULL AND LENGTH( p_order_by ) > 0 ) THEN
[1849] IF ( p_ascending = 1 ) THEN
[1850] l_job_order_clause := ' ORDER BY ' || p_order_by || ' ';
[1851] ELSE
[1852] l_job_order_clause := ' ORDER BY ' || p_order_by || ' DESC ';
[1853] END IF;
[1854] END IF;
[1856] -- Common statement body: bind l_em_user
[1857] l_job_library_stmt :=
[1858] 'SELECT Job.job_type as job_type, ' || -- Start owned by user
[1859] 'Job.job_name as job_name, ' ||
[1860] 'Job.job_description as job_description, ' ||
[1861] '''OWNER'' as job_access, ' ||
[1862] 'Job.job_owner as job_owner, ' ||
[1863] 'COUNT(*) as target_count, ' ||
[1864] 'tgt.type_display_name as target_type, ' ||
[1865] 'Job.job_id as job_id '||
[1866] 'FROM MGMT_JOb Job, ' ||
[1867] 'MGMT_JOB_TARGET jobTgt, ' ||
[1868] 'MGMT_TARGETS tgt ' ||
[1869] 'WHERE Job.is_library = 1 ' ||
[1870] 'AND Job.job_id = jobTgt.job_id (+) ' || -- Target-less jobs
[1871] 'AND jobTgt.target_guid = tgt.target_guid (+) ' ||
[1872] 'AND Job.job_owner = :em_user ' ||
[1873] l_job_where_clause || l_job_groupby_clause1;
[1875] IF MGMT_USER.has_priv( l_em_user, MGMT_USER.SUPER_USER ) = 1 THEN
[1877] -- Super user, all jobs should be returned.
[1878] -- Access is OWNER if super user owns a job, otherwise
[1879] -- See if granted FULL, if not return VIEW
[1880] -- Bind VIEW_JOB, l_em_user, FULL_JOB, l_em_user
[1881] l_job_library_stmt := l_job_library_stmt ||
[1882] 'UNION ' || -- Start FULL_JOB, if not found, VIEW
[1883] 'SELECT Job.job_type as job_type, ' ||
[1884] 'Job.job_name as job_name, ' ||
[1885] 'Job.job_description as job_description, ' ||
[1886] 'NVL(up.priv_name, ''VIEW_JOB'') as job_access, ' ||
[1887] 'Job.job_owner as job_owner, ' ||
[1888] 'COUNT(*) as target_count, ' ||
[1889] 'tgt.type_display_name as target_type, ' ||
[1890] 'Job.job_id as job_id ' ||
[1891] 'FROM MGMT_JOB Job, ' ||
[1892] 'MGMT_JOB_TARGET jobTgt, ' ||
[1893] 'MGMT_TARGETS tgt, ' ||
[1894] 'MGMT_PRIV_GRANTS up ' ||
[1895] 'WHERE Job.is_library = 1 ' ||
[1896] 'AND Job.job_id = jobTgt.job_id (+) ' ||
[1897] 'AND jobTgt.target_guid = tgt.target_guid (+) ' ||
[1898] 'AND up.grantee (+) = :em_user ' ||
[1899] 'AND up.priv_name (+) = :full ' ||
[1900] 'AND up.guid (+) = Job.job_id ' ||
[1901] 'AND Job.job_owner != :em_user ' || -- Only for jobs not owned by current user
[1902] l_job_where_clause || l_job_groupby_clause2 || l_job_order_clause ;
[1904] OPEN p_library_cursor FOR l_job_library_stmt
[1905] USING l_em_user, l_em_user, mgmt_user.full_job, l_em_user;
[1907] ELSE
[1909] -- Not a Super User
[1910] -- Bind: FULL_JOB, l_em_user, FULL_JOB; VIEW_JOB, l_em_user, VIEW_JOB, l_em_user, FULL_JOB
[1911] l_job_library_stmt := l_job_library_stmt ||
[1912] 'UNION ' || -- Start FULL_JOB access: Provided by Anila.Holser
[1913] 'SELECT Job.job_type as job_type, ' ||
[1914] 'Job.job_name as job_name, ' ||
[1915] 'Job.job_description as job_description, ' ||
[1916] '''FULL_JOB'' as job_access, ' ||
[1917] 'Job.job_owner as job_owner, ' ||
[1918] 'COUNT(*) as target_count, ' ||
[1919] 'tgt.type_display_name as target_type, ' ||
[1920] 'Job.job_id as job_id ' ||
[1921] 'FROM MGMT_JOB Job, ' ||
[1922] 'MGMT_JOB_TARGET jobTgt, ' ||
[1923] 'MGMT_TARGETS tgt, ' ||
[1924] 'MGMT_PRIV_GRANTS up ' ||
[1925] 'WHERE Job.is_library = 1 ' ||
[1926] 'AND Job.job_id = jobTgt.job_id (+) '||
[1927] 'AND jobTgt.target_guid = tgt.target_guid (+) ' ||
[1928] 'AND up.grantee = :em_user ' ||
[1929] 'AND up.priv_name = :full ' ||
[1930] 'AND up.guid = Job.job_id ' ||
[1931] 'AND Job.job_owner != :em_user ' || -- Necessary because FULL is granted to owner now!
[1932] l_job_where_clause || l_job_groupby_clause2 ||
[1933] 'UNION ' || -- Start VIEW_JOB access
[1934] 'SELECT Job.job_type as job_type, ' ||
[1935] 'Job.job_name as job_name, ' ||
[1936] 'Job.job_description as job_description, ' ||
[1937] '''VIEW_JOB'' as job_access, ' ||
[1938] 'Job.job_owner as job_owner, ' ||
[1939] 'COUNT(*) as target_count, ' ||
[1940] 'tgt.type_display_name as target_type, ' ||
[1941] 'Job.job_id as job_id ' ||
[1942] 'FROM MGMT_JOB Job, ' ||
[1943] 'MGMT_JOB_TARGET jobTgt, ' ||
[1944] 'MGMT_TARGETS tgt, ' ||
[1945] 'MGMT_PRIV_GRANTS up ' ||
[1946] 'WHERE Job.is_library = 1 ' ||
[1947] 'AND Job.job_id = jobTgt.job_id (+) ' ||
[1948] 'AND jobTgt.target_guid = tgt.target_guid (+) ' ||
[1949] 'AND up.grantee = :em_user ' ||
[1950] 'AND up.priv_name = :view_string ' ||
[1951] 'AND up.guid = Job.job_id ' ||
[1952] 'AND Job.job_owner != :em_user ' ||
[1953] 'AND up.guid NOT IN ' || -- Excluding FULL_JOB entry
[1954] ' ( SELECT guid ' ||
[1955] 'FROM mgmt_priv_grants ' ||
[1956] 'WHERE grantee = :em_user ' ||
[1957] 'AND priv_name = :full ) ' ||
[1958] l_job_where_clause || l_job_groupby_clause2 || l_job_order_clause;
[1960] OPEN p_library_cursor FOR l_job_library_stmt
[1961] USING l_em_user,
[1962] l_em_user, mgmt_user.full_job, l_em_user,
[1963] l_em_user, mgmt_user.view_job, l_em_user, l_em_user, mgmt_user.full_job;
[1965] END IF;
[1967]END get_library_data;
[1970]-- End Library Calls --
[1974]-- End package
[1976]END MGMT_JOB_UI;
Regards
Hristo
|
|
|
|
|
|
|
|
|
|
| Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #350389 is a reply to message #348602] |
Wed, 24 September 2008 22:25 |
naveenmelbourne
Messages: 15
Registered: August 2007
|
Junior Member |
|
|
Hi Friend,
first remidy is run utlrp.sql
second is as follows.
The best way to fix the problem with Invalid Objects is Enterprise Manager. Connect to the EM and Select the object and compile it. It will pop up with the errors and you will know what sort of errors and why. You can fix them straight away.
Thats what I did when I found invalids
Naveen
|
|
|
|
|
|
| Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #350449 is a reply to message #348602] |
Thu, 25 September 2008 01:58 |
hristo
Messages: 258
Registered: May 2007
|
Senior Member |
|
|
Hi!
For those who missed the errors on the beginning:
Line # = 72 Column # = 11 Error Text = PLS-00323: subprogram or cursor 'GET_LIBRARY_DATA' is declared in a package specification and must be defined in the package body
Line # = 576 Column # = 7 Error Text = PL/SQL: SQL Statement ignored
Line # = 620 Column # = 12 Error Text = PL/SQL: ORA-00902: invalid datatype
Line # = 629 Column # = 1 Error Text = PL/SQL: Item ignored
Line # = 644 Column # = 10 Error Text = PLS-00201: identifier 'JOBRUNTABLETYPE' must be declared
Line # = 1155 Column # = 7 Error Text = PL/SQL: SQL Statement ignored
Line # = 1189 Column # = 12 Error Text = PL/SQL: ORA-00902: invalid datatype
Line # = 1197 Column # = 1 Error Text = PL/SQL: Item ignored
Line # = 1217 Column # = 10 Error Text = PLS-00201: identifier 'JOBEXECTABLETYPE' must be declared
Regards
Hristo
|
|
|
|
| Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #350455 is a reply to message #350449] |
Thu, 25 September 2008 02:16 |
 |
Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | PLS-00323: subprogram or cursor "string" is declared in a package specification and must be defined in the package body
Cause: A subprogram specification was placed in a package specification, but the corresponding subprogram body was not placed in the package body. The package body implements the package specification. So, the package body must contain the definition of every subprogram declared in the package specification.
Action: Check the spelling of the subprogram name. If necessary, add the missing subprogram body to the package body.
|
| Quote: | PLS-00201: identifier "string" must be declared
Cause: You tried to reference either an undeclared variable, exception, procedure, or other item, or an item to which no privilege was granted or an item to which privilege was granted only through a role.
Action: 1) Check your spelling and declaration of the referenced name. 2) Verify that the declaration for the referenced item is placed correctly in the block structure. 3) If the referenced item is indeed declared but you don"t have privileges to refer to that item, for security reasons, you will be notified only that the item is not declared. 4) If the referenced item is indeed declared and you believe that you have privileges to refer to that item, check the privileges; if the privileges were granted only via a role, then this is expected and documented behavior. Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared.
|
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Mon Jul 08 01:05:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
