PL/SQL interview questions and answers 👇

  1. PL/SQL Interview Questions


PL/SQL Interview Questions

1.

What are Constraints?

↑

Constraints are the rules enforced on the data columns of a table. These are used to limit the type of data that can go into a table.

2.

What is ROLLBACK?

↑

The ROLLBACK statement is the inverse of the COMMIT statement. It undoes some or all database changes made during the current transaction.

3.

What is the mutating table?

↑

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint.

4.

What is Exception handling in PL/SQL?

↑

Exception handling is a mechanism that is implemented to deal with runtime errors. PL/SQL provides the exception block that raises the exception, thus helping the programmer to find the fault and resolve it. When an error occurs, the program’s error handling code is included.

5.

What are expressions?

↑

An expression is an arbitrarily complex combination of operands (variables, constants, literals, operators, function invocations, and placeholders) and operators.

6.

What is database trigger?

↑

A PL/SQL trigger is a named database object that encapsulates and defines a set of actions that are to be performed in response to an insert, update, or delete operation against a table.

7.

What are sequences?

↑

A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

8.

What is Data Consistency?

↑

Data consistency means that each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.

9.

What are Anonymous blocks?

↑

The PL/SQL anonymous block statement is an executable statement that can contain PL/SQL control statements and SQL statements.

10.

What is a Join?

↑

Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.

11.

What is a View?

↑

A View logically represents subsets of data from one or more tables. A View is a logical table based on a table or another view. A View contains no data of its own but is like a window through which data from tables can be viewed or changed.

12.

What is a Literal?

↑

The terms literal and constant value are synonymous and refer to a fixed data value. For example, 'JANE', 'PINK FLOYD', and '208' are all character literals; 2002 is a numeric literal. Character literals are enclosed in single quotation marks so that Oracle can distinguish them from schema object names.

13.

What is a Dual Table?

↑

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY , defined to be VARCHAR2(1) , and contains one row with a value X .

14.

What is a Sub Query?

↑

A subquery is a SELECT statement that is embedded in a clause of another SELECT statement. A subquery can be placed in WHERE HAVING and FROM clause.

15.

What is ALL operator?

↑

ALL Operator compares value to every value returned by the subquery.

ALL means more than the maximum < ALL means less than the minimum <> ALL is equivalent to NOT IN condition.

16.

What is a MERGE statement?

↑

The Oracle MERGE statement selects data from one or more source tables and updates or inserts it into a target table. The MERGE statement allows you to specify a condition to determine whether to update data from or insert data into the target table.

17.

What are Joins?

↑

Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.

18.

What is a Tuple?

↑

A tuple is a way to refer to a member or a member combination from any number of dimensions.

19.

What is locking in SQL?

↑

Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback.

Types of locking are:

  • Implicit Locking: This occurs for all SQL statements except SELECT.
  • Explicit Locking: This can be done by the user manually.
20.

What is a SET UNUSED option?

↑

SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped, even though their column data remains in the table’s rows. After a column has been marked as unused, you have no access to that column.

21.

Explain Truncate.

↑

Truncate removes all rows from a table and releases storage space used by that table.

22.

What are Constraints?

↑

Constraints are used to prevent invalid data entry or deletion if there are dependencies. Constraints enforce rules at the table level. Constraints can be created either at the same time as the table is created or after the table has been created. Constraints can be defined at the column or table level. Constraint defined for a specific table can be viewed by looking at the USER-CONSTRAINTS data dictionary table. You can define any constraint at the table level except NOT NULL which is defined only at the column level.

There are 5 types of constraints:

  • Not Null Constraint
  • Unique Key Constraint
  • Primary Key Constraint
  • Foreign Key Constraint
  • Check Key Constraint
23.

What is a Candidate Key?

↑

A candidate key is a key that uniquely identifies rows in a table. Any of the identified candidate keys can be used as the table's primary key. Candidate keys that are not part of the primary key are called alternate keys.

24.

What are Triggers?

↑

Triggers are stored programs, which are automatically executed or fired when some events occur.

25.

What is the Discard File?

↑

The DISCARD FILE command removes the definition of a file from the local CICS system, so that the system no longer has access to the file; that is, it revokes the earlier installation of a FILE resource definition of the same name. A file must be closed and disabled for its definition to be discarded.

26.

What are Global Variables?

↑

A global variable is a data structure that can be referenced outside the scope or block in which it’s declared.

27.

What is Forward Declaration?

↑

Forward declaration/reference means declaring the private elements separately and defining it in the later part of the package body. Private elements can be referred only if it is already declared in the package body. For this reason, forward declaration is used.

28.

What is an index?

↑

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

29.

What are predefined exceptions?

↑

Predefined exceptions are internally defined exceptions that occur during the execution of a program.

Some examples: TOO_MANY_ROWS: if more than one row is returned using a select statement NO_DATA_FOUND: when there are no rows returned upon a select operation COLLECTION_IS_NULL: when a collection is null CURSOR_ALREADY_OPEN: When a cursor is already open LOGIN_DENIED: When login is incorrect or permission is not there

30.

What are Character Functions?

↑
31.

What is Merge?

↑

You can use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations.

32.

What is rollback?

↑

Rollback erases all the database changes including all the savepoints. It ends a transaction.