In my previous articles I have explained the SQL interview questions ,BI Interview questions which will give the best idea about the question that may ask in interview. In this article i will try to explain most important PL/SQL Interview Questions that may ask in interview.PL/SQL is the Procedural Language Structured Query Language which will use to add the business logic. In this article I will try to give the brief description about different PL/SQL interview questions.
1.What is cursor ?
Answer :
Cursor is a buffer area which is used to process multiple records and also record by record tabs.
There are 2 types of cursors :
Implicit cursor : Implicit cursor is a buffer area which has been defined and controlled by oracle internally. Implicit cursor will process single record at a time.
example :
declare
v_Ename varchar2(100);
begin
select ename into V_Ename from Employee where empno=101;
dbms_output.put_line(V_Ename );
end;
The above cursor is implicit cursor where all the operations are defined by oracle engine internally like declaring the cursor,fetching values from the cursor and close cursor.
Explicit Cursor : Explicit cursor is a cursor which is defined by user to process set of records.For multiple records user needs to use explicit cursor.Explicit cursor operations are done by the user.
There are following 4 operations needs to be done by user :
1.Declare cursor
2.Open cursor
3.Fetch all records from the cursor
4.Close cursor.
2.What are different cursor attributes?
Answer :
There are following cursor attributes :
1.%Found
2.%Not Found
3.%Isopen
4.%Rowcount
3.What is ref cursor?Why it is used?
Answer :
As the name suggested ref cursor is a variable which will point to the address or reference of the cursor.Ref cursor is variable not cursor but that variable points to cursor.
There are 2 type of ref cursors :
1.Strong Ref cursor
2.Weak Ref cursor
4.What is %ROWTYPE ? Explain this with example.
Answer :
%ROWTYPE is cursor attribute which is used to define the record of the field.Each field assumes it own datatype and %ROWTYPE is used to define the specific record type.
example :
CREATE OR REPLACE PROCEDURE P_Employee_Information
IS
CURSOR Emp_Cur IS SELECT Employee_name, Employee_Number FROM emp;
variable1 Emp_Cur %ROWTYPE; ---This is cursor variable name
BEGIN
OPEN Emp_Cur ;
LOOP
FETCH Emp_Cur INTO variable1;
EXIT WHEN Emp_Cur %Notfound; ---When cursor
DBMS_OUTPUT.PUT_LINE( variable1.Employee_name || ' works in department '
|| myvar.Employee_Number);
END LOOP;
CLOSE Emp_Cur ;
END;
5.How to write Cursor with for loop?
Answer:
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.Means while using the for loop user dont need to Open the cursor and fetch the values from cursor or close cursor explicitly.
In For loop all cursor operations done implicitly..
Real Example:
FOR Sample_cursor IN C1 LOOP
Total_Salary=Total_Salary + Appraisals;
END LOOP;
6.What is Database Trigger?What is real use of trigger?
Answer :
PL SQL procedure which is used to trigger specific event on specific condition is known as database triggers. Triggers are database objects with specific conditions.
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.
7.What is Raise_Application_Error?
Answer :
When user wants to insert Error message then user needs to use the Raise_Application_Error procedure. Raise_Application_Error is the system defined procedure of package named DBMS_STANDARD.
Syntax :
Raise_Application_Error(Error_Code,Error_Message);
Example :
Raise_Application_Error (-20343, ‘The balance is too low.’);
8.What is commit?RollBack?Savepoint?
Answer :
Commit :
When user commits the data after transaction that changes are permanent changes.
1.Other users can see the data changes made by the transaction.
2.The locks acquired by the transaction are released.
3.The work done by the transaction becomes permanent.
Rollback :
When transaction become wrong user can rollback the data.
1.The work done in a transition is undone as if it was never issued.
2.All locks acquired by transaction are released.
Savepoint :
It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.
9.What is mutating error?(90% asked PL SQL Interview Questions)
Answer :
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.
10.What is mean by Unique Indexes?(90% asked PL SQL Interview Questions)
Answer:
1.To create unique index you must have CREATE ANY INDEX privilege.Here the concept is bit different.User needs to check the values of the table to create unique index.If table contains uniquely identified values in specified column then you should use unique index.
2.Especially while creating the table if we specify the primary key then unique index is automatically created on that column.
3.But for Unique key constaint columns you separately need to do indexing.Kindly make sure that Unique key indexes created on the columns which has unique values only.
4.The unique indexes are also called as clustered indexes when primary key is defined on the column.
Example:
Create Unique index Index_name on Table_name(Unique column name);
Example:
CREATE UNIQUE INDEX UI1_EMP on EMP(EMP_ID);
11.How many triggers can be applied on one table?
Answer :
There are maximum 12 triggers can be applied on one table.
12.What is Bit-map index?Explain with Example.(80 % Asked in PL SQL Interview Questions)
Answer:
1.If Table contains the distinct values which are not more than 20 distinct values then user should go for Bit map indexes.
2.User should avoid the indexing on each and every row and do the indexing only on distinct records of the table column.You should able to check drastic change in query cost after changing the normal index to Bit map index.
3.The bit map indexes are very much useful in dataware housing where there are low level of concurrent transactions.Bit map index stores row_id as associated key value with bitmap and did the indexing only distinct values.
4.Means If in 1 million records only 20 distinct values are there so Bitmap index only stores 20 values as bitmap and fetches the records from that 20 values only.
Syntax:
Create bitmap index Index_name on Table_name(Columns which have distinct values);
Example:
CREATE BITMAP index BM_DEPT_NAME on DEPT(Department_name);
12. What are different cursor attributes?
Answer :
%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.
13.What is sequences in PL SQL?
Answer :
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.
14.What is database link used for?
Answer :
Database links are used to communicate between 2 databases.Database links are created in order to form communication between various databases, or different environments like test, development and production.When user wants to insert or update data from one environment to another environment then database links are used.
15.What is difference between Anonymous block and subprogram?
Answer :
Anonymous block :
Anonymous blocks are programs or unnamed block which is used to check some functionality and which are not stored in database.
Subprograms :
Subprograms are stored blocks which are stored in to database. Subprograms are compiled at runtime.
16.What are types of exceptions in PL/SQL?
Answer :
There are 2 types of exceptions in PL SQL :
1.Predefined Exceptions :
These exceptions are system defined exceptions.
2.User defined Exceptions:
User defined exceptions are exceptions which are defined by the user in the program.
17.Give us example of Predefined Exceptions?
Answer :
DUP_VAL_ON_INDEX
ZERO_DIVIDE
NO_DATA_FOUND
TOO_MANY_ROWS
CURSOR_ALREADY_OPEN
INVALID_NUMBER
INVALID_CURSOR
PROGRAM_ERROR
TIMEOUT _ON_RESOURCE
STORAGE_ERROR
LOGON_DENIED
VALUE_ERROR
18.What operators deals with null?
Answer :
NVL converts NULL to another specified value.
var:=NVL(var2,’Hi’);
IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.
19.What is cost based optimizer?
Answer:
Cost Based Optimizer (CBO) uses the artificial intelligence to execute the Query. The Optimizer itself decides the execution plan based on the cost of query. The cost based method means the database must decide which query execution plan to choose. It decides the query execution plan by deciding the statistical information based on the database objects.(tables, indexes and clusters).The Oracle cost based optimizer is designed to determine the most effective way to carry out the SQL statement.
“Cost based optimizer considers the statistical information of the table for query execution”
20.What is difference between Decode and Case?
Answer :
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(Total_salary=50000,’high’,40000,’medium’) as “Decode Test” from Employee where Employee_number in (1,2,3,4,5,6);
This statement will return an error.
CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.
These are some important PL SQL interview questions,hope you like this article.
In my previous article I have given details about application support engineer day to day…
In my previous articles I have given the roles and responsibilities of L1,L2 and L3…
In my previous articles i have given the hierarchy of production support in real company…
In this article i would like to provide information about production support organization structure or…
In my previous article I have given roles for L1 and L2 support engineer with…
I have started this new series of how to become application support engineer. This article…