Explore topic-wise InterviewSolutions in .

PL/SQL is an extension for SQL and Oracle. It enhances the capabilities of SQL. Therefore, with every release of the Oracle Database, the feature and functionalities of PL/SQL is enhanced.

It is available in Oracle Database since version 6. However, PL/SQL is also available in TimesTen in-memory database since version 11.2.1 and IBM DB2 since version 9.7.

PL/SQL is strongly integrated with SQL and supports both static and dynamic SQL.

The following are the FEATURES in PL/SQL:

• PL/SQL is a procedural language that extends SQL.
• PL/SQL has a built-in interpreted programming environment.
• PL/SQL's syntax has SHAPED ADA and PASCAL programming language.
• Structured programming is possible with PL/SQL through functions and procedures.
• It has Block Structures as well as nest block structures. Executes as a whole block.
• PL/SQL has object-oriented features in it.
• PL/SQL also comes with the functionality of Triggers. Triggers are stored in a database and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP).
• PL/SQL is a standard language for Oracle development.
• PL/SQL is USED to write program blocks, functions, procedures triggers, etc.
• It has cursors. Oracle forms a memory area, when an SQL statement is processed. This memory area is called context area. A cursor in PL/SQL is a pointer to this context area. It has INFORMATION about processing the statement.

The PL/SQL PROGRAMMING CONSTRUCTS INCLUDE:

1. EMBEDDED SQL support
2. Stored procedures
3. Packages
4. Triggers
5. Exception Handling
6. Cursors

The VSIZE() function RETURNS the number of bytes in the INTERNAL REPRESENTATION of an EXPRESSION. An example:

The output:

To pad a string with zeros, you can use RPAD or LPAD function in PL/SQL. Let us see how to pad the right SIDE of a string with zeros:

RPAD(tom, 6)

The output would have a WIDTH of 6 characters now. SINCE the string is of 3 characters, therefore REST of the space would be filled with 0S:

tom000

Sequence of characters are called STRINGS. Just like any other language, a string can be letters, numbers, special characters, etc.

The following are the three types of strings in PL/SQL:

• Fixed-length strings

In fixed-length strings, the length is to be specified while DECLARING a string. The string is right-padded with spaces to the length so specified.

• VARIABLE-length string

A maximum length up to 32,767 for the string is specified and no padding TAKES place.

• Character large objects (CLOBs)

CLOBs are variable-length strings that can be up to 128 terabytes.

Let us see how we can declare a String variable. We have a lot of string datatypes, such as CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, and NCLOB in PL/SQL.

The declaration:

Let us see an example where we have two variables:

The output:

VARRARY data STRUCTURE store a fixed-size sequential collection of elements of the same type. It has contiguous memory locations with the lowest address as the FIRST element and the highest address as the last element.

The CREATE TYPE statement is used to create a varray type. The following is the syntax for creating a VARRAY type within a PL/SQL block:

Here,

The following is an example:

Let us see an example wherein we are DISPLAYING the appraisal points for employees according to their IDs:

The output:

In PL/SQL, packages are the schema objects that groups logically related PL/SQL types and variables. A Package has the following two PARTS:

• Package Specification

The specification has the information about the package content. It declares the types, variables, constant, cursors, etc. It EXCLUDES the CODE for subprograms. The objects in the specification are the public OBJECT, however, a subprogram not part of the specification is a private object.

• Package Body

The Package Body has the implementation of subprogram declared in the specification. To create Package Body, use the CREATE PACKAGE BODY statement.

The following are the guidelines:

• You need to design and define the package specifications before the package bodies.
• Declare public cursors in package specifications and define them in package bodies.
• Assign initial values in the initialization part of the package body. Avoid this in declarations.

The WHEN clause has SEARCH conditions that output BOOLEAN values in a searched CASE statement.

The following is the syntax of searched CASE that has the WHEN conditions:

Let us see an example:

The output:

With PL/SQL, we can create a user-defined subtype. It already has PREDEFINED subtypes in STANDARD package.

