Most Frequently asked Oracle database SQL questions and answers
Are you preparing for PL/SQL Interviews? If yes, then we bring you here a list of commonly asked but important PL/SQL interview questions.
PL (Procedural language) is an extension to SQL (Structured Query Language) where a developer can write complex database interactions using procedures, control structures like branching and iteration, modules and functions. It has tight integration with SQL and supports both dynamic and static SQL.
This article covers a list of all the most important and commonly asked PL/SQL interview questions and answers, which will help you to crack any interview.
Most Popular PL/SQL Interview Questions and Answers
Q1 : What are the different schema objects that can be created in PL/SQL?
Ans: Oracle PL/SQL offers following objects creation for processing data at run time.
- Stored procedures
- Stored functions
- Packages
- Triggers
- Collections
Q2 : What are subprograms in PL/SQL and what are the advantages of subprograms?
Ans: Stored procedures and functions in PL/SQL are referred as subprograms. Stored procedures and functions are the key to modular, reusable PL/SQL code.
You can call stored procedures or functions from a database trigger, another stored subprogram, or interactively from SQL Command Line (SQL*Plus).
Procedures and functions are stored in a compact compiled form. When called, they are loaded and processed immediately. Subprograms take advantage of shared memory, so that only one copy of a subprogram is loaded into memory for execution by multiple users.
Q3 : What are the types of subprograms in PL/SQL?
Ans: Oracle PL/SQL have following types of subprograms.
Standalone Subprograms: Procedures and functions that are created outside of a package are called stored or standalone subprograms.
Packaged Subprograms: Procedures and functions defined within a package are known as packaged subprograms.
Local Subprograms: Procedures and functions nested inside other subprograms or within a PL/SQL block are known as local subprograms, which cannot be referenced by other applications and exist only inside of the enclosing block.
Q4 : What is a Stored Procedure in PL/SQL?
Ans: A Stored procedure is a named PL/SQL block having some sequence of statements, that can do some business processes.
Procedures are stored as schema objects in the Oracle database. They can be nested, invoked and parameterized.
Q5 : What is the syntax to create a Stored Procedure in PL/SQL?
Ans:
CREATE OR REPLACE PROCEDURE <your_procedure>
(
<parameterl IN/OUT <datatype>
..
.
)
[ IS | AS ]<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;
Q6 : What is Stored Function?
Ans: A stored function (also called a User Function or User-Defined Function) is a set of PL/SQL statements you can call by name. They are compiled and stored in an Oracle Database and so they got the name as Stored Function.
Stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called.
Q7 : What is the syntax to create a Stored Function in PL/SQL?
Ans:
CREATE OR REPLACE FUNCTION <your_procedure>
(
<parameterl IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;
Q8 : How to create a function that returns the sum of two variables and how to call that?
Ans: Below code creates a function named as “test_fun” in schema “dcd”. This function takes two parameters and returns the sum of both.
CREATE OR REPLACE FUNCTION dcd.test_fun
(
num1 IN NUMBER,
num2 IN NUMBER
)
RETURN NUMBER
IS
BEGIN
RETURN num1+num2;
END;
This function once compiled will be created and can be called from SQL statements or other subprograms if created with no errors when compiled.
–Function call from SQL statements
SELECT dcd.test_fun(10, 5) FROM DUAL;
–Function call within other subprogram
DECLARE
var1 NUMBER := 10;
var2 NUMBER := 20;
res NUMBER;
BEGIN
res := dcd.test_fun(10, 5);
END;Q9 : How to create a procedure that takes two variables as input and does the sum of both ? How do you call that procedure?
Ans: Procedures doesn’t have a RETURN keyword. Procedures are meant for huge business transactions.
Below code creates a procedure named as “test_proc” in schema “dcd”. This procedure takes two parameters and adds both the input values.
CREATE OR REPLACE PROCEDURE dcd.test_proc
(
num1 IN NUMBER,
num2 IN NUMBER
)
IS
DECLARE
res NUMBER;
BEGIN
res := num1+num2;
END;
This procedure once compiled will be created and can be called from other subprograms if created with no errors when compiled.
–Procedures can’t be called from SQL statements. So, the below produces error.
SELECT dcd.test_proc(10, 5) FROM DUAL;
–Calling Procedure from other subprogram
DECLARE
var1 NUMBER := 10;
var2 NUMBER := 20;
res NUMBER;
BEGIN
res := dcd.test_proc(10, 5);
END;Q10 : What are the modes for passing parameters to stored Procedure or Function?
Ans: There are three modes for passing parameters to subprograms
IN – An IN-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
OUT – An OUT-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
INOUT – An INOUT parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.
Q11 : What are the different ways to call a Stored Procedure?
Ans: There are three different ways to execute a procedure.
1. From the SQL prompt –
EXECUTE [or EXEC] your_procedure;
2. You can also use CALL statement from the SQL prompt –
CALL your_procedure;
3. Within another subprogram – simply use the procedure name.
your_procedure;
Q12 : What are the different ways to call a Stored Function?
Ans: There are two ways to execute a function.
1. From the SQL prompt –
SELECT function_name() FROM DUAL;
2. You can also use CALL statement from the SQL prompt –
CALL function_name() INTO :<host_variable>;
3. Within another subprogram – assign the function to a variable same data type that the function returns.
SELECT your_function() INTO your_variable FROM DUAL;
or
your_variable := your_function()
Q13 : Can a user-defined be called from SQL statements?
Ans: To be callable from SQL statements, a stored function must obey certain rules.
- If the function is stored in the database
- If the function returns a data type that SQL recognizes. That means if the stored function returns SQL datatype, not the ones available in PL/SQL (like Collection, ROW Type), then it can be accessible.
- If function doesn’t use any DML operations.
- Functions can be used at the right side of the assignment operator/ statement even if the function uses any DML operations.
Q14 : What is the difference between Function and Procedure?
Ans..
| Function | Procedure |
|---|---|
|
The main purpose of a function is to perform certain computations or arithmetic operations and return a single value of some sort. Functions are designed where the Oracles’s inbuilt functions are insufficient to achieve something. |
The main purpose of a procedure is to process complex business requirements. Functions and procedures are structured alike, except that functions return a value. |
|
Functions uses RETURN keyword which enforces always return one value. The value returned can be of any PL/SQL data type. |
Procedure doesn't have RETURN restriction. |
|
Functions can be used as a part or all, of the right side of the assignment statement. i.e. in short wherever you might use a variable, you can use a function. |
Procedures can't be called like functions. |
Q15 : What are the similarities between Function and Procedure?
Ans: Technically anything you do in a Procedure can be done within a function. The only difference is that function returns a value where procedure does not.
Q16 : What is a Package in PL/SQL?
Ans: Package is a PL/SQL construct that allows related objects to be stored together. A package has two separate parts: the specification and the body; each of them is stored separately in the data dictionary.
Q17 : What are the components in a Package?
Ans: Package is a PL/SQL contains following objects.
- Cursor declarations
- Procedures and functions
- Overloaded procedures and functions
- Variables and constants
- Type declarations
Q18 : What is the syntax to create a Package?
Ans: Packages usually have two parts, a specification and package body.
CREATE PACKAGE <your_package_name>
AS
<your_package_variables>;
<your_package_constants>;
PROCEDURE <your_procedure_1>;
PROCEDURE <your_procedure_2>;
.
.
FUNCTION <your_function_1>;
FUNCTION <your_function_2>;
.
.
END <your_package_name>;
The package specification holds public declarations, which are visible to stored procedures and other code outside the package. The body holds implementation details and private declarations, which are hidden from code outside the package.
CREATE PACKAGE BODY <your_package_name>
AS
PROCEDURE <your_procedure_1>
IS
BEGIN
<execution part>
END;
FUNCTION <your_function_1>
RETURN <datatype>
IS
BEGIN
<execution part>
END;
END <your_package_name>;
Q19 : You write all logic in the package body, then why package specification is necessary?
Ans: It is all about encapsulation – Members declared in package specification are public and must be declared in package body and they can be accessed by any program that has the privilege to access the package.
If declaration is skipped in package specification and members are only defined in package body, then they are private members. They can only be accessed within the package.
Q20 : How do you make a Function or Procedure as a Private?
Ans: Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body. Items declared in the body are restricted to be used within the package.
Q21 : What are the advantages of Package?
Ans: Packages support the development and maintenance of reliable, reusable code and have following advantage.
- Modularity: Packages let you encapsulate logically related types, variables, constants, cursors and subprograms in a named PL/SQL module. This makes your application development simple, well defined and modular.
- Easier Application Design: When designing an application, all you need initially is the interface information in the package specs. You need not define the package bodies fully until you are ready. This helps faster application development.
- Information Hiding: With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). The package hides the implementation of the private subprogram so that only the package (not your application) is affected if the implementation changes. This simplifies maintenance and enhancement.
- Enhanced data sharing capability: Packaged public variables and cursors persist for the duration of a session. They can be shared by all subprograms that execute in the environment. They let you maintain data across transactions without storing it in the database.
- Better Performance: When you call a packaged subprogram for the first time, the whole package is loaded into memory. Later calls to related subprograms in the package require no disk I/O.
- Promote overall good coding techniques: stuff that lets you write, code that is modular, understandable, logically grouped together.
Q22 : What are the disadvantages of Package?
Ans: The only disadvantage we can say is its large size depending upon the size of the project.
Q23 : What is the difference between Package and Procedure?
Ans: Following demonstrates the difference between packaged procedure and standalone procedure.
Break the dependency chain with package – No cascading invalidations when you install a new package body.
If you have procedures that call other procedures, compiling one will invalidate the dependent procedures/functions in the database.
Package support function/procedures overloading – Package names have to be unique in a schema, but we can have many procedures across packages with the same name without colliding. Standalone procedures/ functions can not be overloaded.
Support of public and private members – Packages allow declaration of public and private procedures, variables, constants, and cursors.
Objects declared within Standalone procedures are local to the calling block.
Performance Improvement – An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to execute the compiled code already in memory.
Q24 : What is Function Overloading?
Ans: PL/SQL allows to create functions/ procedures with same name within a package. The functions/ procedures differ by:
- The number of arguments
- Type of arguments
- Order of arguments
Two standalone functions can’t be created with the same name. Hence, overloading is possible only if functions or procedures are inside the package. So, packages provide namespace resolution.
Q25 : Can two packaged functions have same name & input parameters but differ only by return data type?
Ans: No. Return type is not considered for overloading.
Q26 : How can you create a table in PL/SQL procedure or function?
Ans: DDL commands are not allowed directly in a PL/SQL block. But by using EXECUTE IMMEDIATE statement we can create a table in PLSQL.
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE SAMPLE AS SELECT * FROM Emp’;
End;
All DDL,DML,DCL commands can be performed by using EXECUTE IMMEDIATE.
Q27 : What is Pass by value and Pass by reference in PL/SQL?
Ans:
Pass by value:
When a parameter is passed by value the PL/SQL runtime engine copies the actual value of the parameter into the formal parameter. Any changes made to the parameter inside the procedure, has no effect on the values of the variables that were passed to the procedure from outside.
The default action is to create a temporary buffer (formal parameter), copy the data from the parameter variable (actual parameter) to that buffer and work on the temporary buffer during the lifetime of the procedure. On successful completion of the procedure, the contents of the temporary buffer are copied back into the parameter variable. In the event of an exception occurring, the copy back operation does not happen.
Pass by reference:
When a parameter is passed by reference the runtime engine sets up the procedure call so that both the actual and the formal parameters point (reference) the same memory location that holds the value of the parameter.
What is default?
By default OUT and IN OUT parameters are passed by value and IN parameters are passed by reference. When an OUT or IN OUT parameter is modified inside the procedure the procedure actually only modifies a copy of the parameter value. Only when the procedure has finished without exception is the result value copied back to the formal parameter.
Q28 : What is NOCOPY?
Ans: Using the NOCOPY hint tells the compiler to use pass by reference, so no temporary buffer is needed and no copy forward and copy back operations happen. Instead, any modification to the parameter values are written directly to the parameter variable (actual parameter).
Syntax: VariableName OUT/ IN OUT NOCOPY Datatype
Q29: You have execute privilege on a procedure but no privilege on any table or view. Can you execute this procedure which modifies one table?
Ans: Yes
Q30 : What happens to a procedure when the referenced table definition is changed?
Ans: A stored procedure is dependent on the objects referenced in its body. Oracle automatically tracks and manages such dependencies. For example, if you alter the definition of a table referenced by a procedure, the procedure goes into INVALID state and must be recompiled to validate that it will continue to work as designed.