• No products in the cart.

PL/SQL Interview Questions and Answers Updated 2022

1. What is PL/SQL?

PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

2. Compare SQL & PL/SQL?

CriteriaSQLPL/SQL
What it isSingle query or command executionFull programming language
What it comprisesThe data source for reports, web pagesApplication language to build, format, and display reports, web pages
CharacteristicDeclarative in natureProcedural in nature
Used forManipulating dataCreating applications

3. What are local and global variables and their differences?

Local variables are the variables that can be used or exist inside the function. They are not known to the other functions and those variables cannot be referred to or used. Variables can be created whenever that function is called.

Global variables are the variables that can be used or exist throughout the program. The same variable declared in global cannot be used in functions. Global variables cannot be created whenever that function is called.

4. What is the difference between FUNCTION, PROCEDURE AND PACKAGE in PL/SQL?

Function: The main purpose of a PL/SQL function is generally to compute and return a single value. A function has a return type in its specification and must return a value specified in that type.

Procedure: A procedure does not have a return type and should not return any value but it can have a return statement that simply stops its execution and returns to the caller. A procedure is used to return multiple values otherwise it is generally similar to a function.

Package: 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, due to this facility it aids application development. It is used to hide information from unauthorized users.

5. Advantages and Disadvantages of Stored Procedure?

The stored procedure can be used as modular programming – means create once, store and call for several times whenever required. This supports faster execution instead of executing multiple queries. This reduces network traffic and provides better security to the data.

The disadvantage is that it can be executed only in the Database and utilizes more memory in the database server.

6. Define Implicit and Explicit Cursors.

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.

7. Show the cursor attributes of PL/SQL?

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

8. What is PL/SQL?

PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.

9. Explain the uses of database trigger?

A PL/SQL program unit associated with a particular database table is called a database trigger. It is used for:

  • Audit data modifications.
  • Log events transparently.
  • Enforce complex business rules.
  • Maintain replica tables
  • Derive column values
  • Implement Complex security authorizations

10. What is the basic structure of PL/SQL?

PL/SQL uses BLOCK structure as its basic structure. Each PL/SQL program consists of SQL and PL/SQL statements which form a PL/SQL block.

PL/SQL block contains 3 sections.

  • The Declaration Section (optional)
  • The Execution Section (mandatory)
  • The Exception handling Section (optional)

11. Mention what PL/SQL package consists of?

A PL/SQL package consists of

  • PL/SQL table and record TYPE statements
  • Procedures and Functions
  • Cursors
  • Variables ( tables, scalars, records, etc.) and constants
  • Exception names and pragmas for relating an error number with an exception
  • Cursors

12. What are the various types of parameter modes in a procedure ?

IN, OUT, AND INOUT.

13. What are the datatypes available in PL/SQL?

There are two types of data types in PL/SQL:

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

Composite datatypes Example is RECORD, TABLE, etc.

14. Differentiate between Syntax and runtime errors?

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 the exception-handling section in a PL/SQL block. For eg, SELECT INTO statement, which does not return any rows.

15. What packages are available to PL SQL developers?

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.

16. What is the purpose of the optional argument [OR REPLACE] in a CREATE TRIGGER command?

The optional argument [OR REPLACE] in a CREATE TRIGGER command re-creates an existing trigger. Using this option allows changing the definition of an existing trigger without having to delete it first.

17. How many types of triggers exist in PL/SQL?

There are 12 types of triggers in PL/SQL that contain the combination of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE, and ALL keywords.

  • BEFORE ALL ROW INSERT
  • AFTER ALL ROW INSERT
  • BEFORE INSERT
  • AFTER INSERT etc.

18. How do you declare a user-defined exception?

User-defined exceptions are declared under the DECLARE section, with the keyword EXCEPTION. 

Syntax −

<exception_name> EXCEPTION;

19. How do you refer to the types, objects and subprograms declared within a package?

The types, objects, and subprograms declared within a package are referred to using the dot notation as −

package_name.type_name

package_name.object_name

package_name.subprogram_name

20. What is consistency?

Consistency simply means that each user sees a consistent view of the data.

Consider an example: there are two users A and B. A transfers money to B’s account. Here the changes are updated in A’s account (debit) but until it will be updated to B’s account (credit), till then other users can’t see the debit of A’s account. After the debit of A and credit of B, one can see the updates. 

© ZxAcademy - 2022. All rights reserved.