PL/SQL Interview Questions And Answers

Jump To Right Section Show

On this article, we’ll focus on a lot of widespread however essential PL/SQL questions that you could be be requested in the course of the SQL Server database administrator interview. I counsel you to examine them out and prepare for the interview.

PL/SQL is a sophisticated model of Structured Question Language (SQL). There are given a prime checklist of PL/SQL interview questions with solutions.

1) What’s PL/SQL?

PL/SQL stands for procedural language extension to Structured Question Language (SQL). It helps procedural options of programming language and SQL. It was developed by Oracle Company within the early 90’s to reinforce the capabilities of SQL.

2) What’s the goal of utilizing PL/SQL?

PL/SQL is an extension of Structured Question Language (SQL). Whereas SQL is non-procedural, PL/SQL is a procedural language designed by Oracle. It’s invented to beat the restrictions of SQL.

3) What are a very powerful traits of PL/SQL?

An inventory of some notable traits:

PL/SQL is a block-structured language.
It’s transportable to all environments that assist Oracle.
PL/SQL is built-in with the Oracle information dictionary.
Saved procedures assist higher sharing of purposes.

4) What’s PL/SQL desk? Why it’s used?

Objects of sort tables are referred to as PL/SQL tables which can be modeled as database tables. We will additionally say that PL/SQL tables are a strategy to offering arrays. Arrays are like short-term tables in reminiscence which can be processed in a short time. PL/SQL tables are used to maneuver bulk information. They simplify shifting collections of information.

5) What are the datatypes out there in PL/SQL?

There are two varieties of datatypes in PL/SQL:

Scalar datatypes Instance is NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN, and many others.
Composite datatypes Instance is RECORD, TABLE, and many others.

6) What’s the fundamental construction of PL/SQL?

PL/SQL makes use of BLOCK construction as its fundamental construction. Every PL/SQL program consists of SQL and PL/SQL statements which kind a PL/SQL block.

PL/SQL block accommodates 3 sections.

The Declaration Part (elective)
The Execution Part (necessary)
The Exception dealing with Part (elective)

7) What’s the distinction between FUNCTION, PROCEDURE, AND PACKAGE in PL/SQL?

Perform: The principle goal of a PL/SQL perform is mostly to compute and return a single worth. A perform has a return sort in its specification and should return a price laid out in that sort.

Process: A process doesn’t have a return sort and shouldn’t return any worth however it might have a return assertion that merely stops its execution and returns to the caller. A process is used to return a number of values in any other case it’s usually much like a perform.

Bundle: A bundle is a schema object which teams logically associated PL/SQL sorts, objects, and subprograms. You too can say that it’s a group of capabilities, procedures, variables, and record-type statements. It offers modularity, attributable to this facility it aids software growth. It’s used to cover info from unauthorized customers.

8) What’s an exception? What are the varieties of exceptions?

The exception is an error dealing with a part of PL/SQL. There are two varieties of exceptions: pre_defined exception and user_defined exception.

9) How one can write a single assertion that concatenates the phrases? Hey? and? World? and assign it in a variable named Greeting?

Greeting := ‘Hey’ || ‘World’;

10) Does PL/SQL assist CREATE command?

No. PL/SQL would not assist the info definition instructions like CREATE.

11) Write a singular distinction between a perform and a saved process.

A perform returns a price whereas a saved process would not return a price.

12) How exception is totally different from error?

Each time an Error happens Exception arises. Error is a bug whereas an exception is a warning or error situation.

13) What’s the fundamental motive behind utilizing an index?

Sooner entry of information blocks within the desk.

14) What are PL/SQL exceptions? Inform me any three.

Too_many_rows
No_Data_Found
Value_error
Zero_error and many others.

15) How do you declare a user-defined exception?

You possibly can declare the Person-defined exceptions underneath the DECLARE part, with the key phrase EXCEPTION.

Syntax:

<exception_name> EXCEPTION;

16) What are some predefined exceptions in PL/SQL?

An inventory of predefined exceptions in PL/SQL:

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
and many others.

17) What’s a set off in PL/SQL?

A set off is a PL/SQL program that’s saved within the database. It’s executed instantly earlier than or after the execution of INSERT, UPDATE, and DELETE instructions.

18) What’s the most variety of triggers, you may apply on a single desk?

12 triggers.

19) What number of varieties of triggers exist in PL/SQL?

There are 12 varieties of triggers in PL/SQL that include the mixture of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key phrases.

BEFORE ALL ROWS INSERT
AFTER ALL, ROW INSERT
BEFORE INSERT
AFTER INSERT and many others.

20) What’s the distinction between the execution of triggers and saved procedures?

A set off is robotically executed with none motion required by the person, whereas, a saved process is explicitly invoked by the person.

21) What occurs when a set off is related to a view?

When a set off is related to a view, the bottom desk triggers are usually enabled.

22) What’s the utilization of the WHEN clause in a set off?

A WHEN clause specifies the situation that should be true for the set off to be triggered.

23) How one can disable a set off title update_salary?

