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
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.
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.
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.