Most Frequently asked Oracle database SQL questions and answers

Are you preparing for PL/SQL Interviews? If yes, then we bring you here a list of commonly asked but important PL/SQL interview questions.

PL (Procedural language) is an extension to SQL (Structured Query Language) where a developer can write complex database interactions using procedures, control structures like branching and iteration, modules and functions. It has tight integration with SQL and supports both dynamic and static SQL.

This article covers a list of all the most important and commonly asked PL/SQL interview questions and answers, which will help you to crack any interview.

Most Popular PL/SQL Interview Questions and Answers

Q1 : What are the most important characteristics of PL/SQL?

Ans: A list of some notable characteristics:

    • 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 sharing of application.

Q2 : What are the PL/SQL data types?

Ans: PL/SQL data types can be broken down to the following categories.

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

Composite datatypes: Example are RECORD, TYPE, TABLE

Collection: Associative array, Nested table, VARRAY

Special types: %TYPE, %ROWTYPE

Reference datatype: REF Cursor

Q3 : What is the basic structure of a PL/SQL block?

Ans: PL/SQL uses block structure that consists of SQL and PL/SQL statements.

PL/SQL block contains following 3 sections called as parts of PL/SQL block.

      1. The Declaration Section (optional)
      2. The Execution Section (mandatory)
      3. The Exception handling Section (optional)

Q4 : What is an Anonymous block?

Ans: Anonymous Block is a block of instructions in PL/SQL that is not saved under a name as an object in database schema. It is also not compiled and saved in server storage. So, it needs to be parsed and executed each time it is run. However, this simple form of program can use variables, can have flow of control logic, can return query results into variables and can prompt the user for input using the SQL*Plus ‘&’ feature as any stored procedure.

Q5 : Is there a limit on the size of a PL/SQL block?

Ans: Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure.

SELECT * FROM DBA_OBJECT_SIZE WHERE Name = 'procedure_name';

Q6 : Variable and constant declaration in PL/SQL?

Ans:

Variable declaration-

Variable_name Datatype [NOT NULL][:= or DEFAULT] Expression;

Constant declaration-

Constant_name CONSTANT Datatype [NOT NULL][:= or DEFAULT] Expression;

Q7 : What are bind variables in PL/SQL?

Ans: Suppose you want to display the variables you use in your PL/SQL subprograms in SQL*Plus or use the same variables in multiple subprograms. If you declare a variable in a PL/SQL subprogram, you cannot display that variable in SQL*Plus. Use a bind variable in PL/SQL to access the variable from SQL*Plus.

Bind variables are variables you create in SQL*Plus and then reference in PL/SQL. If you create a bind variable in SQL*Plus, you can use the variable as if you have declared a variable in your PL/SQL subprogram and then access the variable from SQL*Plus. You can use bind variables for such things like storing return codes or debugging your PL/SQL subprograms.

Because bind variables are recognized by SQL*Plus, you can display their values in SQL*Plus or reference them in other PL/SQL subprograms that you run in SQL*Plus.

Syntax: VARIABLE Datatype

e.g.

VARIABLE a NUMBER

VARIABLE b VARCHAR2(30)

 

You can also use the abbreviation VAR instead of VARIABLE

Q8 : How to list all the bind variables created in a session?

Ans: Type VARIABLE without any arguments.

VARIABLE

variable   a

datatype   NUMBER

variable   b

datatype   VARCHAR2(30)

Q9 : How to display the bind variable value in SQL*PLUS?

Ans: To display the value of a bind variable in SQL *plus, we use the SQL * plus PRINT command.

Syntax: PRINT

e.g. PRINT a

 

If PRINT command is used without any argument then it prints the values of all the bind variables in that session.

Q10 : What is PL/SQL Record type?

Ans: Record types are the programming structures that group a set of variable types. Variable types are grouped together and managed as a unit. PL/SQL record is a group of related data items stored in individual fields each with its own attribute name and data type.

Q11 : What is a Collection in PL/SQL?

Ans: A collection is an ordered group of elements, all of the same type and allows programmatic access to its elements through an index. Each element has a unique subscript that determines its position in the collection.

PL/SQL offers three kinds of collections: Associative Arrays, Nested Tables, and Varrays (short for variable-size arrays). Nested tables extend the functionality of associative arrays (formerly called PL/SQL tables or index-by tables).