ALTER TRIGGER update_salary DISABLE;

24) Which command is used to delete a set off?

DROP TRIGGER command.

25) what are the 2 digital tables out there on the time of database set off execution?

Desk columns are referred as THEN.column_name and NOW.column_name.

For INSERT-related triggers, NOW.column_name values can be found solely.

For DELETE-related triggers, THEN.column_name values can be found solely.

For UPDATE associated triggers, each Desk columns can be found.

26) What’s saved Process?

A saved process is a sequence of statements or a named PL/SQL block that performs a number of particular capabilities. It’s much like a process in different programming languages. It’s saved within the database and could be repeatedly executed. It’s saved as a schema object. It may be nested, invoked, and parameterized.

27) What are the totally different schemas objects that may be created utilizing PL/SQL?

Saved procedures and capabilities
Packages
Triggers
Cursors

28) What are you aware by PL/SQL Cursors?

Oracle makes use of workspaces to execute SQL instructions. When Oracle processes a SQL command, it opens an space within the reminiscence referred to as Personal SQL Space. This space is recognized by the cursor. It permits programmers to call this space and entry its info.

29) What’s the distinction between implicit and specific cursors?

An implicit cursor is implicitly declared by Oracle. It is a cursor to all of the DDL and DML instructions that return just one row.

An specific cursor is created for queries returning a number of rows.

30) What’s going to you get by the cursor attribute SQLpercentROWCOUNT?

The cursor attribute SQLpercentROWCOUNT will return the variety of rows which can be processed by a SQL assertion.

31) What’s going to you get by the cursor attribute SQLpercentFOUND?

It returns the Boolean worth TRUE if not less than one row was processed.

32) What’s going to you get by the cursor attribute SQLpercentNOTFOUND?

It returns the Boolean worth TRUE if no rows have been processed.

33) What do you perceive by PL/SQL packages?

A PL/SQL bundle could be specified as a file that teams capabilities, cursors, saved procedures, and variables in a single place.

34) What are the 2 totally different components of the PL/SQL packages?

PL/SQL packages have the next two components:

Specification half: It specifies the half the place the interface to the applying is outlined.

Physique half: This half specifies the place the implementation of the specification is outlined.

35) Which command is used to delete a bundle?

The DROP PACKAGE command is used to delete a bundle.

36) How one can execute a saved process?

There’s two strategy to execute a saved process.

From the SQL immediate, write EXECUTE or EXEC adopted by procedure_name.

EXECUTE or [EXEC] procedure_name;

Merely use the process title

procedure_name;

37) What are the benefits of saved procedures?

Modularity, extensibility, reusability, Maintainability, and one-time compilation.

38) What are the cursor attributes utilized in PL/SQL?

%ISOPEN: it checks whether or not the cursor is open or not.

%ROWCOUNT: returns the variety of rows affected by DML operations: INSERT, DELETE, UPDATE, SELECT.

%FOUND: it checks whether or not the cursor has fetched any row. If sure – TRUE.

%NOTFOUND: it checks whether or not the cursor has fetched any row. If no – TRUE.

39) What’s the distinction between syntax error and runtime error?

A syntax error could be simply detected by a PL/SQL compiler. For instance incorrect spelling and many others. whereas, a runtime error is dealt with with the assistance of exception-handling part in a PL/SQL block. For instance: SELECT INTO assertion, which doesn’t return any rows.

40) Clarify the Commit assertion.

The next circumstances are true for the Commit assertion:

Different customers can see the info modifications made by the transaction.
The locks acquired by the transaction are launched.
The work achieved by the transaction turns into everlasting.

41) Clarify the Rollback assertion?

The Rollback assertion is issued when the transaction ends. The next circumstances are true for a Rollback assertion:

The work achieved in a transition is undone as if it was by no means issued.
All locks acquired by the transaction are launched.

42) Clarify the SAVEPOINT assertion.

With SAVEPOINT, solely a part of the transaction could be undone.

43) What’s a mutating desk error?

A mutating desk error happens when a set off tries to replace a row that it’s presently utilizing. It’s fastened by utilizing views or short-term tables.

44) What’s consistency?

Consistency merely signifies that every person sees a constant view of the info.

Think about an instance: there are two customers A and B. A transfers cash to B’s account. Right here the modifications are up to date in A’s account (debit) however till it is going to be up to date to B’s account (credit score), until then different customers cannot see the debit of A’s account. After the debit of A and credit score of B, one can see the updates. That is consistency.

45) What’s a cursor and why it’s required?

A cursor is a short-term work space created in system reminiscence when an SQL assertion is executed.

A cursor accommodates info on a choose assertion and the row of information accessed by it. This short-term work space shops the info retrieved from the database and manipulate this information. A cursor can maintain multiple row however can course of just one row at a time. Cursors are required to course of rows individually for queries.

46) What number of varieties of cursors can be found in PL/SQL?

There are two varieties of cursors in PL/SQL.

Implicit cursor, and
specific cursor

The article was published on October 14, 2021 @ 12:48 PM

PL/SQL Interview Questions and Answers