PL/SQL Interview Questions & Answers

Answer:

PL/SQL refers to a procedural language extension to SQL. It supports procedural features of both SQL & programming language. Oracle Corporation has developed PL/SQL in the early ‘90s to enhance the capabilities of SQL. It helps users build complex database applications using functions, modules, control structures, procedures, etc.

Answer:

• TYPE RECORD– It provides the data type of a table column or variable. You can use it to declare variables that hold database values. For instance, if you have a column, user_id in the users’ table, so to declare a variable with the same data as the users.user_id you will write: user_id users.user_id%TYPE;.
• % ROWTYPE: A composite type variable is called a row-type variable or row variable. Such a variable can hold the entire row of FOR or SELECT query result so that the query’s column set matches the declared variable type. All fields of row value are accessed through the usual dot notation, e.g., rowvar.field.

Answer:

A cursor for loop processes rows in the explicit cursor. It is a shortcut as a cursor is opened, rows are fetched only once for every iteration in the loop, the loop exists after the last row is processed, & the cursor gets closed automatically. The loop is then terminated at the iteration end, where the last row is fetched.

Answer:

Database trigger refers to a PL/SQL program unit associated with a specific database table. It is used for the following functions:

• Log events transparently
• Maintain replica tables
• Audit data modifications
• Derive column values
• Enforce complex business rules.
• Implement complex security authorizations

Answer:

Exception is an error handling part of PL/SQL, classified in two types: predefined Exception & user_defined Exception.

Answer:

You can use the Raise_application_error procedure to issue user_defined error messages from the database trigger or stored sub-program. You can report any errors to your application & avoid returning the unhandled exceptions. It is used in two different places; exceptional section & executable section.

Syntax:  raise_application_error(error_number,message[,{true|false}];

In the syntax:

• Error_number: It is a user-specified no. for the exception between -20000 & -20999.
• Message: It is the user-specified message for the exception, a character string up to 2048 bytes.
• True|False: It is an optional Boolean parameter (if true, the error is placed on the previous error stack. If false, the default, the error replaces all the previous errors).

Answer:

The two table columns available at the time of database trigger execution are called THEN.column_name & NOW.column_name.

• For INSERT-related triggers, only NOW.column_name values are available.
• For DELETE-related triggers, only THEN.column_name values are available.
• For UPDATE-related triggers, both the table columns are available.

Answer:

When doing comparisons, the following rules are applied to the NULLs:

• NULL is never FALSE or TRUE;
• NULL cannot be unequal or equal to the other values;
• If a value in an expression is NULL, the expression itself evaluates to the NULL except for the concatenation operator (||).

Answer:

The compilation process includes binding, syntax check, & p-code generation processes.
Syntax checks the PL SQL codes for compilation errors. When all the errors are rectified, a storage address is assigned to variables that hold data called binding. P-code is an instructions list for the PL SQL engine. P-code is stored in a database for named blocks & used the next time.

Answer:

Answer:

Three specifications, COMMIT, SAVEPOINT, & ROLLBACK, are available in PL/SQL.

• COMMIT Statement: When performing DML operation, it only manipulates data in the database buffer while the database remains unaffected by the changes. To save or store the transaction changes to a database, we need to COMMIT the transaction. The COMMIT transaction saves all the outstanding changes since the last COMMIT.
• ROLLBACK Statement: When we want to erase or undo all the changes in the current transaction, we need to be rolled back of the transaction. In simple words, ROLLBACK erases all the outstanding changes since the last ROLLBACK or COMMIT.
• SAVEPOINT Statement: It gives a name & marks a point in the current transaction processing. The changes & locks that occurred before the SAVEPOINT in a transaction are preserved when changes occurred after the SAVEPOINT are released.

Answer:

Cursor is a SQL private work area. It opens an area of memory where a query is executed & parsed.

• Implicit Cursor: It is declared by PL/SQL implicitly for all DML & PL/SQL statements, including queries that return only one row.
• Explicit: Explicit cursors are declared for queries that return more than a row, and named by the developer, and manipulated through particular statements in the block’s executable actions.

Answer:

Mutating table error occurs when a trigger tries to update a currently using row. It is fixed through temporary tables or views, so the database selects one & updates another.

Answer:

DECLARE is used as the first statement for a stand-alone file containing anonymous blocks of code which are not stored procedures, triggers, or functions.

Answer:

• SQLCODE returns the numeric value for an error message or error code through two functions. Based on the code or message value, you can determine which subsequent action you should take based on the error.
• SQLERRM returns character data containing the message related to an error number.

Answer:

No, SQL*Plus does not have a PL/SQL Engine embedded in it. Thus, all the PL/SQL codes are sent to the database engine directly. It makes it essential as each statement is not stripped off individually.

Answer:

Answer:

• Triggering SQL Statement: It is a DML statement that invokes the triggers. It directs when to call a trigger before or after, on which DML statement of the table – INSERT/ DELETE/ UPDATE/ and whether to call a trigger when only a few columns are processed or the entire table is processed. BEFORE & AFTER is used on tables & INSTEAD OF is used on the views to create triggers.
• Trigger Restriction: This part of the trigger determines the number of times the trigger needs to be executed. It informs whether a trigger has to be invoked for each row INSERT /UPDATE/DELETE, or only once for a transaction.
• Trigger Action: This part of the trigger actually performs a set of transactions as a result of the original DML statement. For instance, to insert records into the log tables, it does not always need to insert the logs so it can be any transaction.

Answer:

Character functions are those functions that manipulate the character data. These are popularly known as string functions. Below are some examples of character functions:

Answer:

The TTITLE command defines the top title, while the BTITLE command defines the bottom title.