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.