Home » RDBMS Server » Server Administration » Invalid Objects: SPACE_FREE & SPACE_AVAILABLE (RDBMS 10.2.0.4 win2003 x64)
Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #348602] Wed, 17 September 2008 07:03 Go to next message
hristo
Messages: 258
Registered: May 2007
Senior Member
Hi!

I have SPACE_FREE & SPACE_AVAILABLE as invalid objects in my databases. I have checked metalink and the documentation and found nothing.
Both are own by SYSTEM and are VIEWS.

Anyone have any info about this two objects?

Regards
Hristo

[Updated on: Wed, 17 September 2008 07:03]

Report message to a moderator

Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #348614 is a reply to message #348602] Wed, 17 September 2008 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are not RDBMS standard but you have their definition so maybe you can post it.

Regards
Michel

[Updated on: Wed, 17 September 2008 07:35]

Report message to a moderator

Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #349193 is a reply to message #348614] Fri, 19 September 2008 04:37 Go to previous messageGo to next message
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 #349197 is a reply to message #349193] Fri, 19 September 2008 04:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without the code, we can't help.
Some things don't exist.

Regards
Michel
Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #349222 is a reply to message #349197] Fri, 19 September 2008 06:30 Go to previous messageGo to next message
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 #349236 is a reply to message #349222] Fri, 19 September 2008 07:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think someone will count the lines?

Regards
Michel
Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #350213 is a reply to message #348602] Wed, 24 September 2008 07:23 Go to previous messageGo to next message
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 #350224 is a reply to message #350213] Wed, 24 September 2008 07:56 Go to previous messageGo to next message
ora110
Messages: 42
Registered: September 2007
Location: China
Member

so long .
Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #350240 is a reply to message #350213] Wed, 24 September 2008 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
After reading your post, I forgot what is the question.

Regards
Michel
Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #350256 is a reply to message #350240] Wed, 24 September 2008 09:07 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Great Michal,
And your post make one smile on my face Smile
Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #350389 is a reply to message #348602] Wed, 24 September 2008 22:25 Go to previous messageGo to next message
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 #350400 is a reply to message #350389] Wed, 24 September 2008 23:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You also forgot the beginning of the topic after reading!
The errors are listed in a previous post, far above.

Regards
Michel
Re: Invalid Objects: SPACE_FREE & SPACE_AVAILABLE [message #350449 is a reply to message #348602] Thu, 25 September 2008 01:58 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64119
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
Previous Topic: Problem in Oracle® Database Patch Set Notes 10g Release 2 (10.2.0.3) Patch Set 2 for Linux x86
Next Topic: Installation on Windows vista.
Goto Forum:
  


Current Time: Tue Dec 06 08:38:06 CST 2016

Total time taken to generate the page: 0.21870 seconds