Oracle PL/SQL Interview Questions and Answers

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.

What is Savepoint?
Savepoint is a point within a particular transaction to which you may rollback without rolling back the entire transaction.
What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?

A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.

A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

What are the various types of Exceptions ?

User defined and Predefined/System defined Exceptions.

What are the various types of database triggers ?
There are 12 types of triggers, they are combination of : Insert, Delete and Update Triggers. Before and After Triggers. Row and Statement Triggers.
What are the advantages/disadvantages of clusters ?
advantages : Access time reduced for joins. disadvantages : The time for Insert increases.
What is a forward declaration ? What is its use ?
PL/SQL requires that you declare an identifier before using it.Therefore, you must declare a subprogram before calling it.This declaration at the start of a subprogram is called forward declaration.A forward declaration consists of a subprogram specification terminated by a semicolon.
What are the types of Notation ?
Position, Named, Mixed and Restrictions.
If I have an execute privilege on a procedure in another users schema, can I execute his procedure even though I do not have privileges on the tables within the procedure ?
Yes

Latest Updates

Physics

Atmospheric pressure at sea level equals ?

General Knowledge of India

Which of the following dance forms was introduced by the great Vaishnava saint and reformer Mahapurusha Sankaradeva ?

Which of the following is the only classical dance of india having links with Muslim culture and represents a unique synthesis of Hindu and Muslim genius in art ?

Time and Distance

Starting from the same point at the same time, Ruhan and Yuvan had to reach the same destination. While Ruhan drove at 45 km/hr, Yuvan was slower by 5 km/hr. If Yuvan arrived at the destination an hour after Ruhan did, the distance covered by each, in Kilometres, was ?

Simple Interest

A sum of money was invested at a certain rate of simple interest for 4 years. Had the sum been invested at a rate 3% higher than the actual rate, the interest earned would have been 720 Rs more. Find the sum initially invested?

Ratio and Proportion

A bag contains red, blue and black balls. The ratio of red to blue balls is 3 : 5. The ratio of black to red balls is 3 : 4. What is the ratio of blue to black balls?

Profit and Loss

Selling a pack of incense sticks for 136 Rs resulted in a loss of 15%. To make a profit of 10%, the selling price should be ?

Percentage

23% of a bucket full of diluted milk was water. If the quantity of pure milk in the bucket was 15.4 liter, then the capacity of the bucket was ?

Average

The average of the marks obtained by three girls in a test was twice the average of the marks obtained by eight boys in that test. If the total marks obtained by all these examinees was 168, find the average marks each of the girls obtained ?

Pipes and Cistern

44 pipes can fill a large water tank in 27 hours. How many hours it take for 66 pipes to fill four such tanks ?

HCF and LCM

A natural number, when divided by 3, 4, 5, 6, and 7 respectively, leaves corresponding remainder of 2, 3, 4, 5 and 6. What is the smallest of all such numbers fulfilling the above condition ?

Coding-Decoding

If in a certain code language, PRINT is written as 34825 and TRUE is written as 5479. Then, how will TENURE be written in that language?

Order Sequence

Arrange the given words from smallest to biggest-
1.Jug
2.Mug
3.Bucket
4.Drum
5.Cup

Statement and Conclusion

Statements
All socks are shoes
Some shoes are black

Conclusions
I. Some shoes are socks.
II. No scoks is black

Numbers

The difference between the place value of 4 and 2 in the number 833749502 is ?

Analogy

GIKM : HJLN :: PRTV : .........?

Order Sequence

In a row of girls, Nanda is eighth from the left and Chanda is nineteenth from right. When Nanda and Chanda interchange their positions, Chanda becomes twnty-third form right. How many girls are there in the row?

Analogy

Summer: Day :: Winter : ........?