PL/SQL Interview Questions – Set 01

by

What is PL/SQL table? Why it is used?

Objects of type tables are called PL/SQL tables that are modeled as database table. We can also say that PL/SQL tables are a way to providing arrays. Arrays are like temporary tables in memory that are processed very quickly. PL/SQL tables are used to move bulk data. They simplifies moving collections of data.

What is the usage of WHEN clause in trigger?

A WHEN clause specifies the condition that must be true for the trigger to be triggered.

Explain the Commit statement.

Following conditions are true for the Commit statement:

  • Other users can see the data changes made by the transaction.
  • The locks acquired by the transaction are released.
  • The work done by the transaction becomes permanent.

Does PL/SQL support CREATE command?

No. PL/SQL doesn’t support the data definition commands like CREATE.

What do you know by PL/SQL Cursors?

Oracle uses workspaces to execute the SQL commands. When Oracle processes a SQL command, it opens an area in the memory called Private SQL Area. This area is identified by the cursor. It allows programmers to name this area and access it?s information.

How many types of cursors are available in PL/SQL?

There are two types of cursors in PL/SQL.

  1. Implicit cursor, and
  2. explicit cursor

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

What are the two different parts of the PL/SQL packages?

PL/SQL packages have the following two parts:

Specification part: It specifies the part where the interface to the application is defined.

Body part: This part specifies where the implementation of the specification is defined.

What are the datatypes available in PL/SQL?

There are two types of datatypes in PL/SQL:

  • Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
  • Composite datatypes Example are RECORD, TABLE etc.

How to disable a trigger name update_salary?

ALTER TRIGGER update_salary DISABLE;