What will you get by the cursor attribute SQL%ROWCOUNT?

The cursor attribute SQL%ROWCOUNT will return the number of rows that are processed by a SQL statement.

What is the maximum number of triggers, you can apply on a single table?

12 triggers.

How many types of triggers exist in PL/SQL?

There are 12 types of triggers in PL/SQL that contains the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL keywords.

BEFORE ALL ROW INSERT
AFTER ALL ROW INSERT
BEFORE INSERT
AFTER INSERT etc.

What are the advantages of stored procedure?

Modularity, extensibility, reusability, Maintainability and one time compilation.

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.

what are the two virtual tables available at the time of database trigger execution?

Table columns are referred as THEN.column_name and NOW.column_name.

For INSERT related triggers, NOW.column_name values are available only.

For DELETE related triggers, THEN.column_name values are available only.

For UPDATE related triggers, both Table columns are available.

What is mutating table error?

Mutating table error is occurred when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables.

What is the main reason behind using an index?

Faster access of data blocks in the table.

What will you get by the cursor attribute SQL%FOUND?

It returns the Boolean value TRUE if at least one row was processed.

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.