Top 10 Essential PLSQL Developer Interview Questions Part-3 (Ace Your Interview!)

PLSQL Developer Interview Questions

In the competitive landscape of technology careers, mastering the art of interviewing is crucial for aspiring PL/SQL developers. This blog post, Top 10 Essential PL/SQL Developer Interview Questions Part-3 (Ace Your Interview!), aims to equip job seekers, students, and tech enthusiasts with the knowledge they need to excel in their interviews. By exploring key PL/SQL developer interview questions, we provide insights and tips that will enhance your preparation and boost your confidence. Whether you are a seasoned professional or new to the field, this guide will help you navigate the interview process and stand out as a candidate.

2. What is the Difference between procedure and function?

FUNCTION	PROCEDURE
Function can be called inside select statement	Procedure cannot be called inside the select statement
Function must return a value	Procedure may or may not return a value
We can use return statement inside a function	We can use return statement inside a procedure. But that doesn’t act like the return statement inside the function
In functions, we can use DML operations. But the functions with dml operations cannot be called inside the select statement	In procedures, we can use DML operations
If we want to call the functions with dml operations inside the select statement, then we can use pragma autonomous transaction + tcl command	Irrespective of dml operations, we cannot call procedure inside select statement
Out parameter is not required inside the function, as function must return a value	If we want any output in procedure then we can use out parameter
If we want, we can use out parameter in function but that function cannot be called inside the select statement	Irrespective of out parameter , we cannot call procedure inside select statement

3. Difference between constraint and trigger?

TRIGGER	CONSTRAINT
Trigger is user-defined	Constraint is pre-defined
Trigger is 3 types i.e DDL ,DML , DATABASE	Constraints are 4 types UNIQUE KEY, PRIMARY KEY , CHECK key, FOREIGN KEY
Triggers can be written on tables, schema, database	Constraints can be created only on columns
Trigger can be seen and modify the code	Constraints code cannot be seen and cannot be modified as they are encrypted
Constraints always follow same rules and regulations	Constraints always follow the same rules and regulations
To see triggers in our schema: Select * from user_triggers	To see constraints in our schema: Select * from user_constraints
Whatever we cannot handle through constraints that can be handled through triggers.	By using constraints we cannot maintain audit_table

4. Difference between Procedure and Trigger ?

TRIGGER	PROCEDURE
Trigger fires implicitly	Procedure fires explicitly
We cannot give parameters to triggers	We can debug the procedure
Anyone can call the procedure and use it	We can give parameters to a procedure
We can drop the procedure	We cannot debug the trigger
We can call procedure inside triggers	We cannot call triggers inside procedures
We can disable or enable triggers	We can drop procedure
Triggers are mainly used to avoid transaction, to maintain audit, to do one action while another action is going on	Procedure is mainly used to do calculations

5. Difference between Procedure and Package?

Package	Procedure
Package is a collection of related objects	Procedure is a standalone object with a set of statements
Package has 2 parts SPEC & BODY	Procedure has no parts. Complete procedure we can write in one area
If we call any program in the package then entire package will come and store in the ram until the session end	If we call procedure, then the procedure will come and store in ram until program execution only
If we declare variables in the package spec, they become global and can be used inside and outside the package.	Procedure variables are local variables
If we want to declare any objects that can be done only in package spec, if we want to write any function or procedure, we can do it in package body.	In procedure both variable declaration and body will be in one area
Packages support function overloading, forward declaration, on time procedure	Those we cannot see in procedures
Packages are mainly used to develop a module	Procedures are mainly used to do calculations

6. Difference between Implicit cursor and Explicit cursor?

