PL/SQL Interview Questions – Set 04

by

What is the difference between execution of triggers and stored procedures?

A trigger is automatically executed without any action required by the user, while, a stored procedure is explicitly invoked by the user.

What are the cursor attributes used in PL/SQL?

%ISOPEN: it checks whether the cursor is open or not.

%ROWCOUNT: returns the number of rows affected by DML operations: INSERT,DELETE,UPDATE,SELECT.

%FOUND: it checks whether cursor has fetched any row. If yes – TRUE.

%NOTFOUND: it checks whether cursor has fetched any row. If no – TRUE.

What is exception? What are the types of exceptions?

Exception is an error handling part of PL/SQL. There are two type of exceptions: pre_defined exception and user_defined exception.

What is stored Procedure?

A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions. It is similar to a procedure in other programming languages. It is stored in the database and can be repeatedly executed. It is stored as schema object. It can be nested, invoked and parameterized.

What is consistency?

Consistency simply means that each user sees the consistent view of the data.

Consider an example: there are two users A and B. A transfers money to B’s account. Here the changes are updated in A’s account (debit) but until it will be updated to B’s account (credit), till then other users can’t see the debit of A’s account. After the debit of A and credit of B, one can see the updates. That?s consistency.

What are PL/SQL exceptions? Tell me any three.

  • Too_many_rows
  • No_Data_Found
  • Value_error
  • Zero_error etc.

What will you get by the cursor attribute SQL%NOTFOUND?

It returns the Boolean value TRUE if no rows were processed.

What is the purpose of using PL/SQL?

PL/SQL is an extension of SQL. While SQL is non-procedural, PL/SQL is a procedural language designed by Oracle. It is invented to overcome the limitations of SQL.

What are the most important characteristics of PL/SQL?

A list of some notable characteristics:

  • PL/SQL is a block-structured language.
  • It is portable to all environments that support Oracle.
  • PL/SQL is integrated with the Oracle data dictionary.
  • Stored procedures help better sharing of application

What happens when a trigger is associated to a view?

When a trigger is associated to a view, the base table triggers are normally enabled.