-
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?
-
You must have permission on V_$ARCHIVED_LOG granted explicitly. V_$ARCHIVED_LOG is the underlying object for the v$archived_log view.
Jeff Hunter
-
i have granted the view and it doesnt work
-
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.
Jeff Hunter
-
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]
Santosh
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|