Oracle PL/SQL Interview Questions and Answers

What is a package spec and package body? Why the separation?

Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs.
Separation helps make development easier. Dependency is simplified. You can modify body without invalidating dependent objects.

What is the difference between anonymous blocks and stored procedures?

Anonymous block is PL/SQL code and compiled only when called.
Stored procedure is compiled and stored in database with the dependency information as well.

Can you use COMMIT in a trigger?
Yes but by defining an autonomous transaction.
What is an autonomous transaction?
An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.
What is the difference between implicit and explicit cursors?
An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements.
What are the cursor attributes used in PL/SQL?

% ISOPEN – Used to check whether a cursor is open or not.
% ROWCOUNT – Used to check the number of rows fetched/updated/deleted.
% FOUND – Used to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND – Used to check whether cursor has fetched any row. True if no rows are fetched.

What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?

% TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are:
I. Need not know about variable’s data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

What are the datatypes a available in PL/SQL?
Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.
What are the components of a PL/SQL Block?

Declarative part
Executable part
Exception part

What is a stored procedure ?
A stored procedure is a sequence of statements that perform specific task. A stored procedure is a named pl/sql block which performs an action.It is stored in the database as a schema object and can be repeatedly executed.It can be invoked, parameterised and nested.
What is a cursor ? Why Cursor is required?
Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
What are advantages of Stored Procedures?

Extensibility,Modularity, Reusability, Maintainability and one time compilation.

How can a function retun more than one value in oracle with proper example?
Basically as per property of function it has to return one value. So the other values can be returned from the out parameter of the function. But its advised if you want more that one return value go for procedure however function will also yield the same result.
What is a cursor for loop ?

Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.
eg. 

FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;
Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger? Why?
It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing. yes WE can use COMMIT and ROLLBACK triggers, but by using PRAGAMA AUTONAMOUS_TRANSATIONS. Now the transation treated as a autonomous tansation.
What is a database trigger ? Name some usages of database trigger ?

Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.

Unlike the stored procedure and functions, which have to be called explicitly, the database triggers are fires (executed) or called implicitly whenever the table is affected by any of the above said DML operations.

Till oracle 7.0 only 12 triggers could be associated with a given table, but in higher versions of Oracle there is no such limitation. 

A database trigger has three parts
1. A triggering event
2. A trigger constraint (Optional)
3. Trigger action

A triggering event can be an insert, update, or delete statement or a instance shutdown or startup etc. The trigger fires automatically when any of these events occur.
A trigger constraint specifies a Boolean expression that must be true for the trigger to fire. This condition is specified using the WHEN clause. 
The trigger action is a procedure that contains the code to be executed when the trigger fires.

Latest Updates

Articles

Choose appropriate article for the given sentence:
__________ teachers had ________ late lunch as their meeting lasted longer than___ hour.

Child Development & Pedagogy

Which of the following is the most effective way to create interest for learning in a student?

The rules of presenting the content to make them easy are called_________?

The term SLD in children’s mental health denotes for?

The child will be able to use proper sentences by the age of?

Indian Politics

______is an institutional mechanism to provide representation to the states of India?

Books and Authors

Which text is not written by Kalidasa?

Indian Constitution

Which Constitutional Amendment introduced Right to Education as a Fundamental Right?

Indian History

Which animal is engraved on Harappan seals?

Indian Politics

Who is known for initiating electoral reforms in India to ensure free and fair elections?

Indian Constitution

The sequence in which the given terms are mentioned in the preamble to the Constitution of India is?

Which of the following parts of the Constitution of India provides fundamental rights ?

Books and Authors

Who among the following is the author of ‘Politics’?

Which one is the text not written by Abul Fazl?

Which King was the author of Ratnawali, Nagananda, Priyadarsika?

Fill in the blank

Fill in the blanks with the most appropriate preposition in the given sentence.
The class begins _______ 7:30 am _____ the morning.

The workers were so skilled that their _____ was quite visible even to a layman.

Articles

Choose appropriate article for the given sentence:
______ German is ___ easy language to learn for English speaking students.

Latest Job Notifications

Banking Jobs

बैंकिंग कार्मिक चयन संस्थान Institute of Banking Personnel Selection(IBPS) - 7855 पद - 29/07/2022 अंतिम तिथि

SSC Recruitment

कर्मचारी चयन आयोग (एसएससी) Staff Selection Commission (SSC) - 1411 पद - 29/07/2022 अंतिम तिथि

Jobs in Rajasthan

राजस्थान अधीनस्थ और मंत्रिस्तरीय सेवा चयन बोर्ड Rajasthan Subordinate and Ministerial Services Selection Board (RSMSSB) - 5546 पद - 22-Jul-2022 अंतिम तिथि

Railway Recruitment

उत्तर मध्य रेलवे भर्ती प्रकोष्ठ Railway Recruitment Cell(NCR) - 1659 पद - 01/08/2022 अंतिम तिथि

Jobs in West Bengal

इंडियन पोस्ट सर्विस Indian Post Service - 2357 पद - 19/08/2021 अंतिम तिथि - पश्चिम बंगाल पोस्टल सर्कल

Banking Jobs

भारतीय स्टेट बैंक State Bank of India(SBI) - 6100 पद - 26/07/2021 अंतिम तिथि - CRPD/APPR/2021-22/10

SSC Recruitment

कर्मचारी चयन आयोग Staff Selection Commission(SSC) - 25271 पद - 31/08/2021 अंतिम तिथि

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 अंतिम तिथि