7 Replies Latest reply: May 12, 2008 4:33 PM by 608334 RSS

    ORA-00942 table or view does not exist

    MarGur
      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
          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
            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
              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
                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
                  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
                    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
                      you should have direct select on table .. not through role.

                      you would be abel to compile.

                      --Girish