Oracle PL/SQL Interview Questions and Answers

What is the difference between Procedure and Function?

Differences between Functions and Procedures

-Functions are normally used for computations where as procedures are normally used for executing business logic.

-Functions must return a value (using the RETURN keyword), but for stored procedures this is not compulsory. Procedures can use RETURN keyword but without any value being passed.

-Functions could be used in SELECT statements, but they should don't do any data manipulation. However, procedures cannot be included in SELECT statements.

-Functions could be used in creating "Function Based Indexes" to improve the performance ,but procedure not.

Post comment
Cancel
Thanks for your comment.!
Write a comment(Click here) ...
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.

What is Commit, Rollback and Save point?

Commit – Makes changes to the current transaction permanent. It erases the savepoints and releases the transaction locks.

Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.

Rollback – This statement is used to undo work.

How do you make a Function and Procedure as a Private?
Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.
What is the difference between Package, Procedure and Functions?

A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.

Procedure is a sub program written to perform a set of actions and can return multiple values.

Function is a subprogram written to perform certain computations and always return a value.

What are the modes for passing parameters to Oracle?

There are three modes for passing parameters to subprograms
IN – An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.

What is Raise_application_error?
Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue a user_defined error messages from stored sub-program or database trigger.
What is Pragma EXECPTION_INIT? Explain the usage?

he PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. 
E.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. 

SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code.

Latest Updates

Percentage

20% of a quantity is Rs 400, the quantity equals?

Time and Work

A certain piece of work can be completed by 12 men in 9 days. After they have worked for six days, six more men joined them. How many days will it take to complete the remaining work?

Ratio and Proportion

A certain sum was divided among A, B and C in the ratio 5 : 7 : 6. If B received Rs 4500 more than C, find C's share?

Problems on Ages

A is two years older than B who is twice as old as C. If the total of the ages of A, B and C be 27, the how old is A?

Time and Work

A and B can together finish a work in 30 days. They worked at it for 20 days and then B left. The remaining work was done by A alone in 20 more days. B alone can do the entire work in?

Discount

A retailer buys a washing machine marked at Rs 4800 and gets two successive discounts of 15% and 5%. If he spends Rs 124 on transportation and sells the washing machine at a gain of 13%, then find the selling price of the machine.

Boats and Streams

Speed of a boat in still water is 10 kmph, and speed of the stream is 5 Kmph. In how many minutes would the boat cover a distance of 5 km downstream?

Problems on Ages

The age of two persons differ by 2 years. If the sum of their ages is equal to the square root of 144 then find the age of the elder among the two person?

The age of A is six times the age of his son. A's father is 40 years older than him. Together, they are 118 years old. What is the age of A?

Average

The average mark of 50 students is 75. If the marks obtained by one of the student had been written as 36 instead of 86, then the corrected average of marks is:

Unix/Linux

What is inode?

Problems on Ages

A group of 15 people has average age of 45 years. Two persons are excluded and the average of the remaining is increased by 5 years. What is the average age of the two persons who have been excluded?

Average

In the world cup, Yuvraj has a batting average of 65 in 7 matches till semi-final. How much should Yuvraj score in the 8th match ie the final match to get a batting average of 85?

Problems on Trains

A train takes 8 sec to cross a 75 metre long platform at a speed of 90 km/h. What is the length of the train?

Alligation or Mixture

In what ratio must Rs 50 per kg salt be mixed with Rs 80 per kg salt to obtain a mixture worth of Rs 60 per kg?

Numbers

Find the sum of the given series: 3 + 9 + 27 + 81 + 243 + 729 + 2187 + 6561

Average

Find the average. 62, 54, 44, 36, 28 & 22

Percentage

In an examination, 36% of the students failed in English and 40% failed in Hindi. If 20% of the students failed in both subjects, then the pass percentage is?

Latest Job Notifications

Jobs in Madhya Pradesh

राष्ट्रीय स्वास्थ्य मिशन मध्य प्रदेश National Health Mission MP(NRHM) - 5215 पद - 22/06/2021 अंतिम तिथि

Jobs in Punjab

पंजाब स्टेट पावर कॉर्पोरेशन लिमिटेड Punjab State Power Corporation Limited (PSPCL) - 2632 पद - 20/06/2021 अंतिम तिथि

Jobs in Chhattisgarh

स्वास्थ्य सेवा निदेशालय, छत्तीसगढ़ Department of Health & Family Welfare, Chhattisgarh - 267 पद - 26/06/2021 अंतिम तिथि

Government Jobs

दिल्ली सबऑर्डिनेट सर्विस सिलेक्शन बोर्ड Delhi Subordinate Services Selection Board(DSSSB) - 5807 पद - 03/07/2021 अंतिम तिथि

Jobs in Madhya Pradesh

राष्ट्रीय स्वास्थ्य मिशन मध्य प्रदेश National Health Mission MP(NRHM) - 2850 पद - 31/05/2021 अंतिम तिथि

Government Jobs

इंडियन पोस्ट सर्विस Indian Post Service - 4368 पद - 29/05/2021 अंतिम तिथि

दिल्ली सबऑर्डिनेट सर्विस सिलेक्शन बोर्ड Delhi Subordinate Services Selection Board(DSSSB) - 7236 पद - 24/06/2021 अंतिम तिथि

Banking Jobs

Reserve Bank of India (RBI) - 841 Posts - 15/03/2021 Last date

Institute of Banking Personnel Selection (IBPS) - CRP SPL-IX - 1163 Posts - 26/11/2019 Last date

Government Jobs

Reserve Bank of India (RBI) - 199 Posts - 11/10/2019 Last date