Oracle PL/SQL Interview Questions and Answers

What is the advantage of using packages for storing PL/SQL objects?

Packages offer several advantages when you call a packaged subprogram for the first time, the whole package is loaded into memory. So, later calls to related subprograms in the package require no disk I/O. 
Packaged public variables and cursors persist for the duration of a session. So, they can be shared by all subprograms that execute in the environment. 
You can specify public (visible and accessible) or private (hidden and inaccessible) in Package.

Define exception and How will you raise an exception in PL/SQL procedure?

In PL/SQL, a error condition is called an exception. Exceptions can be system defined or user defined.

Examples of system defined exceptions include division by zero and out of memory. Some common system exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR.
You can define exceptions of your own in the declarative part of any PL/SQL block, subprogram, or package. Unlike system defined exceptions, user-defined exceptions must be declared and must be raised explicitly by RAISE statements. 

The following example illustrates the scope rules: 

DECLARE
due EXCEPTION; 
BEGIN
RAISE due; 
EXCEPTION
WHEN due THEN 
Null;
END; 
What is the difference between Procedure and Function?

A function is a subprogram written to perform certain computations ,
Functions must return a value (using the RETURN keyword), but for stored procedures this is not compulsory. Stored procedures can use RETURN keyword but without any value being passed. 
Functions could be used in SELECT statements, provided they don't do any data manipulation. However, procedures cannot be included in SELECT statements. 

What is PL/SQL ?
SQL is a declarative language that allows database programmers to write a SQL declaration and hand it to the database for execution. As such, SQL cannot be used to execute procedural code with conditional, iterative and sequential statements. To overcome this limitation, PL/SQL was created. PL/SQL is Oracle's Procedural Language extension to SQL.using plsql we perform Conditional Control ,Iterative Sequential Control Statements.it is commonly used to write data-centric programs to manipulate data in an Oracle database.

What is the difference between SQL and PL/SQL?
Both SQL and PL/SQL are languages used to access data within Oracle databases. SQL is a limited language that allows you to directly interact with the database. You can write queries (SELECT), manipulate objects (DDL) and data (DML) with SQL. PL/SQL is a programming language that includes all the features of most other programming languages. its easily integrate with SQL. SQL is executed one statement at a time. PL/SQL is executed as a block of code. SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).
What is the Difference between PL/SQL Table & Nested Table?
PL/SQL Table: Index by Tables are not Stored in Database. Nested Table: Nested Tables are Stored in Database as Database Columns.
What is the Sequence of Firing Database Triggers?

a) Before Statement Level Trigger
b) Before Row Level Trigger
c) After Row Level Trigger
d) After Statement Level Trigger

What is Instead Of Trigger?
This trigger is used to perform DML operation directly on the underlying tables, because a view cannot be modified by normal DML Statements if it contains joins or Group Functions. These triggers are Only Row Level Triggers. The CHECK option for views is not enforced when DML to the view are performed by Instead of Trigger.
Give some important Oracle supplied packages?

DBMS_SQL: It is used to write Procedures & Anonymous blocks that use Dynamic SQL.

DBMS_JOB: Using it, we can submit PL/SQL programs for execution, execute PL/SQL programs on a schedule, identify when programs should run, remove programs from the schedule & suspend programs from running.

DBMS_OUTPUT: This package outputs values & messages from any PL/SQL block.

UTL_FILE: With this package, you can read from & write to Operating system files

UTL_HTTP: This package allows to make HTTP Requests directly from the database.

Give some most often used system defined exceptions?

a) NO_DATA_FOUND (Select Statement returns no rows)
b) TOO_MANY_ROWS (Single row Select statement returns more than 1 row)
c) INVALID_CURSOR (Illegal cursor operations occurred)
d) CURSOR_ALREADY_OPEN (If cursor is opened & we are trying to reopen it)
e) INVALID_NUMBER (Conversion of Character to number fails) f) ZERO_DIVIDE
g) DUP_VAL_ON_INDEX (Attempted to insert a duplicate value)

What is a mutating and constraining table?

“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of being changed, it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state. Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires.

Can one call DDL statements from PL/SQL?
One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the “EXECUTE IMMEDATE” statement.

Latest Updates

General Knowledge of India

In 1897, the Birsa Movement supporters raised which colour flag as a symbol of Birsa Raj?

Ratio and Proportion

The cost of 10 books is Rs. 80/-. Find the value of 15 books?

The cost of 48 bags of paddy is Rs. 16,800/-. What is the cost of 36 bags of paddy?

Monthly expenditure of a family of 4 members is Rs 2800/-. What is the expenditure if there are 3 members in the family?

Annual incomes of the families of Sita and Latha are in the ratio 4:3 and their annual expenditure are in the ratio 3:2. What is their annual income if they save Rs. 3000/- in a year?

A certain sum of money is divided between A and B in the ratio of 5/4 : 5/3 and A got Rs. 360/-. Find the total sum of money and B’s share?

In an office, the ratio of the salaries of an officer and a clerk is 15:7. If the salary of a clerk is Rs. 720/- less than the salary of the officer, find the salary of each?

In an election to a municipal council, the votes secured by two candidates are in the ratio 13:19. The elected candidates gets 312 votes more than the second candidate. How many votes are secured by the second candidate?

A certain sum of money is divided between Murthy and Prasad in the ratio 22/7 : 8/3. If Murthy gets Rs. 120/- more than Prasad, find the share of each?

In a cricket test series, the runs made by Raju and Tendulkar are in the ratio 5:9 and Tendulkar and Azar are in the ratio 6:7. What are the runs made by them if Azar makes 187 runs more than Raju?

If A:B = 2:3 and B:C = 9:5 find A:B:C?

If A:B = 3/5:5/7 and B:C = 3/4:2/5, find A:B:C?

If R:S = 0.01:0.11 and S:T = 2.2:1, Find R:S:T?

In a school, Under a Saving Scheme, Rekha saves 3/2 times the savings of Bindu, Geeta saves 4 times the savings of Rekha. Find the ratio of the savings of all the three in least terms?

Simplification

A man left 1/3 rd of his property to his wife and 3/5 th of the remainder to his daughter. He gave the rest to his son who received Rs. 6400/-. How much was his original property worth?

Ratio and Proportion

In a school 4/9 of the pupils are boys. There are 125 girls. How many boys are there?

If A:B = 3:4 and B:C = 5:6, find A:C?

IF X:Y = 3:4 AND Y:Z = 4:5, FIND X:Y:Z?