PL/SQL interview questions and answers 👇

  1. PL/SQL Interview Questions


PL/SQL Interview Questions

What are Constraints?

View answer

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.

What is ROLLBACK?

View answer

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

What is the mutating table?

View answer

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.

What is Exception handling in PL/SQL?

View answer

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.

What are expressions?

View answer

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

What is database trigger?

View answer

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.

What are sequences?

View answer

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.

What is Data Consistency?

View answer

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.

What are Anonymous blocks?

View answer

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

What is a Join?

View answer

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.

What is a View?

View answer

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.

What is a Literal?

View answer

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.

What is a Dual Table?

View answer

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 .

What is a Sub Query?

View answer

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.

What is ALL operator?

View answer

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.

What is a MERGE statement?

View answer

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.

What are Joins?

View answer

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.

What is a Tuple?

View answer

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

What is locking in SQL?

View answer

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.

What is a SET UNUSED option?

View answer

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.

Explain Truncate.

View answer

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

What are Constraints?

View answer

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

What is a Candidate Key?

View answer

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.

What are Triggers?

View answer

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

What is the Discard File?

View answer

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.

What are Global Variables?

View answer

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

What is Forward Declaration?

View answer

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.

What is an index?

View answer

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.

What are predefined exceptions?

View answer

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

What are Character Functions?

View answer

What is Merge?

View answer

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.

What is rollback?

View answer

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