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

SELECT * FROM USER_SOURCE WHERE TYPE = ‘FUNCTION’ AND NAME=’TEST_FUNCTION1′;
SELECT * FROM ALL_SOURCE WHERE TYPE = ‘PROCEDURE’ AND NAME=’TEST_PROCEDURE1′;

3. Implement sleep/delay

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

 4. Grant access to multiple tables

PLSQL Tip 5

Advertisements

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 ANY PROCEDURE

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

Image

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