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