Let us now SEE how to create an INTEGER subtype:

The output:

Yes, a %ROWCOUNT ATTRIBUTE returns the total NUMBER of rows RETURNED by the FETCH command.

The TIMESTAMP datatype stores the YEAR, MONTH, and day of the DATE datatype. It also ALLOWS you to store HOUR, minute, and second values.

ACTUAL parameters can be passed as Positional notation, Named notation as well as MIXED notation. In Positional Notation, the same parameters in the same order as they are declared in the procedure are specified.

The actual parameter is associated with the formal parameter in named notation using the arrow symbol ( => ) like:

The procedure call for this would be:

In PL/SQL, you can mix both notations in procedure call with n mixed notation. REMEMBER, the positional notation should precede the named notation.

The following is an example:

Let’s say we have below given table:

Now, we will call with Positional, mixed and named notations:

Above, you can see we have a procedure “new_budget”. We are updating the budget here:

We are calling one by one with positional, mixed and named notation:

The RAISE statement is used in PL/SQL to raise exceptions. Through this a user can raise exceptions explicitly.

The following is the SYNTAX to display how to use raise exceptions with RAISE statement:

An EXAMPLE can be if the ID is less than zero, the raise an exception:

Above “my_exception” is the following we set above:

Let us see an example wherein we are raising exceptions. The following is the table we are taking into consideration:

Let’s say we created the following procedure and a user-defined exception in it:

On executing the above procedure, the following output is visible. Let’s say our input is department id as “-2”, yes, a negative number. When execution, it raises the following user-defined error:

The BETWEEN operator in PL/SQL checks whether a value lies in a specified RANGE or not.

For example, the value a BETWEEN x AND y says that a >= x and a <= y.

Let US see an example:

The output:

Let us see another example:

The output:

To include single QUOTES in a string literal, you NEED to use it like:

Above, we have SET TWO single quotes to display one single quote.

Let us see an example.

Our string is:

The following is the code to include single quotes:

The output:

To FIND the FACTORIAL of a NUMBER, the FOLLOWING is the code:

The output:

The %ROWCOUNT cursor attribute returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

To use any of the above attribute, use the following syntax:

Above, set any attribute name under “attribute_name”.

LET us consider the following table for our example:

Now, we will update the budget of all the departments and then check what all rows got affected using the implicit cursor attribute. With that we will use other attributes as well:

The output:

Since we updated the budget for all the departments above, therefore our table rows would look like this:

The LARGE OBJECT Data Types are pointers to large objects that are stored separately from other data items, like text, graphic images, video clips, etc. The following are the data types:

• BFILE: Store large binary objects in operating system files OUTSIDE the DATABASE. The size is system-dependent and exceed 4 GB.
• BLOB: Store large binary objects in the database. The size is 8 to 128 TB.
• CLOB: Store large blocks of character data in the database. The size is 8 to 128 TB.
• NCLOB: Store large blocks of NCHAR data in the database. The size is 8 to 128 TB.

Like other programming languages, PL/SQL ALSO allow users to create own exceptions. Use the RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR to declare and raise the user-defined exception explicitly.

To create own exception, firstly consider the following table <DEPTBUDGET>:

Let’s say we created the following procedure and a user-defined exception in it:

On executing the above procedure, the following output is VISIBLE. Let’s say our input is department id as “-2”, yes, a negative number. When execution, it raises the following user-defined error:

Within a PACKAGE, you can refer the OBJECTS and SUBPROGRAMS LIKE this:

• name_of_package.object_name
• name_of_package.subprogram_name

With PL/SQL, you can easily pass a record as a subprogram parameter in the same way pass any other variable.

The following is an example:

The output:

Each subprogram in PL/SQL has a name and a list of parameters. With that, it is having the following parts:

• Declaration
• Executable
• Exception Handling

Let us now see them one by one:

• Declaration

It contains declarations of cursors, constants, variables, exceptions, etc.

• Executable

Had STATEMENTS that perform actions.

