sys object in procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: sys object in procedure

  1. #1
    Join Date
    Nov 2000
    Posts
    440
    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?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    i have granted the view and it doesnt work

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2001
    Posts
    635
    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
  •  



Click Here to Expand Forum to Full Width