IMPLICIT CURSOR	EXPLICIT CURSOR
If we want to check the status of dml operations in plsql block, then we will use “implicit cursor”	Explicit cursor is used to fetch records in plsql block
Implicit cursor is used to know the status of ‘into clause’	Into clause is there to fetch record, but it will fetch only one record. If we want to fetch more than one record then we will use explicit cursor.
Implicit cursor has a key word called ‘SQL’	In explicit cursor whatever name we want, we can give
Implicit cursor has 3 attributes FOUND, NOTFOUND ,ROWCOUNT	Explicit cursor has 4 attributes i.e FOUND ,NOTFOUND ,ROWCOUNT, ISOPEN
Implicit cursor has no types	Explicit cursor has 3 types BASIC CURSOR, CURSOR FOR LOOP, CUROSR FOR LOOP WITH SELECT STATEMENT

7. Advantages and Disadvantages of Packages

Advantages of packages :

• Package is used to develop module of an application and help us to maintain our application in more efficient way.
• If we call any packaged program then entire package will come and store in the ram until the session end, if we call any other program of the same package it will be executed immediately as the entire package has stored in the ram and no need to go and search in the database.
• If we declare any function/procedure/variable/collection/cursor in the package spec then these objects will be global. They can be used inside the package and outside the package.
• Package is collection of related objects.
• Package supports function overloading means we can write any no. of functions of procedures inside package with same name but the no. of parameters must be different.
• When we declare variables in package spec then they will become global variables.
• If we can drop package, then both spec and body will be dropped at a time. We cannot drop separately.

Disadvantages of packages :

• Without package spec we cannot write package body.
• It occupies space.
• Package won’t support backward navigation.

8. Difference between raise and raise application error ?

RAISE	RAISE APPLICATION ERROR
Raise needs 3 sections DECLARATION SECTION HANDLING SECTION EXCEPTION SECTION	RAE needs only one section HANDLING SECTION
Raise doesn’t need any error number	RAE needs error number between -20000 to -20999
Raise will terminate the program there itself and enters the exception section	RAE will terminate the program there itself and shows error message on the screen.
Raise won’t rollback the previous DML operations	RAE will rollback all the previous DML operations

9. Difference between cursor and collection ?

CURSOR	COLLECTION
Cursor fetches data record by record	Collection fetches all data at time
Cursor requires more no. of context switches to fetch data	Collection requires only one context switch to fetch data
Cursor has global type called sys refcursor	Collection has no global data type
In cursors, we can use normal variables and collection variables	In collection, we must use collection variable
Cursor has 3 types IMPLICIT CURSOR ,EXPLICIT CURSOR ,REF CURSOR	Collection has only one type BULK COLLECT
Cursor has attributes	Collection has methods
We can use into clause and bulk collect into clause	We can use only bulk collect into clause
Cursor supports only forward navigation	Collection supports both forward and backward navigation
There are 2 types of errors in cursors INVALID CURSOR ,CURSOR IS ALREADY OPEN	There are 4 types of errors in collection NO DATA FOUND, SUBSCRIPT BEYOND COUNT, SUBSCRIPT OUT OF LIMIT ,REFERENCE TO UNINITIALIZED COLLECTION

10. What is bulk bind and Bulk Limit ?

Bulk Bind :

• oracle uses two engines to execute plsql code. All the program code is handled by plsql engine and all the sql statements are handled by sql engine.
• When we write any dml statement in collection loop then it will be considered as separate statement. So to execute each dml statement separate context switch is required, to avoid this we will use bulk bind.
• Bulk bind will wait till the end of loop, bind all the dml statements and in one shot it will send them to sql engine.

Bulk Limit :

• PLSQL collections are in memory. Massive collections may affect the performance of the system due to huge amount of data. So to avoid this we will use bulk limit.
• Bulk limit will chunk the huge data of collection and process each chunk in one turn. Bulk limit we have to write along with collection only.

Latest Posts :

• Mastering Oracle SQL Joins: The Ultimate Guide to Data Retrieval and Performance
• Mastering Oracle Database Architecture: A Comprehensive Overview
• Mastering PostgreSQL Architecture: 6 Powerful Steps to Optimize Performance & Scalability
• Understanding Oracle Triggers – A Comprehensive Guide with Examples
• SQL Query Design Patterns: Writing Efficient Queries

Categories :

• Interview Questions
• PLSQL
• PostgreSQL
• SQL