Explore topic-wise InterviewSolutions in .

In PL/SQL, an exception is technically an error CONDITION during the execution of a program. There are two TYPES of limitations in PL/SQL, NAMELY:

• System DEFINED EXCEPTIONS
• User-defined exceptions

Nowadays, Databases are getting COLOSSAL, and there is a large amount of DATA to go through while finding anything in particular when required. Indexing in PL/SQL helps to speed up the process of finding a specific PIECE of data by RETURNING a small portion of table rows in order to make the process of accessing data accurate and faster.

In PL/SQL a cursor is needed for the FOLLOWING reasons:

• To retrieve data, a single row at a TIME, from a data set, UNLIKE in the SQL commands where all the rows are operated on the result set at one time.
• Cursors are also USED for updating records in a singleton fashion or a row by row MANNER into a database table.

A SCALAR data type in PL/SQL is one in which there are single VALUES with no internal components such as DATE, BOOLEAN, or NUMBER.

To define a multi-line comment style in PLSQL, there are two steps.

• A DOUBLE HYPHEN (- -) is placed anywhere on a line (except on a CHARACTER literal), which SHALL turn it into a comment.
• To make a comment as a multi-line, it must begin with a slash-asterisk (/*) and then end with an asterisk-slash (*/).

In PL/SQL, procedures support all the parameter MODES listed below, whereas, functions only support the IN parameter mode. Here are all the parameter modes in PL/SQL for procedures and functions:

• IN: This is the DEFAULT mode, which allows the CALLING code to pass values in the procedures or functions.
• OUT: The OUT PARAMETERS allow the procedures or functions to pass the values back towards the calling code.
• IN OUT: The IN OUT parameters allow the calling code to pass the values in and also to receive values from procedures or functions.

The Function overloading in PL/SQL is based on the CONCEPT of polymorphism. It actually REFERS to the CAPABILITY of any programming method to perform multiple operations based on the input data type and the CONTEXT in which the PROCESS, i.e., function, is used.

SERVER processes assigned in a CLIENT program to be used as a memory for PRIVATE data are known as Process Global Area. UGA or User Global Area is used to retain data during a session database such as PACKAGE Variables and Private SQL areas.

Attributes in PL/SQL are properties which let the USER reference the DATA type and structure of a PRESENT item without having to REPEAT its definition. Attributes are mainly used for EASY maintenance of database tables.

In, PL/SQL, a SAVEPOINT is a TCL(Transition Control Language), statement is used to create a breakpoint during a specific/current transaction at a PARTICULAR location. A Savepoint is basically a PIT stop for transactions and ROLLBACK can be used to ERASE any PART of a transaction.

In PL/SQL modules, packages are used to encapsulate LOGICALLY related DATA types, constants, variables, subprograms, CURSORS, and any other exceptions. Each package can be used to simplify the level of understanding inside an interface using SIMPLE, clear and well-defined functions within.

In PL/SQL, Rowtype is used to PROVIDE a RECORD type/data type in the form of ROWS in a database. Variables declared using the %ROWTYPE attribute are CONSIDERED to be declared using a data type name.

In PL/SQL, there are two types of triggers:

• Row level triggers: Used to trigger an event once any changes are MADE in rows.
• Statement level trigger: It is used to trigger an event for each executed SQL statement.

DELIMITER in PL/SQL is a simple/compound symbol. In PL/SQL, DELIMITERS are used to represent ARITHMETIC operations like addition and subtraction.