Top 50 PL/SQL Interview Questions & Answers

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:

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

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

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.

INITCAP, UPPER, SUBSTR, LOWER and LENGTH are all character functions. Group functions give results based on groups of rows, as opposed to individual rows. They are MAX, MIN, AVG, COUNT and SUM.

TTITLE and BTITLE commands that control report headers and footers.

%ISOPEN : Checks if the cursor is open or not

%ROWCOUNT : The number of rows that are updated, deleted or fetched.

%FOUND : Checks if the cursor has fetched any row. It is true if rows are fetched

%NOT FOUND : Checks if the cursor has fetched any row. It is True if rows are not fetched.

Intersect is the product of two tables and it lists only matching rows.

Sequences are used to generate sequence numbers without an overhead of locking. Its drawback is that the sequence number is lost if the transaction is rolled back.

Using the keyword “new.column name”, the triggers can reference column values by new collection. By using the keyword “old.column name”, they can reference column vaues by old collection.

SYSDATE refers to the current server system date. It is a pseudo column. USER is also a pseudo column but refers to current user logged onto the session. They are used to monitor changes happening in the table.

ROWID is the logical address of a row, it is not a physical column. It composes of data block number, file number and row number in the data block. Thus, I/O time gets minimized retrieving the row, and results in a faster query.

Database links are created in order to form communication between various databases, or different environments like test, development and production. The database links are read-only to access other information as well.

Fetching a cursor reads Result Set row by row.

Closing a cursor clears the private SQL area as well as de-allocates memory

It is a binary file. It records the structure of the database. It includes locations of several log files, names and timestamps. They can be stored in different locations to help in retrieval of information if one file gets corrupted.

Consistency shows that data will not be reflected to other users until the data is commit, so that consistency is maintained.

Anonymous blocks are unnamed blocks that are not stored anywhere whilst sub-programs are compiled and stored in database. They are compiled at runtime.

DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.

select decode(totalsal=12000,’high’,10000,’medium’) as decode_tesr from smp where smpno in (10,12,14,16);

This statement returns an error.

CASE is directly used in PL SQL, but DECODE is used in PL SQL through SQL only.

An autonomous transaction is an independent transaction of the main or parent transaction. It is not nested if it is started by another transaction.

There are several situations to use autonomous transactions like event logging and auditing.

SGA stands for System Global Area whereas PGA stands for Program or Process Global Area. PGA is only allocated 10% RAM size, but SGA is given 40% RAM size.

They are stored in the standard package called “Functions, Procedures and Packages”

Polymorphism is a feature of OOP. It is the ability to create a variable, an object or function with multiple forms. PL/SQL supports Polymorphism in the form of program unit overloading inside a member function or package..Unambiguous logic must be avoided whilst overloading is being done.

MERGE is used to combine multiple DML statements into one.

Syntax : merge into tablename

using(query)

on(join condition)

when not matched then

[insert/update/delete] command

when matched then

[insert/update/delete] command

Yes, they can be executed simultaneously. One query is always independent of the second query in a distributed database system based on the 2 phase commit.

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

Out parameters allows more than one value in the calling program. Out parameter is not recommended in functions. Procedures can be used instead of functions if multiple values are required. Thus, these procedures are used to execute Out parameters.

We can use the J format string :

SQL > select to_char(to_date(’29-Mar-2013′,’dd-mon-yyyy’),’J’) as julian from dual;

JULIAN

Spool command can print the output of sql statements in a file.

spool/tmp/sql_outtxt

select smp_name, smp_id from smp where dept=’accounts’;

spool off;