Top PL/SQL Interview Questions with Answers

 

 

Click on the question to see the answer

Basic PL/SQL Interview Questions

1. Difference between Syntax and Runtime errors in PL/SQL??
2. Explain what is COMMIT, ROLLBACK and SAVEPOINT??
3. How many types of Triggers are there in PL/SQL and what are they?
4. How to generate Debugging Information in PL/SQL?
5. In what order should a open/fetch/loop set of commands in a PL/SQL block be implemented?
6. Name any 5 PL/SQL exception types
7. What are the two different types of Exceptions in PL/SQL?
8. What is IN parameter and OUT parameter in PLSQL?
9. What is Public and Private Procedures in PL/SQL?
10. What is Referential Integrity?
11. What is SQLCODE and SQLERRM in PL/SQL?
12. What is the difference between CASE and DECODE?
13. What is the difference between VARCHAR and VARCHAR2 in PL/SQL?
14. What is %TYPE and %ROWTYPE in PL/SQL??
15. What is the disadvantage of using %ROWTYPE in PL/SQL?
16. What are the advantages of using %TYPE and $ROWTYPE in PL/SQL?
17. List all different types of Data Types in PL/SQL?
18. What is the difference between Views and Materialized Views in Oracle?
19. What is difference between a PROCEDURE & a FUNCTION ?
20. What are the Advantages of Using Database Triggers??
21. What are two virtual tables available during database trigger execution in PL/SQL?
22. Is it mandatory to close the Cursor in a PL/SQL block ?
23. What is difference between % ROWTYPE and TYPE RECORD?
24. How does an Explicit Cursor work in PL/SQL?
25. Can we use RETURN STATEMENT and RETURN key word in Procedure, if we use what will happen?
26. How to debug your PL/SQL procedure?

Click on the question to see the answer

Advanced PL/SQL Interview Questions

1. 1. Does SQL*PLUS has embedded PLSQL engine?
   2. How does an Implicit Cursor work in PL/SQL?
   3. How does ROWID help in running a query faster?
   4. How to return more than one value from a Function in PL/SQL
   5. How to use COMMIT in PL/SQL Trigger?
   6. In a PL/SQL block of code how do you find if a Cursor is STILL open?
   7. What are all the special operators does Oracle provide for dealing with NULLs?
   8. What are Explicit Cursor attributes and what are they?
   9. What are Implicit Cursor attributes and what are they?
   10. What are the rules to be applied to NULL while doing comparison in an expression?
   11. What is a trigger and what is the difference between Row Level and Statement Level trigger??
   12. What is Autonomous transaction in PL/SQL?
   13. What is Global Temporary Tables and What is it Purpose?
   14. What is Mutating error in PL/SQL Triggers?
   15. What is RAISE_APPLICATION_ERROR is PL/SQL?
   16. What is SYS_GUID() function? and what is the purpose of SYS_GUID function?
   17. What is the maximum number of triggers, can apply to a single table?
   18. What steps are included in the compilation process of a PL/SQL block?
   19. How to avoid usage of Cursors in PL/SQL?
   20. How to return more than one value from a Function in PL/SQL
   21. How is the Cursor declared in Procedure is different from Cursor defined in Package Specification?
   22. What is RAISE_APPLICATION_ERROR in PL/SQL Exception handling??
   23. What is PRAGMA EXECPTION_INIT?
   24. What happens if a procedure which updates a column of table X is called in a database trigger of the same table?
   25. What is the purpose of WHERE CURRENT OF statement in PL/SQL?
   26. Why cant we use SQLERRM and SQLCODE in INSERT Statement?
   27. Can we write When OTHERS Exception at the beginning of the exception block and some other exceptions after that ?
   28. What is Cursor Variables or REF CURSOR ?
   29. What are the Disadvantages of Cursor Variables?
   30. What are the advantages of using %TYPE and $ROWTYPE in PL/SQL?
   31. How to find Package/Procedure name and Line Number where the program got errored out???
   32. What is BULK COLLECT in PL/SQL?

Note: New Questions will be added to this list on regular basis