• Exception Handling

The code that handles run-time errors.

The following is the syntax to create a subprogram (procedure):

Here,

• CREATE [OR REPLACE] means a new procedure is created or MODIFIES the existing one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

The EXISTING trigger is recreated by the optional ARGUMENT [OR REPLACE] in a CREATE TRIGGER command.

The INVALID_NUMBER EXCEPTION is raised when the conversion of a CHARACTER STRING into a number fails because the string does not represent a VALID number.

The HEXTORAW in PL/SQL introduced to convert a hexadecimal VALUE into a raw value. An EXAMPLE here DISPLAYS the same:

The output:

With NOT INSTANTIABLE clause in PL/SQL, you can declare an abstract object. To WORK with it, you need to CREATE a SUBTYPE of such objects to USE its functionalities.

In PL/SQL, packages are the schema objects that groups logically RELATED PL/SQL types and variables. A Package has the following TWO parts:

• Package Specification

The specification has the information about the package content. It declares the types, variables, constant, cursors, etc. It excludes the code for subprograms. The objects in the specification are the public object, however, a subprogram not part of the specification is a private object.

• Package Body

The Package Body has the implementation of subprogram declared in the specification. To create Package Body, use the CREATE PACKAGE BODY statement.

The following is our sample table:

The following is an example wherein we have a package “dept_pck”:

On executing the above code, the following is VISIBLE:

Now, to access the package variable, procedures, ett, you can use the dot operator as shown below:

Continuing our example:

When you will run the above code, you need to input the department id. Let’s say your input is department id 3, the output would be:

PL/SQL Block Structures and it executes as a whole block.

The block structure can be understood with the concept of subprograms in PL/SQL. A block structure has the following PARTS:

• Declarative

It contains declarations of cursors, CONSTANTS, VARIABLES, exceptions, etc. Variables are declared here.

• Executable

Had statements that perform actions. It must have atleast one statement.

• Exception Handling

The code that handles run-time errors. It use EXCEPTION keyword. 

The following is the syntax to create a subprogram (procedure):

Here,

• CREATE [OR REPLACE] MEANS a new procedure is created or modifies the EXISTING one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

Here is an example of a subprogram (procedure):

The output displays the procedure created successfully:

When a CURSOR is closed, then the allocate memory is released.  To close a cursor, you need to use the CLOSE CLAUSE with the name of cursor you want to close.

Let us first see how to create a cursor, open and fetch the cursor. After that we will close it. This is all part of Explicit cursors since they are custom defined i.e. PROGRAMMER defined cursors.

The following is the syntax to create explicit cursor:

Here,
“name_of_cursor” is the cursor name

As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,

Let us declare a cursor and set a cursor name with the SELECT statement:

Above, “d_deptbudget” is our cursor.

The memory gets allocated for the cursor when it is opened in PL/SQL. Opening the cursor allocates the memory for the cursor and makes it ready for FETCHING the rows returned by the SQL statement into it

Access one row at a time when a cursor is fetched in PL/SQL.

The following is when you fetch a cursor which is already opened:

When a cursor is closed, then the allocate memory is released. To close the above created cursor:

Let us now see an example:

The output display department id and name:

The FOLLOWING are the methods AVAILABLE in PL/SQL to TRIM characters:

The following is how you can use LTRIM() in PL/SQL to trim characters from the left:

The output:

The following is how you can use RTRIM() in PL/SQL to trim characters from the right:

The output:

The following is how you can use TRIM() in PL/SQL to trim characters from the right and left:

The output:

The LIKE operator is a comparison operator in PL/SQL through which compares a character, string, or CLOB value to a pattern. The operator RETURNS TRUE if the value matches the pattern and FALSE if it does not.

For an example, we will take a procedure here and check for values:

The OUTPUT displays the following:

Above we have used the following wildcards:

For example, above we used J%n for “John”, the output was true, since the % wildcard matches any string of any length.

A transaction in PL/SQL is an ATOMIC unit that may consist of one or more related SQL statements.

