Home / Interview / PL/SQL :: General Questions

Interview :: PL/SQL

11) Write a unique difference between a function and a stored procedure.

A function returns a value while a stored procedure doesn?t return a value.

12) How exception is different from error?

Whenever an Error occurs Exception arises. Error is a bug whereas exception is a warning or error condition.

13) What is the main reason behind using an index?

Faster access of data blocks in the table.

14) What are PL/SQL exceptions? Tell me any three.
  1. Too_many_rows
  2. No_Data_Found
  3. Value_error
  4. Zero_error etc.
15) How do you declare a user-defined exception?

You can declare the User defined exceptions under the DECLARE section, with the keyword EXCEPTION.

Syntax:

16) What are some predefined exceptions in PL/SQL?

A list of predefined exceptions in PL/SQL:

  • DUP_VAL_ON_INDEX
  • ZERO_DIVIDE
  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • CURSOR_ALREADY_OPEN
  • INVALID_NUMBER
  • INVALID_CURSOR
  • PROGRAM_ERROR
  • TIMEOUT _ON_RESOURCE
  • STORAGE_ERROR
  • LOGON_DENIED
  • VALUE_ERROR
  • etc.
17) What is a trigger in PL/SQL?

A trigger is a PL/SQL program which is stored in the database. It is executed immediately before or after the execution of INSERT, UPDATE, and DELETE commands.

18) What is the maximum number of triggers, you can apply on a single table?

12 triggers.

19) How many types of triggers exist in PL/SQL?

There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.

  • BEFORE ALL ROW INSERT
  • AFTER ALL ROW INSERT
  • BEFORE INSERT
  • AFTER INSERT etc.
20) 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.