PL/SQL Debugging Basics

“ If debugging is the process of removing software bugs, then programming must be the process of putting them in. ”

As put by Edsger Dijkstra, debugging is more important than mere programming to ensure the given code block works as expected. Being a developer, I tend to debug as often as I code. But similar is not the case when I am writing scripts. And I am not sure why. So, for this post, I am going to outline the steps involved and issues encountered while debugging PL/SQL scripts using SQL Developer tool.

First of all. the application developer must be granted the following privileges by a security subprogram to debug a PL/SQL subprogram.


– DEBUG CONNECT SESSION Continue reading

Invalid objects in Oracle

We often find that our databases are cluttered with objects that are no longer in valid state. Once operations related to upgrades, patches or DDL changes are made to the database, there might be cases where these changes may invalidate schema objects. It is important that we check our back-end timely for these invalid objects and either clean them or try to re-validate (recompile) these objects.

So, what are invalid objects?

Some types of schema objects (such as views, packages etc) references other objects. A view, for example, contains a query that references tables or other views. The references between these referenced objects and dependent objects are established at compile time. If, somehow, the compiler is unable resolve these references, then the dependent objects are marked as invalid objects.

How can we identify them?

We can identify the invalid objects by using the following query.


And why should we care?

If your application has a large of invalid objects, it adds latency to the execution of the application. Also, these invalid objects may cause exceptions in other concurrent sessions as well.

What can we do?

If we don’t use these objects, we can simply drop them. But if we need them, we can try to re-validate these invalid objects using one of the methods described here.

Happy Learning!! 🙂