PLSQL Tips and Tricks

After nearly five years of working in MSSQL, I had to switch to a project that used Oracle and at that time, I did not know even the ABCs of Oracle. Every PLSQL query that I had to write or analyze, I tried to relate with my prior knowledge of TSQL and it just did not work out.

So, in this post, I am going to list out the tips that would help anyone migrating from TSQL to PL-SQL.

1. Get ‘x’ number of rows

select * from TEST_TABLE WHERE ROWNUM < 10

2. Check if a given package, procedure is used somewhere else


3. Implement sleep/delay

DBMS_LOCK.SLEEP(t) , where t is the amount of time in second

 4. Grant access to multiple tables



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!! 🙂

Query Execution Timeout

I am currently working on this project, in which I have to run huge datasets on SQL Server using ADO.NET. And I have come to realize that there can be many performance issues we often neglect while testing for smaller sets.

One of them that I encountered was the issue related to query execution timeout. To understand this issue, I learnt what happens when we execute any query in SQL Server. When a query is submitted, SQL Server checks if there is a plan cached for that query or not. If yes, then that plan is used.
Continue reading