PL/SQL Interview Question

Topic Introduction:    For Oracle developers here are important questions for beginner to expert level. This part is divided into two parts one is Theory another is practical.

Theory

What is PL/SQL?

PL/SQL is a procedural language extension of SQL used in Oracle databases for writing programmatic code to handle data and perform complex tasks.

 

What is the purpose of using PL/SQL?

PL/SQL is used to overcome the limitations of SQL.

 

What are the most essential characteristics of PL/SQL?

PL/SQL is a block-structured language.

It is portable to all environments that support Oracle.

PL/SQL is integrated with the Oracle data dictionary.

Stored procedures help better the sharing of applications.

 

What is a PL/SQL table? Why is it used?

In PL/SQL, a PL/SQL table is a composite data type that can hold an indexed collection of data elements. It is similar to an array or a list in other programming languages. PL/SQL tables are useful when you need to store and manipulate a dynamic set of data elements without knowing the exact number of elements in advance.

What are the data types available in PL/SQL?

There are two types of datatypes in PL/SQL:

Scalar datatypes Examples are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN, etc.

Composite datatypes Examples are RECORD, TABLE, etc.

What is the basic structure of PL/SQL?

The basic structure of PL/SQL consists of blocks, which can contain three block sections.

PL/SQL block contains 3 sections.

1. The Declaration Section (optional)

2. The Execution Section (mandatory)

3. The Exception handling Section (optional)

What is the FUNCTION in PL/SQL?

A function is a named PL/SQL block that returns a value. It can be stored in the database as a schema object for repeated execution. it is called a part of an expression or is used to provide a parameter value for another subprogram. it can be grouped into PL/SQL packages.

What is the PROCEDURE in PL/SQL?

Procedures are types of subprograms that perform an action. It can be stored in the database as a schema object for reuse.

What is the PACKAGE in PL/SQL?

A package is a schema object which groups logically related PL/SQL types, items, and subprograms. You can also say that it is a group of functions, procedures, variables, and record-type statements. It provides modularity, and due to this facility, it aids application development. It is used to hide information from unauthorized users.

What is the difference between FUNCTION, and PROCEDURE in PL/SQL?

The basic and primary difference is function must return a value but the Procedure can be returned or not.

Function: The primary objective of a PL/SQL function revolves around computing and delivering a singular value, necessitating a defined return type in its specification that aligns with the value it must provide.

Procedure: In contrast to a function, a procedure lacks a designated return type and must not have any value, yet it may contain a return statement solely responsible for terminating its execution and returning control to the caller; a procedure is employed for returning multiple values, making it akin to a function in most other aspects.

What is the exception?

An exception is a PL/SQL error that is raised during program execution, either implicitly by TimesTen or explicitly by your program. Handle an exception by trapping it with a handler or propagating it to the calling environment. 

What are the types of exceptions?

There are two types of exceptions: pre_defined exceptions and user_defined exceptions.

 

How to write a single statement that concatenates the words? Hello? and ? World? and assign it in a variable named Greeting?

Does PL/SQL support the CREATE command?

No. PL/SQL doesn't support the data definition commands like CREATE. 

PL/SQL can only use SELECT, DML(INSERT, UPDATE, DELETE) and TC(COMMIT, ROLLBACK, SAVEPOINT) statements, DDL (CREATE, ALTER, DROP) and DCL(GRANT, REVOKE) cannot be used directly.

Write a unique difference between a function and a stored procedure.

A function must return a value while a stored procedure doesn't return a value.

How is an exception different from an error?

Whenever an Error occurs Exception arises. Error is a bug whereas exception is a warning or error condition.

What is the main reason behind using an index?

Increased performance in searching for records.

What are PL/SQL exceptions? Tell me any three.

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

OTHERS etc.

 How do you declare a user-defined exception?

What are some predefined exceptions in PL/SQL?

What is a trigger in PL/SQL?

What is the maximum number of triggers, you can apply on a single table?

How many types of triggers exist in PL/SQL?

What is the difference between the execution of triggers and stored procedures?

What happens when a trigger is associated with a view?

What is the usage of the WHEN clause in a trigger?

How to disable a trigger name update_salary?

Which command is used to delete a trigger?

what are the two virtual tables available at the time of database trigger execution?

What is the Stored Procedure?

What are the different schema objects that can be created using PL/SQL?

What do you know by PL/SQL Cursors?

What is the difference between the implicit and explicit cursors?

What will you get by the cursor attribute SQL%ROWCOUNT?

What will you get by the cursor attribute SQL%FOUND?

What will you get by the cursor attribute SQL%NOTFOUND?

What do you understand by PL/SQL packages?

What are the two different parts of the PL/SQL packages?

Which command is used to delete a package?

How to execute a stored procedure?

What are the advantages of stored procedure?

What are the cursor attributes used in PL/SQL?

What is the difference between a syntax error and a runtime error?

Explain the Commit statement.

Explain the Rollback statement?

Explain the SAVEPOINT statement.

What is a mutating table error?

A mutating table error happens when a row-level trigger tries to access the same table that triggered it, either directly or indirectly. 

What is consistency?

What is a cursor and why is it required?

A PL/SQL cursor is a named control structure that allows you to process a set of rows returned by an SQL query one at a time.

SELECT statement should return only one row at a time in previous PL/SQL programs. This is too restrictive in many applications. We use the idea of Cursor to handle the above problem.

How many types of cursors are available in PL/SQL?

There are two types of cursors: Implicit Cursors. Explicit Cursors.

Differentiate between % ROWTYPE and TYPE RECORD.

%RowType is used to fetch the entire rows of the table whereas the Type-RECORD is used to fetch the entire columns of the table of the views or variables.

Explain the uses of a cursor.

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

Show the code of a cursor for loop.

Others

Explain the uses of database triggers.

What are the two types of exceptions?

Show some predefined exceptions.

Explain Raise_application_error.

Show how functions and procedures are called in a PL SQL block.

Explain two virtual tables available at the time of database trigger execution.

What are the rules to be applied to NULLs whilst doing comparisons?

How is a process of PL SQL compiled?

Differentiate between Syntax and runtime errors.

Explain Commit, Rollback, and Savepoint.

Define Implicit and Explicit Cursors.

Explain the mutating table error.

When is a declare statement required?

The declaration section is required if any variables are to be used in a PL/SQL block.

How many triggers can be applied to a table?

What is the importance of SQLCODE and SQLERRM?

If a cursor is open, how can we find it in a PL SQL Block?

Show the two PL/SQL cursor exceptions.

What operators deal with NULL?

Does SQL*Plus also have a PL/SQL Engine?

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

What packages are available to PL SQL developers?

Explain 3 basic parts of a trigger.

What are character functions?

Explain TTITLE and BTITLE.

Show the cursor attributes of PL/SQL.

How would you reference column values BEFORE and AFTER you have inserted and deleted 

What are the uses of SYSDATE and USER keywords?

How does ROWID help in running a query faster?

What are database links used for?

What does fetching a cursor do?

What does closing a cursor do?

Explain the uses of Control File.

Explain Consistency

Differ between Anonymous blocks and subprograms.

Differ between DECODE and CASE.

Explain autonomous transactions.

Differentiate between SGA and PGA.

What is the location of Pre_defined_functions?

Explain polymorphism in PL SQL.

What are the uses of MERGE?

Can 2 queries be executed simultaneously in a Distributed Database System?

Explain Raise_application_error.

What is out parameter used for even though the return statement can also be used in PL/SQL?

How would you convert the date into Julian date format?

Give a short explanation of the SPOOL

Mention what PL/SQL package consists of?

Mention what are the benefits of PL/SQL packages?

Mention different methods to trace the PL/SQL code?

Mention what does the hierarchical profiler do?

Mention what does PLV msg allow you to do?

Mention what is the PLV (PL/Vision) package offers?

Mention what is the use of PLVprs and PLVprsps?

Explain how you can copy a file to file content and file to PL/SQL table in advance PL/SQL?

Explain how exception handling is done in advance PL/SQL?

Mention what problem one might face while writing log information to a database table in PL/SQL? Mention what is the function that is used to transfer a PL/SQL table log to a database table?

When do you have to use a default “rollback to” savepoint of PLVlog?

Why PLVtab is considered the easiest way to access the PL/SQL table?

Mention what does PLVtab enable you to do when you show the contents of PL/SQL tables?

Explain how can you save or place your msg in a table?

Mention what is the use of the function “module procedure” in PL/SQL?

Mention what PLVcmt and PLVrb do in PL/SQL?

Practical

Write a PL/SQL script to display the following series of numbers: 99,96,93……9,6,3?

Example of a Sample Procedure In PLSQL

Example of a Sample Function In PLSQL

Example of a Sample FOR LOOP In PLSQL

Example of a Sample Trigger In PLSQL