Q12: What is Associative Array?

Ans: A PL/SQL associative array is a collection type having following properties.

    • Is empty (but not null) until you populate it
    • Can hold an unspecified number of elements. This means Associative arrays have no upper bounds, allowing them to constantly extend.
    • Can not be manipulated with DML statements
    • As the name implies, the collection is indexed using BINARY_INTEGER/ PLS_INTEGER values, which do not need to be consecutive.
    • The collection is automatically extended by assigning values to an element using an index value that does not currently exist.
TYPE typ_name IS TABLE OF VARCHAR2(64) INDEX BY PLS_INTEGER;

Associative arrays were known as PL/SQL tables in Oracle 7, and index-by tables in Oracle 8 and 8i. Their names were changed to associative arrays in Oracle 9i release 1.

Q13: What is Nested Table?

Ans: Nested table collections are an extension of the associative arrays. The main difference between the two is that nested tables can be stored in a database column but associative arrays cannot.

    • Is empty (but not null) until you populate it
    • During creation the collection is dense, having consecutive subscripts for the elements. Once created elements can be deleted using the DELETE method to make the collection sparse.
    • Some DML operations are possible on nested tables when they are stored in the database
TYPE typ_name IS TABLE OF VARCHAR2(64);

Q14: What is VARRAY?

Ans: A VARRAY is similar to a nested table except you must specify an upper bound in the declaration.

    • Is empty (but not null) until you populate it
    • Can hold a fixed number of elements
    • Individual elements cannot be deleted so they remain dense
TYPE your_varray_typ IS VARRAY(5) OF VARCHAR2(64);

Q15 : Difference between different types of Collections?

Ans: Following are the features of Oracle collection types.

Varray Nested Table Associative Array
Subscript is always sequential number starting with 1.
Subscript is always sequential number starting with 1.
Subscript should be numbers or unique string. Subscript/ index can start from any whole number.
Size is fixed. Size is defined at the time of declaration.
Size can be dynamically extended.
Size can be dynamically extended.
They are densely populated arrays. Hence, we cannot apply DELETE procedure to a varray.
They are initially defined as densely populated arrays but may become sparsely populated as records are deleted.
They are sparsely populated arrays, as index doesn’t require being sequential.
They may be stored in permanent tables and accessed by SQL.
They may be stored in permanent tables and accessed by SQL.
Associative arrays are intended for temporary data storage rather than storing persistent data.
Require initialization. Initialization may be - Static Through Constructor
Require initialization. Initialization may be – Static Through Constructor
Don’t require initialization. Have no constructor syntax.
EXTEND method is used to allocate space.
EXTEND method is used to allocate space.
EXTEND method can’t be operated.
Multiset operators don’t work.
Multiset operators work only with Nested Table.
Multiset operators don’t work.

Q16 : What are the different Collection methods in Oracle?

Ans: Following are the collection methods available in Oracle PL/SQL.

If you have a Nested Table, “YourNestedTab“, then you can use the following collection methods.

FIRST       : YourNestedTab.FIRST
LAST : YourNestedTab.LAST
COUNT : YourNestedTab.COUNT
NEXT : YourNestedTab.NEXT(i)
DELETE(m) : YourNestedTab.DELETE(m)
DELETE(m, n): YourNestedTab.DELETE(m, n)
EXTEND : YourNestedTab.EXTEND
EXTEND(m) : YourNestedTab.EXTEND(m)
EXISTS : YourNestedTab.EXISTS(i)
TRIM : YourNestedTab.TRIM
TRIM(m) : YourNestedTab.TRIM(m)

where m, n, i denotes the index of the element in the Collection object.

Q17 : How to traverse a Sparse Collection?

Ans: Only Associative array and Nested tables can be sparse. You can traverse a sparse collection using collection methods. Watch below scenarios.

DECLARE
  TYPE emp_tab IS TABLE OF VARCHAR2(20) INDEX By BINARY_INTEGER;
  v_emp_tab emp_tab;
  v_index VARCHAR2(20);
BEGIN
  SELECT ename BULK COLLECT INTO v_emp_tab FROM emp;
  v_index := v_emp_tab.FIRST;
  WHILE v_index IS NOT NULL
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp_tab(v_index).Empno);
    v_index := v_emp_tab.NEXT(v_index);
  END LOOP;
