Home / Interview / PL/SQL :: General Questions

Interview :: PL/SQL

1) What is PL/SQL?

PL/SQL stands for procedural language extension to SQL. It supports procedural features of programming language and SQL both. It was developed by Oracle Corporation in early of 90's to enhance the capabilities of SQL.

2) 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.

3) 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.
4) 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.

5) What are the datatypes available in PL/SQL?

There are two types of datatypes in PL/SQL:

  1. Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN etc.
  2. Composite datatypes Example are RECORD, TABLE etc.
6) What is the basic structure of PL/SQL?

PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statement which form a PL/SQL block.

PL/SQL block contains 3 sections.

  1. The Declaration Section (optional)
  2. The Execution Section (mandatory)
  3. The Exception handling Section (optional)
7) What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?

Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.

Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.

Package: A package is schema object which groups logically related PL/SQL types , items and subprograms. You can also say that it is a group of functions, procedure, variables and record type statement. It provides modularity, due to this facility it aids application development. It is used to hide information from unauthorized users.

8) 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.

9) How to write a single statement that concatenates the words ?Hello? and ?World? and assign it in a variable named Greeting?

Greeting := 'Hello' || 'World';

10) Does PL/SQL support CREATE command?

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