This discussion is archived
7 Replies Latest reply: May 12, 2008 2:33 PM by 608334 RSS

ORA-00942 table or view does not exist

MarGur Newbie
Currently Being Moderated
Oracle 10.2.0.3 on RHEL 4 64bit

Hi gurus,

Quick question: I imported a user from another DB and have got two package bodies, that are in invalid mode. I want to recompile them, but it throws an error that 'table or view doesn't exist'. I'm going through the body of that .. package body :-) and see that it tries to query a table, that belongs to another user.. I'm looking for this table in another schema and see that it exists. So I'm creating a synonym for that table, trying to recompile - the same story (table or view doesn't exist). I'm creating a public synonym - same here. I'm connecting as the user with the invalid packages and trying to do 'select * from that_table' and everything works.. But if i only want to recompile the package body - here comes 00942..

Please, advise what should I do to get this package body recompiled?

Thanks!!
  • 1. Re: ORA-00942 table or view does not exist
    153119 Pro
    Currently Being Moderated
    Perform a small test:

    issue
    set role none
    try to select from an affected table,
    If you hit 942 again, you have your answer.
    You have privilege through a role.
    You either need direct privilege
    or
    set up the package using authid current_user

    The disadvantage of the first solution is you end up with a myriad of grants.
    This is why ideally only the table owner also owns the packages, and non-owners call the owner's packages.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: ORA-00942 table or view does not exist
    427828 Journeyer
    Currently Being Moderated
    Did you grant SELECT on that table (or synonym) for user that compiles package's body?
    GRANT SELECT ON table TO user;

    Then try to SELECT from first user that table, if successed, then you can compile the package's body
  • 3. Re: ORA-00942 table or view does not exist
    MarGur Newbie
    Currently Being Moderated
    hi, sybrandb!!

    Thanks for quick answer. yup, for one of them i did had privilege through a role. i granted 'select on' and it recompiled.. but for the second one it still barks..

    sys@test> conn fincvt/password@test
    Connected.
    fincvt@test> set role none;

    Role set.

    fincvt@test> select * from SHRTGPA;

    no rows selected

    fincvt@test> select object_name, object_type, created from user_objects where object_name='SHRTGPA';

    OBJECT_NAME OBJECT_TYPE CREATED
    ------------------------------ ------------------- ---------
    SHRTGPA SYNONYM 12-MAY-08


    I did 'select on' on that table as well, but it still is invalid..
  • 4. Re: ORA-00942 table or view does not exist
    MarGur Newbie
    Currently Being Moderated
    Hi, thanks for reply,

    yup i did 'select on' for both of them and one of them recompiled. the second one is still invalid..
  • 5. Re: ORA-00942 table or view does not exist
    MarGur Newbie
    Currently Being Moderated
    strange, now it throws the following, despite the fact that i'm connecting as sysdba

    sys@test> grant select on SHRTGPA to fincvt;

    Grant succeeded.

    sys@test> alter package FINCVT.AHCVGPA compile body;

    Warning: Package Body altered with compilation errors.

    sys@test> show errors
    Errors for PACKAGE BODY FINCVT.AHCVGPA:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    179/4 PL/SQL: SQL Statement ignored
    179/16 PL/SQL: ORA-01031: insufficient privileges
    199/3 PL/SQL: SQL Statement ignored
    199/10 PL/SQL: ORA-01031: insufficient privileges
    232/4 PL/SQL: SQL Statement ignored
    232/16 PL/SQL: ORA-01031: insufficient privileges
    247/4 PL/SQL: SQL Statement ignored
    247/11 PL/SQL: ORA-01031: insufficient privileges
    264/3 PL/SQL: SQL Statement ignored
    279/17 PL/SQL: ORA-00942: table or view does not exist
    284/3 PL/SQL: SQL Statement ignored
    284/15 PL/SQL: ORA-01031: insufficient privileges
    301/3 PL/SQL: SQL Statement ignored
    301/15 PL/SQL: ORA-01031: insufficient privileges
    329/8 PL/SQL: SQL Statement ignored
    362/22 PL/SQL: ORA-00942: table or view does not exist
    470/8 PL/SQL: SQL Statement ignored
    493/23 PL/SQL: ORA-00942: table or view does not exist
    537/1 PL/SQL: SQL Statement ignored
    537/8 PL/SQL: ORA-01031: insufficient privileges
    sys@test>
  • 6. Re: ORA-00942 table or view does not exist
    247514 Expert
    Currently Being Moderated
    Like mentioned, user need to have privilege granted explicitly not through roles.

    grant select on the table directly to the user that own the package.
  • 7. Re: ORA-00942 table or view does not exist
    608334 Newbie
    Currently Being Moderated
    you should have direct select on table .. not through role.

    you would be abel to compile.

    --Girish