5 Replies Latest reply on Jul 28, 2014 10:44 PM by rp0428

    ORA-00942 running a report

    1138

      Hello,

       

      New to SQL Developer -- using version 4.02.15 Build 15.21.

       

      Every time I try to run a report I get the error message

       

      An error was encountered performing the requested operation:

      ORA-00942: table or view does not exist
      00942. 00000 -  "table or view does not exist"
      *Cause:   
      *Action:
      Vendor code 942

       

      Sometimes after getting the message the report runs, sometimes it does not.

      Initially I figured it was just a DBA view that we do not have access to (not unusual in this environment) but then I wrote a user defined report and ran into the same issue. The SQL for the user defined report runs fine in an editor, throws the error when it's in a report.

       

      Any idea what table or view SQL Developer wants that I do not have access to?

       

      Thanks

        • 1. Re: ORA-00942 running a report
          Gary Graham-Oracle

          In the report output tab's toolbar, there will be a SQL icon.  Click on that to see the executed SQL.  In fact, a new worksheet using the same connection as the report will open.  Run the SQL from there to see if the ORA-00942 occurs.

           

          As far as I know, there is no other SQL submitted.

           

          Regards,

          Gary

          SQL Developer Team

          • 2. Re: ORA-00942 running a report
            1138

            Right, and that's the SQL that I entered when I created the report, and it did run fine in a worksheet.

             

            Trying again tonight I think I see what happened. Tonight from a fresh instance of SQL Developer, straight to my report, it's fine.

             

            Then if I go to Tools > Monitor Sessions, I get the 942 error.

             

            From that point on if I run any report (including the one I wrote) I get a 942.

            • 3. Re: ORA-00942 running a report
              Gary Graham-Oracle

              It seems unlikely there is any relationship between ORA-00942 errors when running a report and trying to run Monitor Sessions.  The database connection user for a report just needs to be the owner of (or have read privileges on) the objects referenced in the report.  The database connection user for Monitor Sessions needs more privileges.  I generally use SYS on little development databases, but otherwise I choose a user with at least the SELECT ANY DICTIONARY system privilege or the role SELECT_CATALOG_ROLE.

              • 4. Re: ORA-00942 running a report
                Emilija

                Hello,

                 

                If you are getting ORA-00942 errors when querying the DBA_views, try granting SELECT to these views to the user directly, and not through a role.

                 

                Emilija

                • 5. Re: ORA-00942 running a report
                  rp0428
                  Trying again tonight I think I see what happened. Tonight from a fresh instance of SQL Developer, straight to my report, it's fine.

                   

                  Then if I go to Tools > Monitor Sessions, I get the 942 error.

                   

                  From that point on if I run any report (including the one I wrote) I get a 942.

                  Of course you will - you are still trying to 'monitor sessions'. Almost anything you do in that session will give that error when the 'session monitor' refreshes.

                   

                  Disable 'monitor sessions' and don't use it if you don't have the privileges.