PL-SQL Interview Questions

Looking for PL SQL Interview Questions with Answers? Here we have compiled set of questions and answers for professionals who are looking to crack the interview.

Q.1 What do you understand by PL/SQL ?
PL/SQL is defined as a procedural language that has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.
Q.2 Differentiate between % ROWTYPE and TYPE RECORD.
Lets discuss some of the points of difference -

% ROWTYPE is used when a query returns an entire row of a table or view, whereas 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
Q.3 Explain the uses of cursor.
Cursor is primarily a named private area in SQL from which information can be accessed. Cursor are required to process each row individually for queries which then return multiple rows.
Q.4 What the uses of database trigger?
Database Trigger is a PL/SQL program unit associated with a particular database table. It is primarily 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
Q.5 What are the types of exceptions?
Exception refers to the Error handling part of PL/SQL block. They are of two types namely -
user_defined
predefined.
Q.6 What do you understand by Raise_application_error?
It refers to the procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.
Q.7 List the rules to be applied to NULLs while doing comparisons.
The rules to be applied to NULLs while doing comparisons are -
NULL is never TRUE or FALSE
NULL cannot be equal or unequal to other values
If a value in an expression is NULL, then the expression itself evaluates to NULL except for concatenation operator (||)
Q.8 How can you process of PL-SQL compiled?
The process of compiling includes syntax check, bind and p-code generation processes. The steps followed are -

1. Syntax checking checks the PL SQL codes for compilation errors.
2. When all errors are corrected, a storage address is assigned to the variables that hold data referred as Binding.
3. 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.
Q.9 What is the difference between Syntax and runtime errors?
The difference between Syntax and runtime errors are -
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.
Q.10 What do you understand by Implicit and Explicit Cursors?
Cursor is implicit by default such that the user cannot control or process the information in this cursor.

In case a query returns multiple rows of data, the program defines an explicit cursor. This permits the application to process each row sequentially as the cursor returns it.
Q.11 What do you understand by mutating table error?
Mutating Table Error occurs when a trigger tries to update a row that it is currently using. This error is fixed by using views or temporary tables, so database selects one and updates the other.
Q.12 When do you require a declare statement?
Declare statement is used by PL SQL anonymous blocks like with stand alone, non-stored procedures. Such that if it is used, it must come first in a stand alone file.
Q.13 List the importance of SQLCODE and SQLERRM.
SQLCODE gives the value of the number of error for the last encountered error where on the other hand SQLERRM returns the message for the last error.
Q.14 Does SQL*Plus also have a PL/SQL Engine?
SQL*Plus does not have a PL/SQL Engine embedded in it. Therefore all PL/SQL code is sent directly to database engine. Such that it is way more efficient as each statement is not individually stripped off.
Q.15 What are the packages available to PL SQL developers?
Some of the packages available to the developers are -
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.
Q.16 What are the basic parts of a trigger?
Three basic parts of a trigger are -
1. A triggering statement or event.
2. A restriction
3. An action
Q.17 List some character functions.
Some of the most used character functions are -
INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all character functions. Group functions give results based on groups of rows, as opposed to individual rows. They are MAX, MIN, AVG, COUNT and SUM.
Q.18 How can you reference column values BEFORE and AFTER you have inserted and deleted triggers?
We can reference column values BEFORE and AFTER you have inserted and deleted triggers by -
Using the keyword "new.column name", the triggers can reference column values by new collection.
Using the keyword "old.column name", they can reference column vaues by old collection.
Q.19 State the uses of SYSDATE and USER keywords?
SYSDATE indicates the current server system date. It is a pseudo column. On the other hand USER is also a pseudo column but refers to current user logged onto the session. Primarily they are used to monitor changes happening in the table.
Q.20 How will ROWID assist in running a query faster?
ROWID is the logical address of a row, such that it is not a physical column. ROWID composes of data block number, file number and row number in the data block. Therefore I/O time gets minimized retrieving the row, and results in a faster query.
Q.21 What does dirty read refers to?
Dirty read refers to inconsistent data reading during multiple transaction processing
Q.22 Why does the following statement in the declaration section fail : PRODUCT_IN_STOCK BOOLEAN := 'TRUE';
A Boolean variable cannot be assigned a character string value.
Q.23 What is the use of using RAISE keyword in exception blocks ?
To raise same exception again
Q.24 Why does the following declaration fail : MY_NUMBER POSITIVEN;
A variable declared as POSITIVEN should always be initialized to a non-NULL value.
Q.25 What will the result of executing REPLACE('BLACK AND BLUE','BL','J')
JACK AND JUE
Get Govt. Certified Take Test