PL/SQL Interview Questions and Answers (2023)

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.

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

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.

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.

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

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.

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.

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.

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

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.

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.

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.

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 .

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.

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.

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.

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.

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

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.

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.

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

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

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.

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

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.

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

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.

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.

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

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.

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