PL/SQL Interview Questions

Question 1

What is PL/SQL

Answer 1

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

Question 2

Difference between %ROWTYPE and TYPE Record

Answer 2

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

TYPE RECORD is used when a query returns column or different tables or views

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

e_rec smp %ROWTYPE

Cursor c1 is select smpno,dept from smp;

e_rec c1 %ROWTYPE

Question 3

Explain use of a cursor

Answer 3

Cursor is a named private area in SQL from which information can be accessed. When multiple records exist they require is record to be processed individually

Question 4

Cursor code example for loop

Answer 4

FOR smp_rec IN C1 LOOP
Totalsal:=totalsal + 100;
ENDLOOP;

Question 5

Explain the use of Database Triggers

Answer 5

A PL/SQL program unit associated with a particular DB table. 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

Question 6

What are two types of Exceptions

Answer 6

Error handle part of PL/SQL Block

1. user_defined
2. Predefined

Question 7

Show some predefined exceptions

Answer 7

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

Question 8

Explain Raise_application_error

Answer 8

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