Re: PL/SQL Problem

I see three issues with this code:

1. You don't need to declare job_rec before using it in the cursor-for loop. It's declared implicitly and used only in the loop. This isn't your error, however.
2. I've never selected from system.dba_jobs_running... either sys.dba_jobs_running or merely dba_jobs_running. Since those dba_* views usually all belong to sys, you should get an error trying to select from system.dba_*.
3. If you have been directly granted rights to this view, your procedure (and its users) may access the view through your rights. However, rights granted only through a role (like the DBA role) will not work. This is most likely the primary part of your error.

In article <3B86A313.A648A27C_at_bridgewatersystems.com>, Tony Walby <tony.walby_at_bridgewatersystems.com> wrote:
>Hey Guys,
>i got a bit of a problem. here is the code and the associated message.
>This is more than likely something small but I just can't figure it out.
>
>Code:
>
>create or replace procedure check_jobs_running
>as
>v_date_dd number(2);
>v_date_hh number(2);
>v_thisdate_dd number(2);
>v_thisdate_hh number(2);
>v_what varchar2(100);
>v_magic_number number := 06;
>cursor long_jobs is select * from system.dba_jobs_running;
>job_rec long_jobs%rowtype;
>
>begin
>for job_rec in long_jobs
> loop
> select to_number(to_char(sysdate, 'DD' )) into v_date_dd from dual;
> select to_number(to_char(sysdate, 'HH24' )) into v_date_hh from dual;
> select to_number(to_char(job_rec.this_date, 'DD' )) into v_thisdate_dd
>from system.dba_jobs_running;
> select to_number(substr(job_rec.last_sec, 1,2)) into v_thisdate_hh
>from system.dba_jobs_running;
> if (( v_date_dd > v_thisdate_dd) and (v_thisdate_hh > v_magic_number))
>
> then
> select what into v_what from system.dba_jobs where job = job_rec.job;
>
> elsif ((v_date_hh - v_thisdate_hh) > v_magic_number)
> then
> select what into v_what from system.dba_jobs where job = job_rec.job;
>
> end if;
>end loop;
>end;
>/
>
>Error:
>Errors for PROCEDURE CHECK_JOBS_RUNNING:
>
>LINE/COL ERROR
>--------
>-----------------------------------------------------------------
>9/8 PLS-00341: declaration of cursor 'LONG_JOBS' is incomplete or
> malformed
>
>10/2 PL/SQL: SQL Statement ignored
>10/16 PLS-00201: identifier 'SYSTEM.DBA_JOBS_RUNNING' must be
>declared
>11/9 PL/SQL: Item ignored
>18/3 PL/SQL: SQL Statement ignored
>18/79 PLS-00201: identifier 'SYSTEM.DBA_JOBS_RUNNING' must be
>declared
>19/3 PL/SQL: SQL Statement ignored
>19/75 PLS-00201: identifier 'SYSTEM.DBA_JOBS_RUNNING' must be
>declared
>22/4 PL/SQL: SQL Statement ignored
>22/33 PLS-00201: identifier 'SYSTEM.DBA_JOBS' must be declared
>25/4 PL/SQL: SQL Statement ignored
>25/33 PLS-00201: identifier 'SYSTEM.DBA_JOBS' must be declared
>
>Any help will be greate.
>
>Tony Walby
>oracle DBA
>Bridgewater Systems
>Kanata ON
>Canada
>
Received on Thu Aug 30 2001 - 08:15:49 CDT