PL/Sql Interview Questions Part 3

1. What are some predefined exceptions in PL/SQL?

A list of predefined exceptions in PL/SQL:

• DUP_VAL_ON_INDEX
• ZERO_DIVIDE
• NO_DATA_FOUND
• TOO_MANY_ROWS
• CURSOR_ALREADY_OPEN
• INVALID_NUMBER
• INVALID_CURSOR
• PROGRAM_ERROR
• TIMEOUT _ON_RESOURCE
• STORAGE_ERROR
• LOGON_DENIED
• VALUE_ERROR
• etc.

2. What is a trigger in PL/SQL?

• A trigger is a PL/SQL program which is stored in the database.
• It is executed immediately before or after the execution of INSERT, UPDATE, and DELETE commands.

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

12 triggers.

4. 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.

5. What is the difference between execution of triggers and stored procedures?

• A trigger is automatically executed without any action required by the user, while a stored procedure is explicitly invoked by the user.

6. What happens when a trigger is associated to a view?

• When a trigger is associated to a view, the base table triggers are normally enabled.

7. What is the usage of WHEN clause in trigger?

• A WHEN clause specifies the condition that must be true for the trigger to be triggered.

8. How to disable a trigger name update_salary?

• ALTER TRIGGER update_salary DISABLE;

9. Which command is used to delete a trigger?

• DROP TRIGGER command.

10. 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.

11. What is stored Procedure?

A stored procedure is a sequence of statement or a named PL/SQL block which performs one or more specific functions.

• It is similar to a procedure in other programming languages.
• It is stored in the database and can be repeatedly executed.
• It is stored as schema object.
• It can be nested, invoked and parameterized.

12. What are the different schemas objects that can be created using PL/SQL?

• Stored procedures and functions
• Packages
• Triggers
• Cursors

13. What do you know by PL/SQL Cursors?

• Oracle uses workspaces to execute the SQL commands.
• When Oracle processes a SQL command, it opens an area in the memory called Private SQL Area.
• This area is identified by the cursor.
• It allows programmers to name this area and access it’s information.

14. What is the difference between the implicit and explicit cursors?

• Implicit cursor is implicitly declared by Oracle.
• This is a cursor to all the DDL and DML commands that return only one row.
• Explicit cursor is created for queries returning multiple rows.

15. 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.