PL/SQL Interview Questions and Answers

PL/SQL is a procedural language which has interactive SQL language created by Oracle in the 1990s. It is a completely portable, high performance transaction processing language.SQL is code is processed one statement block at at time, while PLSQL programming code is executed as a single program at one time

Numeric or Arithmetic expressions

Date expressions

Boolean expressions

String Expressions

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

Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

Cursors are two types:

Implicit Cursors: It is used for queries

Explicit Cursors: It is used for declared (also called user defined cursors)

A schema is a user-owned collection of schema objects, or logical data structures. These schema objects types are as follows:

• Clusters
• Database links
• Database triggers
• Dimensions
• External procedure libraries
• Indexes and index types
• Java classes, Java resources, and Java sources
• Materialized views and materialized view logs
• Object tables, object types, and object views
• Operators
• Sequences
• Stored functions, procedures, and packages
• Synonyms
• Views
• Tables and index-organized tables

Among other objects which are not contained in a schema are:

• Directories
• Contexts
• Profiles
• Roles
• Table spaces
• Users
• Rollback segments

Cluster is a optional method of storing table data. It is group table that shares the same data blocks because they share common columns and are often used together.

Hash clusters group table data in manner similar to regular index clusters. Hash clusters are a better than using indexed table or index cluster.

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

 TYPE_ rec RECORD is to be used whenever query returns columns of different table or views and variables.

Oracle allows to procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called Database trigger.

It is used for:

• Audit data modifications
• Invalid transactions
• Provide transparent event log
• Gather statics in tables
• Disrupted database
• Log events transparently
• Enforce complex business rules
• Maintain replica tables
• Derive column values
• Implement Complex security authorizations

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.

Raise_application_error 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 (‘b733’)

Procedure is called as a statement in PL/SQL.

calculate_bonus (‘b733’);

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.

Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined exceptions are.

• CURSOR_ALREADY_OPEN
• DUP_VAL_ON_INDEX
• NO_DATA_FOUND
• TOO_MANY_ROWS
• INVALID_CURSOR
• INVALID_NUMBER
• LOGON_DENIED
• NOT_LOGGED_ON
• PROGRAM-ERROR
• STORAGE_ERROR
• TIMEOUT_ON_RESOURCE
• VALUE_ERROR
• ZERO_DIVIDE

OTHERS

The decode operator has been around since the earliest days of Oracle, the case operator was introduced in Oracle 8.1.6.DECODE and CASE statements are very similar, but CASE is extended version of DECODE. DECODE does not allow Decision making statements in its place.CASE is directly used in PL/SQL, but DECODE is used in PL/SQL through SQL only.

Syntax error: It can be easily detected by a PL/SQL compiler

Runtime error: It is handled with the help of exception handling section in a PL/SQL block.

SQLCODE is the returns the value of the number of error for the last encountered error.

SQLERRM is the returns the message for the last error.

Declaration section

Execution section

Exception section

 This error 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 is an optional sectional of a PL/SQL block. 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 standalone file.

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

Some of the predefined exceptions are

• NO_DATA_FOUND: Single row SELECT statement where no data is returned.
• TOO_MANY_ROWS: Single row SELECT statement where more than one rows are returned.
• INVALID_CURSOR: Illegal cursor operation occurred.
• ZERO_DIVIDE: Attempted to divide by zero.

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

Placeholders are temporary storage area. PL/SQL Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block

Cursor C1 is

Select empno,

ename from emp;

Begin

open C1; loop

Fetch C1 into

eno.ename;

Exit When

C1 %notfound;–

commit;

end loop;

end; The cursor having query as SELECT …. FOR UPDATE gets closed after COMMIT/ROLLBACK.

The cursor having query as SELECT…. does not get closed even after COMMIT/ROLLBACK.

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.

A FUNCTION always returns a value using the return statement.

A PROCEDURE may return one or more values through parameters or may not return at all.

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

Overloading modules is nothing more than a mechanism that allows the coder to reuse the same name for different programs that are within the same scope. Overloading is probably one of my favourite mechanisms to share and increase usability within cod

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

The [OR REPLACE] argument grants the developer to reinvent an existing trigger. This means that with [OR REPLACE] it is possible to edit the specifics of a trigger without removing it.

Block-structured language

Integration with the Oracle data dictionary

Stored procedures help better sharing of application

Portable to all environments that support Oracle

Fetching a cursor reads Result Set row by row.

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.

By using,ALTER INDEX REBUILD

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

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.

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

COMMIT: This statements finalizes to end your transaction and sets all changes to permanent. The COMMIT statement deletes all the save points prior to the transaction and releases transaction locks.

ROLLBACK: This statement undoes the changes that the transaction has made. This is practically the opposite of the COMMIT Statement. Also, any locks made due to the transaction are released.

In conjunction, the SAVEPOINT statement is also used to set a restoration point when the ROLLBACK Statement is used. This limits the bounds of the ROLLBACK Statement by only reverting to the SAVEPOINT set point.

DBMS_ series of packages are

• DBMS_PIPE
• DBMS_DDL
• DBMS_LOCK
• DBMS_ALERT
• DBMS_OUTPUT
• DBMS_JOB
• DBMS_UTILITY
• DBMS_SQL
• DBMS_TRANSACTION
• UTL_FILE

ORA-03113 is a broken connection between Client and Server process, a blanket end-of-file on a communication channel error.

• ORA-01012 not logged on
• ORA-03113: end-of-file on communication channel
• ORA-01041 -hostdef extension does not exist
• ORA-03114 –not connected to Oracle
• ORA-12154-TNS listener error
• ORA-00600 and ORA-01722:Invalid number error

• Connection between client and server is broken
• Server machine Crash issue
• Processes kill on server side
• Incorrect connection handling
• Network issue
• Connection abort

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.

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

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.

%ISOPEN: This attribute is used to determine whether a cursor is in the open state

%FOUND: This attribute is used to determine whether a cursor contains rows after the execution of a FETCH statement.

%NOTFOUND: This attribute is the logical opposite of the %FOUND attribute.

%ROWCOUNT: This attribute is used to determine the number of rows that have been fetched since a cursor was opened.

A PL/SQL record is type of data structure that contains a set of data or distinct information values that can be referenced with each other as fields. They are useful for classifying and retrieving data with common attributes or properties. With this, it is much easier to identify similar data by tracing the attributes.

PL/SQL records are:

• Table based records
• Programmer based records
• Cursor based records

Both TTITLE and BTITLE command that control report heads and footers.

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.

DBA_DATA_FILES

The DROP PACKAGE command

Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

A Bitmap index

A primary key and a foreign key

While you can natively compile any PL/SQL code, it is not always the best thing to do. Native compilation is suited better for those computational intensive procedures, not code that just runs SQL statements.

Alter database backup control file to trace.

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.

WRAP command is used to encrypt a PL/SQL application