sys object in procedure

[steeve123]

im creating a procedure that makes a query on v$archived_log.


but it wont compile, it says
LINE/COL ERROR
-------------------- -----------------------------------------------------------------
3/5 PL/SQL: SQL Statement ignored
4/12 PLS-00201: identifier 'SYS.V_$ARCHIVED_LOG' must be declared
8/5 PL/SQL: Statement ignored

I dont want to create that procedure in the user sys.
So what can i do?

[marist89]

You must have permission on V_$ARCHIVED_LOG granted explicitly. V_$ARCHIVED_LOG is the underlying object for the v$archived_log view.

[steeve123]

i have granted the view and it doesnt work

[marist89]

hmmm, works for me...


SQL> connect jeffhpc/jeffhpc
Connected.
SQL> desc v$archived_log
ERROR:
ORA-04043: object SYS.V_$ARCHIVED_LOG does not exist


SQL> create or replace procedure test1
2 is
3 i integer;
4 begin
5 i :=0;
6 for x in (select * from v$archived_log) loop
7 i :=i+1;
8 end loop;
9 dbms_output.put_line(i);
10 end;
11 /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE TEST1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/15 PL/SQL: SQL Statement ignored
6/29 PLS-00201: identifier 'SYS.V_$ARCHIVED_LOG' must be declared
SQL> connect sys
Enter password: **********
Connected.
SQL> grant select on v_$archived_log to jeffhpc;

Grant succeeded.

SQL> connect jeffhpc/jeffhpc
Connected.
SQL> create or replace procedure test1
2 is
3 i integer;
4 begin
5 i :=0;
6 for x in (select * from v$archived_log) loop
7 i :=i+1;
8 end loop;
9 dbms_output.put_line(i);
10 end;
11 /

Procedure created.
SQL> exec test1;
0

PL/SQL procedure successfully completed.

[santoshym]

Hi

I hope you have given select privilege to v_$archived_log to the user for whom you are creating the procedure if not login as sys and type the following command

grant select on v_$archived_log to username;

If you have allready done the above step and facing a problem why dont you put the code here so that we can have a look at it

Jeff is right works for me after the above step for the following code

set serveroutput on

create or replace procedure somproc as
noofrecs number(3);
begin
select count(*) into noofrecs from sys.v_$archived_log;
dbms_output.put_line(noofrecs);
end;

Regards

[Edited by santoshym on 05-31-2001 at 05:02 PM]