There is a difference between an executed SQL statement and a committed transaction. On successful execution of SQL statement, if transaction CONTAINING the statement is not committed, then it can be rolled back and the changes made by the statement can be undone. Here, committed means permanent in the database. However, rolled back means the changes can be undone.

Beginning with transactions, let us understand its concepts:

• COMMIT

The COMMIT statement saves all the changes SINCE the last COMMIT or ROLLBACK. An explicit or implicit request is made to commit a transaction.

• ROLLBACK

The Rollback undone all the changes since the last COMMIT or ROLLBACK.

• ROLLBACK TO SAVEPOINT

Undone all the changes made since the specified savepoint was made.

• SAVEPOINT

The SAVEPOINT statement forms a savepoint. This allows in performing partial ROLLBACKs. A transaction begins when the first SQL statement is encountered after connecting to the database.

To commit a transaction in PL/SQL, use the COMMIT command:

Let’s say the following is the INSERT in the table. At the end, execute the COMMIT command if you want to commit the transaction:

To rollback the transactions, use the ROOLBACK command. The changes you made to the database without COMMIT can be undone with ROLLBACK.

The syntax:

Here, name_of_savepoint is the name of the savepoint.

If savepoint is not used, then simply use the “ROLLBACK” statement to rollback all the changes.

Savepoints splits a LONG transaction into smaller UNITS. This is simply done using checkpoints. These checkpoints allow you to roll back to a checkpoint in a long transaction using the following command:

Let us see an example of SAVEPOINT:

Above, we have created a SAVEPOINT s1, which will allow us to rollback.

ACCESS one row at a time when a cursor is fetched in PL/SQL.

The following is when you FETCH a cursor which is already opened. Here, d_deptbudget is the cursor name:

Let us first see how to create a cursor, open and fetch the cursor. After that we will close it. This is all part of Explicit cursors since they are custom defined i.e. programmer defined cursors.

The following is the syntax to create explicit cursor:

Here,
“name_of_cursor” is the cursor name

As you can see above, an explicit cursor is created on a SELECT statement. Let us now see how to work with Explicit Cursors,

Let us declare a cursor and set a cursor name with the SELECT statement:

Above, “d_deptbudget” is our cursor.

The memory gets allocated for the cursor when it is opened in PL/SQL. Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it

Access one row at a time when a cursor is fetched in PL/SQL.

The following is when you fetch a cursor which is already opened:

When a cursor is closed, then the allocate memory is released. To close the above created cursor:

Let us now see an example:

The output display department id and name:

Triggers are stored in a DATABASE and fired when some event occurs. The event here can be a Database Manipulation statement i.e. DELETE, INSERT, or UPDATE, or a Database Definition statement (CREATE, ALTER, or DROP). It can even be a database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Syntax

The CREATE TRIGGER statement is used in PL/SQL to create a trigger. The syntax:

Here,

• CREATE [OR REPLACE] TRIGGER name_of_trigger – The statement creates or replaces existing trigger with the name_of_trigger.
• {BEFORE | AFTER | INSTEAD OF} – The BEFORE or AFTER specifies when the trigger will get executed. The INSTEAD OF is for creating trigger on a view.
• {INSERT [OR] | UPDATE [OR] | DELETE} – These clauses specify the DML operation.
• [OF col_name] − The column name that will be updated.
• [ON table_name] – The name of the table associated with the trigger.
• [REFERENCING OLD AS o NEW AS n] − Refer new and old values for various DML statements.
• [FOR EACH ROW] − A row-level trigger, i.e. the trigger will be executed for each row being affected.
• WHEN (condition) − A condition for rows for which the trigger would fire.

Let us now take an example wherein we will create a trigger, trigger it, and even delete it.

For that, we are considering the FOLLOWING table <DEPTBUDGET>:

Create Trigger

Now we will create a row-level trigger that gets fired for INSERT or UPDATE or DELETE operations performed on the table. This is created to display the budget differences between new and old assigned BUDGETS:

