PL/SQL Interview Questions and Answers Part 2
9. Triggers vs. Stored Procedure
Answer:
A Trigger is executed implicitly when a DML statement is issued on a database table.
A stored procedure must be called explicitly by issuing procedure call from another block.
10. Why is %ISOPEN always false for an implicit cursor?
Answer:
SQL%ISOPEN attribute is always false because implicit cursor is closed immediately after the execution of DML statement.
11. Explain Exception handling in PL/SQL.
Answer:
Exception handling lets program continue operating despite a run-time error such as stack overflow or division by zero. Exception is a warning and error condition. When an error occurs, an exception is raised which means normal execution stops and control transfers to the exception-handling part of your PL/SQL block. We can write a separate routine (exception handler) to handle raised exceptions.
Types of Exceptions
Predefined Exceptions: There are many pre-defined exceptions in PL/SQL that are raised at the time when database rules are violated. For example - NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows.
User-defined Exceptions: A programmer can define his own exceptions to handle errors that violate business rules. It must be declared and then raised explicitly using RAISE statement.
12. What is the use of INSTEAD OF triggers?
Answer:
Instead OF triggers enable us to modify views which cannot be directly modified through SQL DML statements.
We can write DML statements such as INSERT, UPDATE and DELETE against a view and the INSTEAD OF trigger fires to update the underlying tables.
13. What are the different tracing methods in PL/SQL?
Answer:
DBMS_APPLICATION_INFO
DBMS_TRACE
DBMS_SESSION and DBMS_MONITOR
trcsess and tkproof utilities
14. Explain Function, Procedure and Package in PL/SQL.
Answer:
Function: It's main purpose is to compute and return a single value.
Procedure: It can consist of several SQL statements and return multiple values.
Package: It is a schema object which groups logically related functions, procedures, variables and record type statements, thereby providing modularity to PL/SQL programs.
