Top 50 PL SQL interview questions and answers 2018
1.What is PL/SQL?
Oracle PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
Go through this tutorial to learn more about PL/SQL.
2.What is the basic structure of PL/SQL?
PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
3.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 :
Audit data modifications.
Log events transparently.
Enforce complex business rules.
Maintain replica tables
Derive column values
Implement Complex security authorizations
Any of the constant, variable or parameter has a data type depending on which the storage constraints, format and range of values and operations are determined.
4.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.
Go through this tutorial to learn more about handling PL/SQL errors.
5.Mention what PL/SQL package consists of?
A PL/SQL package consists of
PL/SQL table and record TYPE statements
Procedures and Functions
Variables ( tables, scalars, records, etc.) and constants
Exception names and pragmas for relating an error number with an exception
Check the insightful PL/SQL tutorial to learn more about Pl/SQL Packages.
6.What are the benefits of PL/SQL packages?
It provides several benefits like
Enforced Information Hiding: It offers the liberty to choose whether to keep data private or public
Top-down design: You can design the interface to the code hidden in the package before you actually implemented the modules themselves
Object persistence: Objects declared in a package specification behaves like a global data for all PL/SQL objects in the application. You can modify the package in one module and then reference those changes to another module
Object oriented design: The package gives developers strong hold over how the modules and data structures inside the package can be used
Guaranteeing transaction integrity: It provides a level of transaction integrity
Performance improvement: The RDBMS automatically tracks the validity of all program objects stored in the database and enhance the performance of packages.
7.What are different methods to trace the PL/SQL code?
Tracing code is a crucial technique to measure the code performance during the runtime. Different methods for tracing includes
DBMS_SESSION and DBMS_MONITOR
trcsess and tkproof utilities
8.What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?
Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.
Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.
Package: A package is schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.
9.What is stored Procedure?
A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as schema object. It can be nested, invoked and parameterized.
Learn more about PL/SQL Collections & Records.
10.What is cursor and why it is required?
A cursor is a temporary work area created in a system memory when an SQL statement is executed.A cursor contains information on a select statement and the row of data accessed by it. This temporary work area stores the data retrieved from the database and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. Cursor are required to process rows individually for queries.
11.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.
Show some predefined exceptions.
12.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.
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.
13.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 (||)
14.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.
15.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.
16.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.
17.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.
18.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.
19.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.
20.How many triggers can be applied to a table?
A maximum of 12 triggers can be applied to one table.
21.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.
If a cursor is open, how can we find in a PL SQL Block?
the %ISOPEN cursor status variable can be used.
Show the two PL/SQL cursor exceptions.
22.What operators deal with NULL?
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.
23.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.
24.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.
25.Explain 3 basic parts of a trigger.
A triggering statement or event.
26.What are character functions?
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.
27.Explain TTITLE and BTITLE.
TTITLE and BTITLE commands that control report headers and footers.
Show the cursor attributes of PL/SQL.
%ISOPEN : Checks if the cursor is open or not
%ROWCOUNT : The number of rows that are updated, deleted or fetched.
%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched
%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.
28.What is an Intersect?
Intersect is the product of two tables and it lists only matching rows.
29.What are sequences?
Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.
30.How would you reference column values BEFORE and AFTER you have inserted and deleted triggers?
Using the keyword “new.column name”, the triggers can reference column values by new collection. By using the keyword “old.column name”, they can reference column vaues by old collection.
31.What are the uses of SYSDATE and USER keywords?
SYSDATE refers to the current server system date. It is a pseudo column. USER is also a pseudo column but refers to current user logged onto the session. They are used to monitor changes happening in the table.
32.How does ROWID help in running a query faster?
ROWID is the logical address of a row, it is not a physical column. It composes of data block number, file number and row number in the data block. Thus, I/O time gets minimized retrieving the row, and results in a faster query.
33.What are database links used for?
Database links are created in order to form communication between various databases, or different environments like test, development and production. The database links are read-only to access other information as well.
34.What does fetching a cursor do?
Fetching a cursor reads Result Set row by row.
35.What does closing a cursor do?
Closing a cursor clears the private SQL area as well as de-allocates memory
36.Explain the uses of Control File.
It is a binary file. It records the structure of the database. It includes locations of several log files, names and timestamps. They can be stored in different locations to help in retrieval of information if one file gets corrupted.
Consistency shows that data will not be reflected to other users until the data is commit, so that consistency is maintained.
38.Differ between Anonymous blocks and sub-programs.
Anonymous blocks are unnamed blocks that are not stored anywhere whilst sub-programs are compiled and stored in database. They are compiled at runtime.
39.Differ between DECODE and CASE.
DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.
select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from smp where smpno in (10,12,14,16);
This statement returns an error.
CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.
40.Explain autonomous transaction.
An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction.
There are several situations to use autonomous transactions like event logging and auditing.
41.Differentiate between SGA and PGA.
SGA stands for System Global Area whereas PGA stands for Program or Process Global Area. PGA is only allocated 10% RAM size, but SGA is given 40% RAM size.
42.What is the location of Pre_defined_functions.
They are stored in the standard package called “Functions, Procedures and Packages”
43.Explain polymorphism in PL SQL.
Polymorphism is a feature of OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package..Unambiguous logic must be avoided whilst overloading is being done.
44.What are the uses of MERGE?
MERGE is used to combine multiple DML statements into one.
Syntax : merge into tablename
when not matched the
when matched then
45.What do you understand by PL/SQL cursors?
Answer: PL/SQL requires a special capability to retrieve and process more than one row and that resource is known as Cursors. A cursor is a pointer to the context area, which is an area of memory containing SQL statements and information for processing the statements.
PL/SQL Cursor is basically a mechanism under which multiple rows of the data from the database are selected and then each row is individually processed inside a PL/SQL program.
46.Explain cursor types?
There are two types of cursors. They are explained as follows
1) Explicit Cursors: For queries that return more than one row, an explicit cursor is declared and named by a programmer. In order to use explicit cursor in PL/SQL, 4 steps are followed
Declare the cursor
Syntax: CURSOR is
Where is the name assigned to the cursor and SELECT statement is the query that returns rows to the cursor active set.
Open the cursor
Syntax: OPEN ;
Where, is the name of the previously defined cursor.
Fetch rows from the cursor
Syntax: FETCH INTO ;
Where refers to the name of the previously defined cursor from which rows are being fetched.
represents the list of variables that will receive the data being fetched.
Closing the cursor
Syntax: CLOSE ;
Where is the name of the cursor being closed.
2) Implicit cursors: When any SQL statement is executed, PL/SQL automatically creates a cursor without defining such cursors are known as implicit cursors.
For following statements, PL/SQL employs implicit cursors
SELECT ( queries that return exactly one row)
47.When do we use triggers?
Answer: The word ‘Trigger’ means to activate. In PL/SQL, the trigger is a stored procedure that defines an action taken by the database when database related event is performed. Triggers are mainly required for the following purposes
To maintain complex integrity constraints
Auditing table information by recording the changes
Signaling other program actions when changes are made to table
Enforcing complex business rules
Preventing invalid transactions
It is a procedure of the package DBMS_STANDARD that allow issuing a user_defined error messages from the database trigger or stored sub-program.
49.What is out parameter used for eventhough return statement can also be used in pl/sql?
Out parameters allows more than one value in the calling program. Out parameter is not recommended in functions. Procedures can be used instead of functions if multiple values are required. Thus, these procedures are used to execute Out parameters.
Spool command can print the output of sql statements in a file.
select smp_name, smp_id from smp where dept=’accounts’;