END;

OR

DECLARE
  TYPE emp_tab IS TABLE OF VARCHAR2(20) INDEX By BINARY_INTEGER;
  v_emp_tab emp_tab;
  v_index VARCHAR2(20);
BEGIN
  SELECT ename BULK COLLECT INTO v_emp_tab FROM emp;
  v_index := v_emp_tab.FIRST;
  WHILE v_emp_tab.EXISTS(v_index)
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_emp_tab(v_index).Empno);
    v_index := v_emp_tab.NEXT(v_index);
  END LOOP;
END;

Q18 : What is the difference between NULL Collection and Empty Collection in PL/SQL?

Ans :

NULL collection: A collection which is declared but not initialized is a null collection.

Empty collection: A collection which is declared and initialized is known as an Empty collection.

NOTE

Associative arrays are always by default initialized. So, NULL Associative arrays doesn’t exist.

Nested table and Varrays have to be initialized, else they remain NULL.

Q19 : How to test NULL and Empty for collection in Oracle?

Ans: To use a collection in a PL/SQL program, you declare the type first. Declare a collection variable of that type. Based on the type of collection you defined, the collection variable will either be empty or NULL.

    • Associative arrays are always by default initialized. So, NULL Associative arrays doesn’t exist.
    • Nested table and Varrays have to be initialized, else they remain NULL.

Associative Array NULL/Empty Test

DECLARE
TYPE tab_asarray IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_tab_asarray tab_asarray;

