PL/SQL Interview Questions

This is the first set of PL/SQL interview questions. Here, we are trying to give you more than 200 interview questions for SQL. For PL/SQL tutorial visit our blog’s tutorial sections.

1. Why PL/SQL?

PL/SQL is the Oracle Procedural Language extension of SQL. A PL/SQL program can have both your SQL statements and also procedural statements. The SQL statements are used to access data from the database, and the procedural statements are used to process this individual piece of data and control the program flow together.

• Support SQL
• Provides Support for the object oriented programming.
• Better performance
• Higher productivity
• Full protability
• Tight integration with oracle
• Tight Security

2. What is the Structure of PL/SQL Block?

DECLARE
<declare section>
BEGIN
<execution section>
EXCEPTION
<exception handling section>
END;

3. What is your Front-end?

And the answer is, obviously, if your organization is using Java, then it’s Java and if .net then it is .net or any other language like Python, scala, or any other programming language.

4. How do you pass the data from PL/SQL to front-end?

We can pass the data by using the cursors.

5. What is the difference between a global variable and a parameter variable?

The answer is any variable defined outside the procedure at a function is called a global variable and it maintains

its data for the duration of the session. Normally we define this global variable in a package or a package body, but whereas a parameter variable is a variable that is defined in the procedure or a function.

6. What are the new features introduced in Oracle Version 19C?

This particular version will be supported through 2023, with extended support available until 2026. Oracle Database Release 19c provides complete backup and recovery flexibility for the multitenant container database (CDB) and PDB level backups and restores, with add-on recovery catalog support.

7. I’m trying to insert 10000 records in a table. I’m inserting the 500 record set time after inserting 500 records. Unexpectedly, the program was aborted due to some exceptions. So now the question is when we try to rerun the program from which position the records are inserted into a database?

The Program will start from the beginning because oracle does not keep track of where exactly the program failed and again started off from there. Its developer responsibility is to clean up the mess caused by the political program where the exception occurred.

8. What are the various ways PL/SQL allows to execute the SELECT statements?

• Using the SELECT-INTO statement
• Fetching from on ecplicit cursor
• Using a cursor for loop
• Using execute IMMERATE-INTO for dyanmaic quries
• Using Cursor Variables

9. Which of the following literals are not present is pl/sql?

a) String b)Date and Time c) boolean d) Integer

Ans: D) integer

10. Which of the Control structures provide an unconditional approach to a particular part of a complex PL/SQL block?

Ans: GOTO

11. Is There Any Limit On the Size Of A Pl/sql procedure?

In current days, the maximum compiled size of a PL/SQL block is 64K and the maximum code size is 100K. we can run a select statement to query the size of an existing package or procedure.

select * from dba_object_size where name = 'procedure_name/package_name'

12. What is the difference between stored procedure and Function?

• Functions are written for specific tasks or computations, but whereas procedures are used to execute business logic.
• The function must return at least one value of any data type.We can return more values using the OUT parameters, but the procedure did not return any value.
• Functions can be called from SQL statements but if the function has a DML statement like insert update and delete, then it cannot be called from a SQL statement. If it doesn’t have any DML statement, then obviously it can be called from a SQL statement. Whereas a procedure cannot be called from SQL statements.

13. What is the difference between a procedure function and a package?

The answer is packages are used to group logically related objects like PL/SQL types, variables, constants subprogram cursors, exceptions. So basically packages are used to group them together.

Whereas procedure/functions are used to execute reusable business logic or perform business calculations.

14. what is the difference between a trigger and a procedure?

• We can execute a store procedure whenever we want with the help of executing the command. A trigger can be executed when the event(insert, delete and update) is fired on the table on which the trigger is defined,
• The stored procedures can take input parameters, but whereas we cannot pass parameters in a trigger.
• The store procedure can return values, but trigger cannot return a value.
• We can use the transaction statements like begin transaction, commit transaction and rollback inside a store procedure.But we cannot use all those transactional statements inside trigger.
• We can call a store procedure from the front end(.asp file, .asps file, .java files etc) but trigger can not be called by this files.

15. What is a stand-alone procedure?

A procedure that is not defined in the Package is called a stand-alone procedure.

It is created by using this command – CREATE PROCEDURE procedure_name

The major disadvantage of the standalone procedure is that it can not be overloaded. Overloading is not allowed in the standalone Procedure.