The above trigger it will fire before any DELETE or INSERT or UPDATE operation on the table, since we added the following above:

We will now perform a DML operation on the DEPTBUDGET table i.e. the UPDATE statement.

The statement would update an existing record in the table:

We updated the record in the DEPTBUDGET table. When we will fire the “budget_diff”, it displays the following:

Once a value is DECLARED in PL/SQL, you cannot change it. The CONSTANT keyword is used in PL/SQL to DECLARE a constant.

The following is a query to FIND the area of circle, wherein we have SET the constant pi as:

Above, we have declared a constant.

Let us see the COMPLETE code:

The output displays the area of circle:

The EXIT statement is used to immediately terminate a loop. With this, the program control resumes at the next statement FOLLOWING the loop. It can also be used in a NESTED loop to stop the execution of the INNERMOST loop.

When the EXIT statement is encountered inside a loop, the loop is immediately TERMINATED and the program control resumes at the next statement following the loop.

The following is the syntax:

The example code:

The output:

Here are some of the PREDEFINED EXCEPTIONS in PL/SQL:

1. PROGRAM_ERROR
2. TIMEOUT _ON_RESOURCE
3. INVALID_CURSOR
4. LOGON_DENIED
5. ZERO_DIVIDE
6. STORAGE_ERROR
7. NO_DATA_FOUND
8. TOO_MANY_ROWS
9. CURSOR_ALREADY_OPEN
10. INVALID_NUMBER

No, STORED PROCEDURE in PL/SQL does not return a value. Stored Procedure CALL USING CALL keyword.

The access operator (.) is used in PL/SQL to access the attributes and METHODS of the OBJECT USING the instance name and the (.). The EXAMPLE shows the usage:

The DBMS_TRACE package has the INTERFACE to trace PL/SQL FUNCTIONS, procedures, and exceptions. The package provides subprograms to start and STOP PL/SQL TRACING in a session.

The FOLLOWING are the constants of the DBMS_TRACE package:

• TRACE_ALL_CALLS: Traces calls or returns
• TRACE_ALL_EXCEPTIONS: Traces exceptions
• TRACE_ENABLED_EXCEPTIONS: Traces exceptions and handlers
• TRACE_LIMIT: Save only the last few records.
• TRACE_ALL_SQL: Traces SQL statements
• TRACE_ENABLED_SQL: Traces SQL statements at PL/SQL level.
• TRACE_ALL_LINES: Traces each line
• TRACE_PAUSE: Pauses trancing
• TRACE_RESUME: Resume tracing
• TRACE_STOP: Stops tracing

The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data TYPES introduced in PL/SQL 11g Release 1. These are predefined subtypes of PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively.

The SIMPLE_FLOAT and SIMPLE_DOUBLE DATATYPES are alike their base types, except for their NOT NULL constraint.

• SIMPLE_INTEGER:  When the value will never be NULL and overflow checking is unnecessary.
• SIMPLE_FLOAT: When the value will never be NULL.
• SIMPLE_DOUBLE: When the value will never be NULL.

The SIMPLE_FLOAT and SIMPLE_DOUBLE has BETTER performance than BINARY_FLOAT and BINARY_DOUBLE.

SIMPLE_INTEGER is a predefined subtype of the PLS_INTEGER data type and differs from it in its overflow semantics. It has the same range as PLS_INTEGER i.e. -2,147,483,648 through 2,147,483,647) and has a NOT NULL constraint.

A subprogram in PL/SQl is a standalone subprogram at the schema level. The CREATE PROCEDURE or the CREATE FUNCTION statement is used in PL/SQL to create a standalone procedure.

• If you want to execute a standalone procedure, then use the EXECUTE keyword.
• If you want to delete a standalone procedure in PL/SQL, then you need to use the DROP PROCEDURE or DROP FUNCTION statement.

The FOLLOWING is the syntax to create a procedure:

Here,

