PL SQL Interview Questions

PL SQL is a procedural language which has interactive SQL, as well as procedural programming language constructs like conditional branching and iteration.

% ROWTYPE is used when a query returns an entire row of a table or view.

TYPE RECORD, on the other hand, is used when a query returns column of different tables or views.

Eg. TYPE r_emp is RECORD (sno smp.smpno%type,sname smp sname %type)

e_rec smp %ROWTYPE

Cursor c1 is select smpno,dept from smp;

e_rec c1 %ROWTYPE

Cursor is a named private area in SQL from which information can be accessed. They are required to process each row individually for queries which return multiple rows.

Cursor declares %ROWTYPE as loop index implicitly. It then opens a cursor, gets rows of values from the active set in fields of the record and shuts when all records are processed.

Eg. FOR smp_rec IN C1 LOOP

totalsal=totalsal+smp_recsal;

ENDLOOP;

A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for :

1) Audit data modifications.

2) Log events transparently.

3) Enforce complex business rules.

4) Maintain replica tables

5) Derive column values

6) Implement Complex security authorizations

Error handling part of PL/SQL block is called Exception. They have two types : user_defined and predefined.

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.

It is a procedure of package DBMS_STANDARD that allows issuing of user_defined error messages from database trigger or stored sub-program.

Function is called as a part of an expression.

total:=calculate_sal(b644)

Procedure is called as a statement in PL/SQL.

calculate_bonus(b644);

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.

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

Compilation process includes syntax check, bind and p-code generation processes.

Syntax checking checks the PL SQL codes for compilation errors. When all errors are corrected, a storage address is assigned to the variables that hold data. It is called Binding. P-code is a list of instructions for the PL SQL engine. P-code is stored in the database for named blocks and is used the next time it is executed.

A syntax error can be easily detected by a PL/SQL compiler. For eg, incorrect spelling.

A runtime error is handled with the help of exception-handling section in an PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.

For a COMMIT statement, the following is true:

• Other users can see the data changes made by the transaction.
• The locks acquired by the transaction are released.
• The work done by the transaction becomes permanent.

A ROLLBACK statement gets issued when the transaction ends, and the following is true.

• The work done in a transition is undone as if it was never issued.
• All locks acquired by transaction are released.

It undoes all the work done by the user in a transaction. With SAVEPOINT, only part of transaction can be undone.

A cursor is implicit by default. The user cannot control or process the information in this cursor.

If a query returns multiple rows of data, the program defines an explicit cursor. This allows the application to process each row sequentially as the cursor returns it.

It occurs when a trigger tries to update a row that it is currently using. It is fixed by using views or temporary tables, so database selects one and updates the other.

DECLARE statement is used by PL SQL anonymous blocks such as with stand alone, non-stored procedures. If it is used, it must come first in a stand alone file.

A maximum of 12 triggers can be applied to one table.

SQLCODE returns the value of the number of error for the last encountered error whereas SQLERRM returns the message for the last error.

the %ISOPEN cursor status variable can be used.

Cursor_Already_Open

Invaid_cursor

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.

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

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

It returns the Boolean value TRUE if no rows were processed.

A PL/SQL package can be specified as a file that groups functions, cursors, stored procedures, and variables in one place.

NVL converts NULL to another specified value.

var:=NVL(var2,Hi);

IS NULL and IS NOT NULL can be used to check specifically to see whether the value of a variable is NULL or not.

No, SQL*Plus does not have a PL/SQL Engine embedded in it. Thus, all PL/SQL code is sent directly to database engine. It is much more efficient as each statement is not individually stripped off.

DBMS_ series of packages, such as, DBMS_PIPE, DBMS_DDL, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_SQL, DBMS_TRANSACTION, UTL_FILE.

• A triggering statement or event.
• A restriction
• An action