50+ PL/SQL Interview Questions

50+ PL/SQL Interview Questions

PL/SQL Interview Questions

1) What is PL/SQL? 

PL/SQL represents procedural language augmentation to SQL. It underpins procedural highlights of programming language and SQL both. It was created by Oracle Corporation in ahead of schedule of 90's to improve the abilities of SQL.

2) What is the motivation behind utilizing PL/SQL? 

PL/SQL is an expansion of SQL. While SQL is non-procedural, PL/SQL is a procedural language structured by Oracle. It is concocted to conquer the restrictions of SQL.

3) What are the most critical attributes of PL/SQL? 

A rundown of some remarkable attributes:

PL/SQL is a square organized language.

It is versatile to all situations that help Oracle.

PL/SQL is incorporated with the Oracle information lexicon.

Put away techniques help better sharing of use.

4) What is PL/SQL table? Why it is utilized? 

Objects of sort tables are called PL/SQL tables that are displayed as database table. We can likewise say that PL/SQL tables are an approach to giving exhibits. Exhibits resemble transitory tables in memory that are handled in all respects rapidly. PL/SQL tables are utilized to move mass information. They streamlines moving accumulations of information.

5) What are the datatypes accessible in PL/SQL? 

There are two kinds of datatypes in PL/SQL:

Scalar datatypes Example are NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN and so forth.

Composite datatypes Example are RECORD, TABLE and so forth.

6) What is the fundamental structure of PL/SQL? 

PL/SQL utilizes BLOCK structure as its fundamental structure. Each PL/SQL program comprises of SQL and PL/SQL explanation which structure a PL/SQL square.

PL/SQL square contains 3 areas.

The Declaration Section (discretionary)

The Execution Section (obligatory)

The Exception dealing with Section (discretionary)

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

Capacity: The principle motivation behind a PL/SQL work is for the most part to process and restore a solitary esteem. A capacity has an arrival type in its determination and must restore an esteem indicated in that type.

Methodology: A system does not have an arrival type and ought not restore any esteem but rather it can have an arrival proclamation that essentially stops its execution and comes back to the guest. A method is utilized to restore numerous qualities else it is commonly like a capacity.

Bundle: A bundle is mapping object which bunches coherently related PL/SQL types , things and subprograms. You can likewise say that it is a gathering of capacities, method, factors and record type explanation. It gives measured quality, because of this office it helps application improvement. It is utilized to conceal data from unapproved clients.

8) What is exemption? What are the sorts of exemptions? 

Special case is a mistake dealing with part of PL/SQL. There are two sort of exemptions: pre_defined special case and user_defined exemption.

9) How to compose a solitary proclamation that connects the words ?Hello? what's more, ?World? also, relegate it in a variable named Greeting? 

Welcome := 'Hi' || 'World';

10) Does PL/SQL support CREATE direction? 

No. PL/SQL doesn't bolster the information definition directions like CREATE.

11) Write a one of a kind contrast between a capacity and a put away method. 

A capacity restores an esteem while a put away technique doesn?t return an esteem.

12) How special case is not quite the same as mistake? 

At whatever point an Error happens Exception emerges. Blunder is a bug while special case is a notice or mistake condition.

13) What is the primary purpose for utilizing a record? 

Quicker access of information obstructs in the table.

14) What are PL/SQL exemptions? Disclose to me any three. 

Too_many_rows

No_Data_Found

Value_error

Zero_error and so forth.

15) How would you proclaim a client characterized special case? 

You can pronounce the User characterized special cases under the DECLARE area, with the catchphrase EXCEPTION.

Grammar:

EXCEPTION; 

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

A rundown of predefined exemptions 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 so forth.

17) What is a trigger in PL/SQL? 

A trigger is a PL/SQL program which is put away in the database. It is executed preceding or after the execution of INSERT, UPDATE, and DELETE directions.

18) What is the most extreme number of triggers, you can apply on a solitary table? 

12 triggers.

19) what number kinds of triggers exist in PL/SQL? 

There are 12 sorts of triggers in PL/SQL that contains the blend of BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL catchphrases.

BEFORE ALL ROW INSERT

AFTER ALL ROW INSERT

Prior to INSERT

AFTER INSERT and so on.

20) What is the contrast between execution of triggers and put away methodology? 

A trigger is naturally executed with no activity required by the client, while, a put away method is expressly summoned by the client.

21) What happens when a trigger is related to a view? 

At the point when a trigger is related to a view, the base table triggers are regularly empowered.

22) What is the use of WHEN statement in trigger? 

A WHEN statement determines the condition that must be valid for the trigger to be activated.

23) How to incapacitate a trigger name update_salary? 

Change TRIGGER update_salary DISABLE;

24) Which order is utilized to erase a trigger? 

DROP TRIGGER order.

25) what are the two virtual tables accessible at the season of database trigger execution? 

Table segments are alluded as THEN.column_name and NOW.column_name.

For INSERT related triggers, NOW.column_name values are accessible as it were.

For DELETE related triggers, THEN.column_name values are accessible as it were.

For UPDATE related triggers, both Table segments are accessible.

26) What is put away Procedure? 

A put away methodology is a succession of articulation or a named PL/SQL square which performs at least one explicit capacities. It is like a strategy in other programming dialects. It is put away in the database and can be over and over executed. It is put away as construction object. It very well may be settled, conjured and parameterized.

27) What are the diverse outlines questions that can be made utilizing PL/SQL? 

Put away strategies and capacities

Bundles

Triggers

Cursors

28) What do you know by PL/SQL Cursors? 

Prophet utilizes workspaces to execute the SQL directions. At the point when Oracle forms a SQL direction, it opens a region in the memory called Private SQL Area. This region is distinguished by the cursor. It enables developers to name this territory and access it?s data.

29) What is the distinction between the understood and express cursors? 

Verifiable cursor is certainly announced by Oracle. This is a cursor to all the DDL and DML directions that arrival just a single line.

Express cursor is made for inquiries restoring numerous columns.

30) What will you get by the cursor characteristic SQL%ROWCOUNT? 

The cursor trait SQL%ROWCOUNT will restore the quantity of lines that are prepared by a SQL articulation.

31) What will you get by the cursor property SQL%FOUND? 

It restores the Boolean esteem TRUE if something like one column was handled.

32) What will you get by the cursor property SQL%NOTFOUND? 

It restores the Boolean esteem TRUE if no columns were handled.

33) What do you comprehend by PL/SQL bundles? 

A PL/SQL bundle can be determined as a record that bunches capacities, cursors, put away strategies, and factors in a single spot.

34) What are the two distinct pieces of the PL/SQL bundles? 

PL/SQL bundles have the accompanying two sections:

Determination part: It indicates the part where the interface to the application is characterized.

Body part: This part indicates where the execution of the determination is characterized.

35) Which direction is utilized to erase a bundle? 

The DROP PACKAGE direction is utilized to erase a bundle.

36) How to execute a put away system? 

There are two approach to execute a put away system.

From the SQL brief, compose EXECUTE or EXEC pursued by procedure_name.

EXECUTE or [EXEC] procedure_name;

Essentially utilize the methodology name

procedure_name;

37) What are the upsides of put away methodology? 

Measured quality, extensibility, reusability, Maintainability and one time accumulation.

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

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

%ROWCOUNT: restores the quantity of columns influenced by DML tasks: INSERT,DELETE,UPDATE,SELECT.

%FOUND: it checks whether cursor has gotten any column. On the off chance that yes - TRUE.

%NOTFOUND: it checks whether cursor has brought any column. Assuming no - TRUE.

39) What is the distinction between grammar blunder and runtime mistake? 

A sentence structure mistake can be effectively distinguished by a PL/SQL compiler. For instance: off base spelling and so on while, a runtime mistake is dealt with the assistance of special case taking care of segment in a PL/SQL square. For instance: SELECT INTO articulation, which does not restore any lines.

40) Explain the Commit explanation. 

Following conditions are valid for the Commit explanation:

Different clients can see the information changes made by the exchange.

The locks gained by the exchange are discharged.

The work done by the exchange ends up lasting.

41) Explain the Rollback articulation? 

The Rollback articulation is issued when the exchange closes. Following conditions are valid for a Rollback explanation:

The work done in a progress is fixed as though it was never issued.

All locks obtained by exchange are discharged.

42) Explain the SAVEPOINT explanation. 

With SAVEPOINT, just piece of exchange can be fixed.

43) What is transforming table blunder? 

Transforming table blunder is happened when a trigger attempts to refresh a column that it is as of now utilizing. It is fixed by utilizing perspectives or transitory tables.

44) What is consistency? 

Consistency essentially implies that every client sees the reliable perspective on the information.

Think about a model: there are two clients An and B. An exchanges cash to B's record. Here the progressions are refreshed in A's record (charge) yet until it will be refreshed to B's record (credit), till then different clients can't see the charge of A's record. After the charge of An and credit of B, one can see the updates. That?s consistency.

45) What is cursor and why it is required? 

A cursor is a brief work territory made in a framework memory when a SQL articulation is executed.

A cursor contains data on a select explanation and the column of information gotten to by it. This impermanent work zone stores the information recovered from the database and control this information. A cursor can hold more than one column, however can process just a single line at any given moment. Cursor are required to process pushes exclusively for inquiries.

46) what number kinds of cursors are accessible in PL/SQL? 

There are two kinds of cursors in PL/SQL.

Certain cursor, and

unequivocal cursor