• CREATE [OR REPLACE] means a new procedure is created or modifies the existing one.
• name_of_procedure is the procedure name.
• procedure_body is the EXECUTABLE part.

Here is an example of a procedure:

The output displays the procedure created successfully:

• Procedure created.
• Now to execute the procedure created above, you need to use the EXECUTE keyword. The name of the above procedure is “one”.
• To call it:

The above call will give the following output:

The RPAD function is USED to pad the right SIDE of a string with CHARACTERS. An example here pads the string:

The OUTPUT is:

NCHR is a function in PL/SQL that RETURNS the character BASED on the SPECIFIED number, which is an ASCII value. An EXAMPLE:

It displays the associated character:

To RETURN the ASCII value of a character, you need to USE the ASCII() method. It RETURNS a numeric value, which is the ASCII value.

The following is the syntax:

Here, myCharacter is the character you need to MENTION to get the ASCICC value.

The following is an example:

The output:

Recursion works the same way as we saw in programming languages like C, C++, JAVA, C#, etc.

A program or subprogram calling itself is called a recursive call and the process is recursion.

Let us see an example wherein we are displaying the usage of Recursive call:

The output:

The following are some of the Oracle Database PL/SQL packages:

• DBMS_ALERT

You can use the database triggers to alert an APPLICATION when a database values change.

• DBMS_OUTPUT

You may have USED PUL_LINE in this package to display output from PL/SQL blocks, subprograms, packages, and triggers. This also displays debugging information.

• DBMS_PIPE Package

This package lets different sessions to communicate over NAMED pipes

• HTF and HTP Packages

This allows the PL/SQL programs to generate HTML tags.

• UTL_SMTP

Using this package, you can send emails over SMTP (Simple Mail Transfer Protocol).

PL/SQL has subtypes of datatypes. This MEANS subtype is a subset of a datatype. The FOLLOWING are the types and subtypes of Numeric data TYPE in PL/SQL

• PLS_INTEGER

Signed INTEGER in range -2,147,483,648 through 2,147,483,647, represented in 32 bits

• BINARY_INTEGER

Signed integer in range -2,147,483,648 through 2,147,483,647, represented in 32 bits

• BINARY_FLOAT

Single-precision IEEE 754-format floating-point number

• BINARY_DOUBLE

Double-precision IEEE 754-format floating-point number

• DEC(prec, scale)

ANSI specific fixed-point type with maximum precision of 38 decimal digits

• DECIMAL(prec, scale)

IBM specific fixed-point type with maximum precision of 38 decimal digits

• NUMERIC(pre, secale)

Floating type with maximum precision of 38 decimal digits

• DOUBLE PRECISION

ANSI specific floating-point type with maximum precision of 126 binary digits (APPROXIMATELY 38 decimal digits)

• SMALLINT

ANSI and IBM specific integer type with maximum precision of 38 decimal digits

• REAL

Floating-point type with maximum precision of 63 binary digits (approximately 18 decimal digits)

The default VALUE is NULL. PL/SQL assigns NULL as the default value of a variable. To assign another value, you can use the DEFAULT keyword or the assignment operator. Let us see them one by one.Initialization with DEFAULT keyword:

The OUTPUT:

Initialization with assignment operator:

The output:

A subprogram in PL/SQL is EITHER a procedure or a function that can be invoked repeatedly. Each subprogram has a name and a LIST of parameters. It performs a specific task and the following two subprograms EXIST in PL/SQL:

• Functions: RETURN a single value.
• Procedures: Do not return a value directly.

The following are the parts of a subprogram:

• Declarative

It contains declarations of cursors, constants, variables, exceptions, etc.

• Executable

Had statements that perform actions.

• Exception Handling

The code that handles run-time errors.

The following is the syntax to create a subprogram (procedure):

Here,

• CREATE [OR REPLACE] means a new procedure is created or modifies the EXISTING one.
• name_of_procedure is the procedure name.
• procedure_body is the executable part.

Here is an example of a subprogram (procedure):

The output displays the procedure created successfully: