PL/SQL Interview Questions

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. Basically PL/SQL is a block structure programming language whenever we are submitting PL/SQL Blocks then all SQL statements are executing separately by using sql engine and also all procedure statements are executed separately.

What is stored Procedure? What are the advantages of 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. Advantages: Modularity, extensibility, reusability, Maintainability and one time compilation.

What are the different functionalities of a Trigger ?

Trigger is also same as stored procedure & also it will automatically invoked whenever DML operation performed against table or view.
There are two types of triggers supported by PL/SQL

Statement Level Trigger: In statement level trigger, trigger body is executed only once for DML statement.Row Level Trigger: In row level trigger, trigger body is executed for each row DML statements. It is the reason, we are employing each row clause and internally stored DML transaction in trigger specification, these qualifiers :old, :new, are also called as records type variables.
These qualifiers are used in trigger specification & trigger body.
Synatx: :old.column_name
Synatx: :new column_name

When we are use this qualifiers in trigger specification then we are not allowed to use “:” in forms of the qualifiers names.

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.

What is the difference between execution of triggers and stored procedures?

A trigger is automatically executed without any action required by the user, while, a stored procedure is explicitly invoked by the user.

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.

What is Mutating Trigger?

Into a row level trigger based on a table trigger body cannot read data from same table and also we cannot perform DML operation on same table.
If we are trying this oracle server returns mutating error oracle-4091: table is mutating.
This error is called mutating error, and this trigger is called mutating trigger, and table is called mutating table.
Mutating errors are not occured in statement level trigger because through these statement level trigger when we are performing DML operations automatically data committed into the database, whereas in row level trigger when we are performing transaction data is not committed and also again we are reading this data from the same table then only mutating errors is occured.

What is Triggering Events (or) Trigger Predicate Clauses?

If we want to perform multiple operations in different tables then we must use triggering events within trigger body. These are inserting, updating, deleting clauses. These clauses are used in statement, row-level trigger. These triggers are also called as trigger predicate clauses.
Syntax:

If inserting then
stmts;
else if updating then
stmts;
else if deleting then
stmts;
end if;

What is Discard File?

This file extension is .dsc
Discard file we must specify within control file by using discard file clause.
Discard file also stores reflected record based on when clause condition within control file. This condition must be satisfied into table tablename clause.

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.

What is REF CURSOR (or) CURSOR VARIABLE (or) DYNAMIC CURSOR ?

Oracle 7.2 introduced ref cursor, This is an user defined type which is used to process multiple records and also this is a record by record process.

In static cursor database servers executes only one select statement at a time for a single active set area where in ref cursor database servers executes number of select statement dynamically for a single active set area that’s why those cursor are also called as dynamically cursor.

Generally we are not allowed to pass static cursor as parameters to use subprograms where as we can also pass ref cursor as parameter to the subprograms because basically refcursor is an user defined type in oracle we can also pass all user defined type as parameter to the subprograms.
Generally static cursor does not return multiple record into client application where as ref cursor are allowed to return multiple records into client application (Java, .Net, php, VB, C++).

This is an user defined type so we are creating it in 2 steps process i.e first we are creating type then only we are creating variable from that type that’s why this is also called as cursor variable.

In Weak ref cursor we must specify select statement by using open for clause this clause is used in executable section of the PL/SQL block.
Syntax:

Open ref cursor varname for SELECT * FROM tablename condition;

What is Difference Between trim, delete collection method?

TRIM removes one or more elements from the END of a collection, whereas DELETE will remove the element(s) you specify anywhere in the collection. TRIM frees the space for removed elements and DELETE does not ( unless you DELETE ALL elements that is ).

What is Overloading Procedures?

Overload is refers to same name can be used for different purpose, in oracle we can also implement overloading procedure through package. Overloading procedure having same name with different type or different number of parameters.

What is Global Variables?

In oracle we are declaring global variables in Package Specification only.

What is Forward Declaration?

In oracle declaring procedures within package body is called forward declaring generally before we are calling private procedures into public procedure first we must implements private into public procedure first we must implements private procedure within body otherwise use a forward declaration within package body.

What is Invalid_number, Value_Error?

In oracle when we try to convert “string type to number type” or” data string into data type” then oracle server returns two types of errors.
1. Invalid.number
2. Value_error (or) numeric_error

a) Invalid_number:
When PL/SQL block have a SQL statements and also those SQL statements try to convert string type to number type or data string into data type then oracle server returns an error: ora-1722-Invalid Number
For handling this error oracle provides number exception Invalid_number exceptionname.
Example:

b)value_error
Whenever PL/SQL block having procedural statements and also those statements find to convert string type to number type then oracle servers returns an error: ora-6502:numeric or value error: character to number conversion error
For handling this error oracle provided exception value_error exception name
Example:

Flashback query are handle by Database Administrator only flashback queries along allows content of the table to be retrieved with reference to specific point of time by using as of clause that is flashback queries retrieves clause that is flashback queries retrieves accidental data after committing the transaction also.
Flashback queries generally uses undo file that is flashback queries retrieve old data before committing the transaction oracle provide two method for flashback queries
Method1: using timestamp
Method2: using scn number

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.

Mention what PL/SQL package consists of?

A PL/SQL package consists of

PL/SQL table and record TYPE statements
Procedures and Functions
Cursors
Variables ( tables, scalars, records, etc.) and constants
Exception names and pragmas for relating an error number with an exceptionƒ

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.

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_APPLICATION_INFO
DBMS_TRACE
DBMS_SESSION and DBMS_MONITOR
trcsess and tkproof utilities

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.

What are the cursor attributes used in PL/SQL?

%ISOPEN: it checks whether the cursor is open or not.
%ROWCOUNT: returns the number of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT.
%FOUND: it checks whether cursor has fetched any row. If yes – TRUE.
%NOTFOUND: it checks whether cursor has fetched any row. If no – TRUE.

Explain the Commit statement.

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.