PL SQL Interview Questions
Do you get scared by hearing the word “interview”? If you do, then it’s because of lack of preparation and confidence that you have. But now you can boost your confidence level to a certain extent by reading our PL SQL interview questions and answers that are prepared by the greatest minds who have been working in this field for years. Now it’s possible for you to sit in the interview rooms and answer all the questions asked with confidence and crack it by winning the trust of the interviewers.
About PL SQL
PL/SQL is the procedural extension created by Oracle Corporation for SQL, and a relational database of Oracle. PL/SQL is present in the database that Oracle has, and in the memory database of TimesTen, as well as in IBM DB2. It usually extends functionality based on SQL with successive releases. PL/SQL stands for Procedural Language extensions to SQL. Most of the companies use this platform for their efficiency and accuracy in writing and executing codes in the procedural format across all the industries. In layman terms, it means an extension of SQL utilized in the databases of Oracle
Best PL SQL Interview Questions of 2020
1. What is PL SQL ?
PL SQL is a procedural language which has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.
2. Differentiate between % ROWTYPE and TYPE RECORD.
% ROWTYPE is used when a query returns an entire row of a table or view.
TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.
Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)
e_rec smp %ROWTYPE
Cursor c1 is select smpno,dept from smp;
e_rec c1 %ROWTYPE
3. Explain uses of cursor.
Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows.
4. Show code of a cursor for loop.
Cursor declares %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.
Eg. FOR smp_rec IN C1 LOOP
5. Explain the uses of database trigger.
A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :
1) Audit data modifications.
2) Log events transparently.
3) Enforce complex business rules.
4) Maintain replica tables
5) Derive column values
6) Implement Complex security authorizations
6. What are the two types of exceptions.
Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.
7. Show some predefined exceptions.
8. Explain Raise_application_error.
It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.
9. Show how functions and procedures are called in a PL SQL block.
Function is called as a part of an expression.
Procedure is called as a statement in PL/SQL.
10. Explain two virtual tables available at the time of database trigger execution.
Table columns are referred as THEN.column_name and NOW.column_name.
For INSERT related triggers, NOW.column_name values are available only.
For DELETE related triggers, THEN.column_name values are available only.
For UPDATE related triggers, both Table columns are available.
11. What are the rules to be applied to NULLs whilst doing comparisons?
1) NULL is never TRUE or FALSE
2) NULL cannot be equal or unequal to other values
3) If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)
12. How is a process of PL SQL compiled?
Compilation process includes syntax check, bind and p-code generation processes.
Syntax checking checks the PL SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.
13. Differentiate between Syntax and runtime errors.
A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.
A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.
14. Explain Commit, Rollback and Savepoint.
For a COMMIT statement, the following is true:
- Other users can see the data changes made by the transaction.
- The locks acquired by the transaction are released.
- The work done by the transaction becomes permanent.
A ROLLBACK statement gets issued when the transaction ends, and the following is true.
- The work done in a transition is undone as if it was never issued.
- All locks acquired by transaction are released.
It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.
15. Define Implicit and Explicit Cursors.
A cursor is implicit by default. The user cannot control or process the information in this cursor.
If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.
16. Explain mutating table error.
It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.
17. When is a declare statement required?
DECLARE statement is used by PL SQL anonymous blocks such as with stand alone, non-stored procedures. If it is used, it must come first in a stand alone file.
18. How many triggers can be applied to a table?
A maximum of 12 triggers can be applied to one table.
19. What is the importance of SQLCODE and SQLERRM?
SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.
20. If a cursor is open, how can we find in a PL SQL Block?
the %ISOPEN cursor status variable can be used.
21. Show the two PL/SQL cursor exceptions.
22. What is the difference between the implicit and explicit cursors?
Implicit cursor is implicitly declared by Oracle. This is a cursor to all the DDL and DML commands that return only one row.
Explicit cursor is created for queries returning multiple rows.
23. What will you get by the cursor attribute SQL%ROWCOUNT?
The cursor attribute SQL%ROWCOUNT will return the number of rows that are processed by a SQL statement.
24. What will you get by the cursor attribute SQL%FOUND?
It returns the Boolean value TRUE if at least one row was processed.
25. What will you get by the cursor attribute SQL%NOTFOUND?
It returns the Boolean value TRUE if no rows were processed.
26. What do you understand by PL/SQL packages?
A PL/SQL package can be specified as a file that groups functions, cursors, stored procedures, and variables in one place.
27. What are the two different parts of the PL/SQL packages?
NVL converts NULL to another specified value.
IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.
28. Does SQL*Plus also have a PL/SQL Engine?
No, SQL*Plus does not have a PL/SQL Engine embedded in it. Thus, all PL/SQL code is sent directly to database engine. It is much more efficient as each statement is not individually stripped off.
29. What packages are available to PL SQL developers?
DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.
30. Explain 3 basic parts of a trigger.
- A triggering statement or event.
- A restriction
- An action
Career scopes and salary scale
When it comes to SQL, most of the tools and applications that you run in your operations division are based on SQL. If you want to pursue a career in the field of PL/SQL, then this might be the best career option available for you. As most of the companies nowadays use this platform for their benefits, they are in need of developers or software architects who are well versed with PL/SQL. Therefore, getting into a job in this field will help you in earning lakhs per month once you gain experience. For a fresher who is willing to start his career in this field can get up to 25,000 to 35,000 dollars per annum, while for an experienced employee, they can earn up to 95,000 to 110,000 dollars per annum easily.
Pursuing a career in this field is the best option for the freshers or currently graduated candidates. With the help of our PL SQL interview questions, it has become easier for the candidates to crack the interviews easily and sit in a dignified position in various companies.