52.  What is database Trigger?

            A database trigger is a PL/SQL block that can defined to automatically execute for insert, update, and delete statements against a table. The trigger can e defined to execute once for the entire statement or once for every row that is inserted, updated, or deleted. For any one table, there are twelve events for which you can define database triggers. A database trigger can call database procedures that are also written in PL/SQL.

 

53.  Name two utilities that Oracle provides, which are use for backup and recovery.

            Along with the RDBMS software, Oracle provides two utilities that you can use to back up and restore the database. These utilities are Export and Import.

The Export utility dumps the definitions and data for the specified part of the database to an operating system binary file. The Import utility reads the file produced by an export, recreates the definitions of objects, and inserts the data

If Export and Import are used as a means of backing up and recovering the database, all the changes made to the database cannot be recovered since the export was performed. The best you can do is recover the database to the time when the export was last performed.

 

54.  What are stored-procedures? And what are the advantages of using them.

Stored procedures are database objects that perform a user defined operation. A stored procedure can have a set of compound SQL statements. A stored procedure executes the SQL commands and returns the result to the client. Stored procedures are used to reduce network traffic.

 

55.  How are exceptions handled in PL/SQL? Give some of the internal exceptions’ name

            PL/SQL exception handling is a mechanism for dealing with run-time errors encountered during procedure execution. Use of this mechanism enables execution to continue if the error is not severe enough to cause procedure termination.

The exception handler must be defined within a subprogram specification. Errors cause the program to raise an exception with a transfer of control to the exception-handler block. After the exception handler executes, control returns to the block in which the handler was defined. If there are no more executable statements in the block, control returns to the caller.

User-Defined Exceptions

PL/SQL enables the user to define exception handlers in the declarations area of subprogram specifications. User accomplishes this by naming an exception as in the following example:

                                ot_failure  EXCEPTION;

In this case, the exception name is ot_failure. Code associated with this handler is written in the EXCEPTION specification area as follows:

EXCEPTION

      when OT_FAILURE then

        out_status_code := g_out_status_code;

        out_msg         := g_out_msg;

The following is an example of a subprogram exception:

EXCEPTION

      when NO_DATA_FOUND then

        g_out_status_code := ‘FAIL’;

        RAISE ot_failure;

Within this exception is the RAISE statement that transfers control back to the ot_failure exception handler. This technique of raising the exception is used to invoke all user-defined exceptions.

System-Defined Exceptions

Exceptions internal to PL/SQL are raised automatically upon error. NO_DATA_FOUND is a system-defined exception. Table below gives a complete list of internal exceptions.

 

PL/SQL internal exceptions.

 

Exception Name

Oracle Error

CURSOR_ALREADY_OPEN

ORA-06511

DUP_VAL_ON_INDEX

ORA-00001

INVALID_CURSOR

ORA-01001

INVALID_NUMBER

ORA-01722

LOGIN_DENIED

ORA-01017

NO_DATA_FOUND

ORA-01403

NOT_LOGGED_ON

ORA-01012

PROGRAM_ERROR

ORA-06501

STORAGE_ERROR

ORA-06500

TIMEOUT_ON_RESOURCE

ORA-00051

TOO_MANY_ROWS

ORA-01422

TRANSACTION_BACKED_OUT

ORA-00061

VALUE_ERROR

ORA-06502

ZERO_DIVIDE

ORA-01476

 

In addition to this list of exceptions, there is a catch-all exception named OTHERS that traps all errors for which specific error handling has not been established.

 

56.  Does PL/SQL support “overloading”? Explain

            The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same name. PL/SQL does not look only at the referenced name, however, to resolve a procedure or function call. The count and data types of formal parameters are also considered.

PL/SQL also attempts to resolve any procedure or function calls in locally defined packages before looking at globally defined packages or internal functions. To further ensure calling the proper procedure, you can use the dot notation. Prefacing a procedure or function name with the package name fully qualifies any procedure or function reference.

 

57.  Tables derived from the ERD

            a) Are totally unnormalised

            b) Are always in 1NF

            c) Can be further denormalised

            d) May have multi-valued attributes

 

 (b) Are always in 1NF

Advertisements