How to View Errors for VIEWs in Oracle SQL Developer

thatjeffsmith SQL Developer 21 Comments

Tell Others About This Story:

I know, who has errors in their code? I don’t have errors because I don’t write code. But for the rest of you, it’s possible you have inherited someone else’s problem. And that problem might be an invalid VIEW.

You can tell your view has a problem because of the big, sad, red X

This view looks a little buggy to me.

Oracle stores these errors in a data dictionary view, ALL_ERRORS. You just need to query them. Or if you’re in SQL*Plus and you just issued the CREATE OR REPLACE FORCE…you can also execute ‘SHOW ERRORS’

But you’re not in SQL*Plus and you didn’t create the view, and you’re too lazy to query it manually in a worksheet. Wouldn’t it be nice if you had a report right there to view your errors?

Wait a Second, I Can Build My Own Custom Extension!

That’s right, if there’s some information you want to see that’s not displayed in the object viewer, you can create a user defined extension using XML and SQL.

A customer asked me how to view errors for their views, and I didn’t have a great answer. So I decided to write up a quick extension. Here’s the source:

<items>
<item type="editor" node="ViewNode" vertical="true">
<title><![CDATA[ERRORS]]></title>
<query>
<sql><![CDATA[SELECT
ATTRIBUTE, LINE
|| ':'
||POSITION "LINE:POSITION", TEXT
FROM
All_Errors
WHERE
type = 'VIEW'
AND owner = :OBJECT_OWNER
AND name = :OBJECT_NAME
ORDER BY
SEQUENCE ASC
]]></sql>
</query>
</item>
</items>

To activate the extension, add a new entry to the preferences. You’ll want to add an ‘EDITOR’ and point to the location of the XML file that has the above code.

Adding your new VIEW, ERRORS panel. Click ‘OK’ the restart SQL Developer.

After you restart SQL Developer, we can now easily see the errors for all of our views. Hopefully your errors are about as easy to diagnose and fix as are mine.

My new errors panel.

I want to see the errors in the code on one screen, so I ‘split the document.’ That gives me 2 independent looks at the view in one area. Very handy, and one of my favorite tricks!

As always, you can trust me, but don’t trust my code. Make sure it suits your needs, and feel free to improve upon it.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 21

    1. I use the extension but get an error saying, ATRIBUTE invalid identifier i hope you can give me some advice on what’s happening thanks in advanced

      1. thatjeffsmith Post
        Author
  1. I added the extension accordingly. But seems it shows error contents to all other view (even they are with no error icon). Wonder if it’s related that I removed the part :object_name/owner (since I don’t know what to fill). Thanks.

    1. thatjeffsmith Post
      Author

      Why did you remove the :OBJECT_OWNER and :OBJECT_NAME bits? Those are supplied by SQLDev – it knows what the object owner and object name is. Put it back in, it will work as advertised.

        1. thatjeffsmith Post
          Author
  2. Jeff, I’m on 4.1.0.19.

    When I ask SQL Developer to check for updates, it points to 4.0.1.

    I’m downloading 4.1.1 now. Thanks for pointing it out. I assume it will work as you describe.

    Skip

  3. Hi Jeff,

    I added the view errors extension in an earlier release of SQL Developer a few years ago. Now the ERRORS tab is on the far left, before the Columns tab.

    Your screenshot shows it on the far right.

    I don’t see anything in the XML for positioning it.

    How would I get it to be on the far right, next to the SQL tab?

    1. thatjeffsmith Post
      Author
  4. Hi Jeff. This is a great extension, but I’m not seeing the errors pane when I loaded it and restarted sql developer. I’m running 4.0.2.15. I put the xml code into a local directory, went into database preferences to add the editor with the pointer to the xml file and restarted the app. When I go into the materialized view editor, I don’t see any tabs other than the default ones. Any suggestions?

    1. thatjeffsmith Post
      Author
  5. Please can I get some assistance, I am getting a timeout in 1 of my xml jobs which executes via sql sentry, if I execute as a batch on the nodepool server the job works fine.

  6. Wow, it’s awesome! Is there any similar solution for the triggers? Because on the trigger view it also shows a big red X, and a similar tool whould be useful instead of using ‘show errors trigger my_trigger’.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  7. Thanks for another informative post Jeff.
    Out of choice, I do not use SQL Developer but your posts on custom extensions for SQL Developer might just turn me into a user.

    1. thatjeffsmith Post
      Author

      I respect your choice and look forward to the day when you change your mind 🙂

      Thanks for hanging out here and sharing your opinion. Especially since you don’t even use the tool yet!

Leave a Reply

Your email address will not be published. Required fields are marked *