BEGIN
--Check collection if empty or null or populated
IF v_tab_asarray IS NULL THEN
DBMS_OUTPUT.put_line('Associative Array is NULL"");
ELSIF v_tab_asarray IS EMPTY THEN
DBMS_OUTPUT.put_line('Associative Array is EMPTY"");
ELSE
DBMS_OUTPUT.put_line('Associative Array contains data"");
END IF;

END;

Nested table NULL/Empty Test

DECLARE
TYPE tab_nested IS TABLE OF NUMBER;
v_tab_nested tab_nested;

BEGIN
--Check collection if empty or null or populated
IF v_tab_nested IS NULL THEN
DBMS_OUTPUT.put_line('Nested table is NULL"");
ELSIF v_tab_nested IS EMPTY THEN
DBMS_OUTPUT.put_line('Nested table is EMPTY"");
ELSE
DBMS_OUTPUT.put_line('Nested table contains data"");
END IF;

--Initialize Nested table by calling collection constructor
v_tab_nested := tab_nested();

--Check collection if empty or null or populated
IF v_tab_nested IS NULL THEN
DBMS_OUTPUT.put_line('Nested table is NULL"");
ELSIF v_tab_nested IS EMPTY THEN
DBMS_OUTPUT.put_line('Nested table is EMPTY"");
ELSE
DBMS_OUTPUT.put_line('Nested table contains data"");
END IF;

END;

Varray NULL/Empty Test

DECLARE
TYPE tab_varray IS VARRAY(5) OF NUMBER;
v_tab_varray tab_varray;

BEGIN
--Check collection if empty or null or populated
IF tab_varray IS NULL THEN
DBMS_OUTPUT.put_line('Varray is NULL"");
ELSIF tab_varray IS EMPTY THEN
DBMS_OUTPUT.put_line('Varray is EMPTY"");
ELSE
DBMS_OUTPUT.put_line('Varray contains data"");
END IF;

--Initialize Nested table by calling collection constructor
tab_varray := tab_varray();

--Check collection if empty or null or populated
IF tab_varray IS NULL THEN
DBMS_OUTPUT.put_line('Varray is NULL"");
ELSIF tab_varray IS EMPTY THEN
DBMS_OUTPUT.put_line('Varray is EMPTY"");
ELSE
DBMS_OUTPUT.put_line('Varray contains data"");
END IF;

END;

Q20 : What is a Cursor? How many types of Cursor are there?

Ans: To process SQL statements, Oracle creates an area of memory known as Context Area or Process Global Area. Cursor is a Handle or a Pointer to the Context Area. Using a Cursor, PL/SQL program can control the Context Area, as the SQL statements are being processed.

Cursor allows fetching and processing of rows, returned by a SELECT statement, one row at a time. A Cursor can be named, so that it can be referenced by the PL/SQL programmer dynamically at run time.

We can classify Cursors in the following types:

Implicit cursors – It is a Cursor that is automatically declared by Oracle every time an SQL statement is executed. The programmer can’t control or process the information in an Implicit Cursor.

Explicit cursors – It is a Cursor that is defined by the programmer within the program for any query that returns more than one row of data. This Cursor is declared within the PL/SQL block, and allows sequential process of each row of the returned data from database.

Dynamic Cursors/Ref Cursors – Ref Cursors are used for the runtime modification of the SELECT query.

Q21 : What is SGA?

Ans : A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance’s SGA is shared among the users. Consequently, the SGA is sometimes called the Shared Global Area.

The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance’s SGA, and several processes write to the SGA during execution of Oracle.

Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.

The SGA contains the following data structures:

Database buffer cache

Redo log buffer

Shared pool

Java pool

Large pool (optional)

Streams pool

Data dictionary cache

Other miscellaneous information

Q22 : What is PGA?

Ans: A program global area (PGA) is a memory region that contains data and control information for a server process. It is a nonshared memory created by Oracle when a server process is started. Access to it is exclusive to that server process and is read and written only by Oracle code acting on behalf of it. The total PGA memory allocated by each server process attached to an Oracle instance is also referred to as the aggregated PGA memory allocated by the instance.

The PGA memory can be classified as follows.

Private SQL Area

Session Memory

SQL Work Areas

A cursor is a name or handle to a specific private SQL area. You can think of a cursor as a pointer on the client side and as a state on the server side. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.

Oracle Database processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers)much the same way it processes individual SQL statements. Oracle Database allocates a shared area to hold the parsed, compiled form of a program unit. Oracle database allocates a private area to hold values specific to the session that runs the program unit, including local, global, and package variables (also known as package instantiation) and buffers for executing SQL. If more than one user runs the same program unit, then a single, shared area is used by all users, while each user maintains a separate copy of his or her private SQL area, holding values specific to his or her session.

Q23 : What are FOR LOOP Cursors?

Ans: Cursors with FOR LOOP are highly automated. We need only to declare the Cursor. The Opening, Fetching and Closing is done by FOR loop.

Fetching is done by FOR loop through their Index. So, we needn’t declare any %TYPE or %ROWTYPE for explicit fetching. The indexes in FOR loops are implicitly declared by the system.

Q24 : What is Dynamic Cursor or REF Cursor?

Ans: Cursor variables are evaluated at run time instead of compile time and can be opened for multiple SELECT statements in the same block.

Q25 : When do you use Ref Cursors?

Ans: We base a query on a ref cursor when you want to:

      1. More easily administer SQL
      2. Avoid the use of lexical parameters in your reports
      3. Share data sources with other applications, such as Form Builder
      4. Increase control and security
      5. Encapsulate logic within a subprogram

Q26 : Strong Cursor and Weak Cursor?

Ans: When return type is included in the REF cursor declaration then it is called strong cursor or static structure type. Strong ref cursors support different types of select statements but all of same structure, but not necessary that the table should be same.

Weak cursors allow us to any type of select statement irrespective of data structure i.e. any table.

Q27 : What is Context Switch?

Ans: Almost every program PL/SQL developers write, includes both PL/SQL and SQL statements. PL/SQL statements are run by the PL/SQL statement executor; SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine. This transfer of control is called a Context Switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.

Q28 : What is BULK COLLECT?

Ans: The bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine.

Use the BULK COLLECT clause to fetch multiple rows into one or more collections with a single context switch.

Q29 : What is FORALL?

Ans: Use the FORALL statement when you need to execute the same DML statement repeatedly for different bind variable values.

Q30 : What are the attributes of BULK COLLECT?

Ans: %BULK_ROWCOUNT, %BULK_EXCEPTION

Q31 : When do we get INVALID CURSOR error?

Ans: There are basically 3 scenarios where we get invalid cursor error.

    • Fetch statement encountered before cursor is opened.
    • Closing a cursor more than once.
    • Accessing cursor using any cursor attribute after the cursor is closed.
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments