PL/SQL, i.e., Procedural Language Extension for SQL, The Programmers use PL/SQL to increase their productivity as it can query data, update data and transform data in the database. PL/SQL saves time on debugging and design by using its powerful features like exception handling, data encapsulation, and object-oriented data types. In simple terms, PL/SQL instructs the compiler “What to do” through Structured Query Language, i.e., SQL, and how to implement it procedurally.
Are you preparing for a PL/SQL interview? Are you in search of a database job? We have done the research and updated this article with the most frequently asked PL/SQL Interview Questions And Answers. Make sure you read the full article so that you will not miss any Interview Questions and Answers. I hope you come across these questions in your interview.
Top 100 PL/SQL Interview Questions And Answers
1. What does a PL/SQL package specification consist Explain?
The PL/SQL package specification is a schema that groups related variables, exception handling, procedural programs, cursors, constants, and subprograms. The PL/SQL packages compile and store in the Oracle database so that other applications can share their content.
The PL/SQL package has two major components, namely:
- Package specification.
- Package body.
2. Can you explain the uses of database triggers?
A database trigger is a schema code executed automatically in response to an event on a specified table or view in a database. We make use of database trigger to maintain the integrity of the data or information on the database. Database triggers provide a robust, customizable database management system. The database triggers complex business rules.
3. Can you explain what SQL stored procedures are?
SQL prepares a code that is saved and reused over and over is called stored procedure. For example, you have written an SQL query that is used repeatedly in your program. In that case, you can save the query as a stored procedure and execute it whenever you need them. The queries are stored in the database. Here the stored procedures are used to validate the data and process a large amount of data at a time.
4. Differentiate between functions, procedures, and packages in PL/SQL?
Function: The PL/SQL function returns a single value. The PL/SQL function has a return type specified. Here the value that the function returns must be of the same type that the data type is.
Based on the return statement, the procedure can either execute the statement or return it to the caller. This process returns multiple values, and it does not have any specific return type like Function.
Package: The package defines a schema object which groups all the related entities and PL/SQL types. Package encapsulates the data.
5. Can you explain the benefits of PL/SQL packages?
PL/SQL is a procedural programming language.
Benefits of PL/SQL packages are:
- It provides security and integrity.
- PL/SQL organizes code management.
- It has an easy application design.
- You can quickly implement the changes because the code changes do not affect the other objects.
- It increases the overall performance.
6. Explain subquery and its types?
Subquery, also called a Nested query, is a query placed within other queries. Here the subquery can return a single value, or it can return entire tables. A subquery gets placed inside an INSERT, UPDATE, DELETE SELECT statement. Subqueries solve other queries.
- Single-row subquery.
- Multiple row subquery.
- Correlated subquery.
7. Can you explain the difference between SQL and PL/SQL?
Differences between SQL and PL/SQL:
|SQL is a Structured Query Language.||PL/SQL(Structured Query Language) is a Procedural Language extension to SQL.|
|SQL encapsulate in PL/SQL.||PL/SQL does not encapsulate in SQL.|
|SQL is a data-oriented language.||PL/SQL is an application-oriented language.|
|SQL query performs single operations.||PL/SQL query performs multiple operations.|
8. Explain Literal with an example? Where can it be used?
Literals initialize variables. It can be a number, string, character. Example String s = “Employee.” Here Employee is literal. Int x =”1”; here, 1 is literal.
9. Name the different methods used to trace the PL/SQL code?
- Use the debug option to trace the PL/SQL code.
- You set the tracing level to the program you are tracing.
10. Define SQL and also describe types of SQL statements?
SQL is defined as a structured query language used by programmers to maintain and process data in a Relational Database Management System. SQL uses queries to retrieve data from the database. SQL statements perform tasks like update data in the database or retrieve data in the database. The queries are stored in the database known as stored procedures.
Types of SQL statements:
- DDL(Data Definition Languages) statements.
- DML(Data Manipulation Languages) statements.
- Transaction Control Statements.
11. Define MERGE statement?
Merge statements combine insert, delete, and update operations into a single statement. Merge statements make changes in a table based on the matched values from another table. The main advantage of a merge statement is, you did not write separate logic. You can implement multiple logic in one single statement and execute.
12. Define a Dual Table in SQL?
ORACLE creates a Dual table. It is a default table in database installations. A dual table has one column named dummy and has a value ‘ X.’ A dual table is a kind of temporary table. A dual table used in SQL queries does not need any data from the table.
PL/SQL Interview Questions And Answers
13. Can you explain the character, number, and date function in detail?
The string in PL/SQL is a sequence of characters with size specifications. Here the characters can be numbers, letters, special characters, blank spaces, or it can be a combination of all these.
The number is a datatype in oracle used to store positive or negative numeric values. The number data type has a precision and a scale. Precision is the digits that range from 1-38 in a number. A scale is a number of digits after a decimal point, and it varies from -84 to 127. Example: 2086.589, here the precision is seven and the scale is 3., to store, you need the number(7,3).
Date functions help to handle date and time efficiently. The most-used date functions in oracle are:
14. Differentiate between Entity, Attribute, and Tuple?
An entity represents a unique object in the real world. W.r.t MYSQL databases store information about specific objects called entities.
Attributes specify the characteristics or properties of an entity in a database table.
A tuple is also called a record which defines a single entry in a table.
15. Explain what a cursor is? Why is it required?
The cursor retrieves the data one row at a time from the result. Cursors update records row by row in a database table. The programmers make use of cursors when they want to retrieve or update in a single shot. The cursor processes the rows individually.
We have three types of cursors, namely,
- Static cursor.
- Dynamic cursors.
- Reference cursor.
16. What is the PL/SQL engine?
PL/SQL engine compiles PL/SQL code into bytecode and executes the preferred executable code. Only the Oracle database server can install the PL/SQL engine.
17. Differentiate between procedures and functions in PL/SQL?
The differences between Procedures and Functions in PL/SQL are:
|Procedures execute specific processes.||Functions perform calculations.|
|The SELECT statement cannot call procedures.||SELECT statement call Functions.|
|To return a value, it uses the‘ OUT’ parameter.||Function use ‘RETURN’ parameter to return the values.|
|For a procedure, it is not necessary to return a value.||The function must return a value.|
18. Explain locking and its types in SQL?
A lock is a process to ensure safeguarding data. SQL Server uses locking to manage transactions adequacy. It provides the integrity of the data in the database.
- Shared lock.
- Exclusive lock.
- Update lock.
- Intent lock.
- Schema lock.
- Bulk update lock.
19. Differentiate between Commit, Rollback, and Savepoint?
Commit: Commit is a SQL control language that saves the changes permanently made by the current transactions.
Rollback is a SQL control language that undoes the changes or reverses the changes made by current transactions. A previous state of the transaction is reached after Rollback.
Savepoint: You can roll back the transaction to a specific or specified point called a Savepoint without rolling back to the entire transaction. A Savepoint is a mechanism of implementing subtransactions in a Relational database Management System.
20. Define a Transaction and describe the common errors that can occur while executing any Transaction?
A transaction is a mechanism of implementing one or more SQL statements. The transaction implies the SQL statements that are committed or rollbacked.
The common errors that would occur while executing any transaction are listed below.
- TP BEGIN: Caused when an attempt to start a transaction.
- TPCANCEL: when a transaction gets called off.
- TPCONNECT: A call made to a service that does not support transactions.
21. Differentiate between USER TABLES and DATA DICTIONARY?
A data dictionary is a group of database tables that store database definition information. The dictionary is loaded with database objects such as tables, data types, views, indexes, and columns.
User-defined tables are the tables in SQL Server, which represent tabular information. These tables are used as parameters in the SQL queries.
22. Name a few major Data Types used in SQL?
- String data types.
- Numeric data types.
- Date and Time data types.
23. Define a Constraint and mention its types?
Constraints in SQL server are the restrictions and rules applied on a column or a table so that only a specific or required data gets entered into the tables.
Constraints classify into two types, namely, Column type constraints and Table type constraints.
Table Type Constraints:
- Not null constraint.
- Check constraint.
- Default constraint.
- Unique Constraint.
- Primary Constraint.
- Foreign Constraint.
24. Can you describe a few restrictions on using the “LONG” data type?
The LONG data type is similar to the VARCHAR2 data type. For LONG data type, the specified maximum size is 32760 bytes.
- The table should contain only one long column.
- You cannot index long columns.
- Long Column should not appear in WHERE clauses.
- Do not create an object with LONG attribute.
- A stored function will not return a long value.
- Long Columns cannot be replicated.
25. Differentiate between truncate and delete?
|Delete is a data manipulation language.||Truncate is a data definition language.|
|Delete maintains the integrity of the data.||Truncate does not maintain the integrity of the data.|
|DELETE function deletes only the specified data.||The truncate function deletes the entire table.|
|Delete returns the no of deleted rows in a table.||It does not return any value.|
26. Differentiate between CHAR and VARCHAR2?
CHAR in SQL is used to store a fixed length of character strings. CHAR is blankly padded upon insertion. Here the string values will be space or padded before storing on discs. Char makes use of the maximum length.
VARCHAR Is a data type that specifies the variable length. If you are using a VARCHAR2 data type, then you need to determine the size also. Here the string values stored on the disc are the values themselves. VARCHAR2 makes use of only the allotted space.
27. Differentiate between simple and complex views?
|Simple view involves only a single base table.||Complex views involve more than one table.|
|No associations are applied since it uses only a single table.||General associations are to be applied since it is using multiple tables.|
|group functions like max(), count(), aggregate() cannot be used.||Complex views make use of group functions as it involves multiple tables.|
|Simple views make use of DML operations.||You cannot apply DML operations.|
|Insert, update, delete operations can be applied.||Insert, update, delete operations cannot be applied.|
|Simple Views do not include NOT NULL columns.||Complex views include NOT NULL columns.|
28. Explain views and their uses?
Views are also called virtual tables. A view can contain a single table or group of tables, depending on the query.
- Views optimize the database.
- Views provide extra security.
- Views provide data analyzing options.
- Views define a table without using extra or unnecessary storage.
29. Define a candidate key?
A candidate key is a group of related attributes which uniquely identifies a record in a table. Every table should be specified with at least one candidate key.
30. Differentiate between unique key and Primary key?
|The primary key cannot accept any null values.||The unique key can accept one null value.|
|The primary key uniquely identifies each row.||The unique key specifies a row, which is not a primary key.|
|A table is specified with only one primary key.||A table can have multiple unique keys.|
|The primary key creates a clustered index.||The unique key creates a non-clustered index.|
PL/SQL Interview Questions And Answers
31. Define a REF CURSOR in PL/SQL?
In PL/SQL, a REF CURSOR is a datatype. The value of this datatype is the memory address of the query. The REF CURSOR passed as a parameter to function or procedure. The REF CURSOR can point to other REF CURSOR available.
32. Explain global variables in PL/SQL?
PL/SQL variables are declared in the declaration section or declared in a package as global variables. If you declare a variable as global, then you can use it throughout the program.
33. Explain Flashback Query?
Flashback query allows the table’s data or contents to be queried concerning or referencing a specified point. Splashback query protects UNDO information.
34. Explain forward declaration?
The forward declaration is a process of declaring a variable or function before its implementation. The importance of ‘Forward declaration’ tells the compiler about the entity’s presence before defining the entity.
35. Name the exceptions in PL/SQL?
The error that occurs during program execution is called an exception. There are two types of exceptions, namely,
- User-defined exception.
- System defined except
36. Differentiate between the Trim, delete collection methods?
Trim removes one or more elements or items from the end of a collection. Trim frees up the space of the drawn elements.
Delete removes only the specified elements from the collection. Delete does not free the space, unlike Trim.
40. Name the command used to delete the package?
You can run Delete Package Command to delete a package.
You can also use the below command to delete the virtual application package.
SFTMIME DELETE PACKAGE:” name.”
In case you want to delete a previously added package of the user, then you can use the below code:
SFTMIME UNPUBLISH PACKAGE:” name.”
41. Can you differentiate between implicit and explicit cursors in PL/SQL?
|Implicit Cursors||Explicit Cursors|
|When the SELECT statement gets executed, then the Implicit cursors are automatically created.||The user has to specify the explicit cursor by giving it a name.|
|Implicit cursors can fetch a row in a single time.||Explicit cursors cannot fetch a row in a single time.|
|Implicit cursors are more prone to errors like data errors.||Explicit Cursors are less prone to errors like data errors.|
|In terms of efficiency, implicit cursors are less efficient.||Explicit cursors or more efficient.|
50. Explain the basic structure of PL/SQL?
The PL/SQL block consists of three main sections.
- Declaration Section: In the declaration section of the PL/SQL block, variables get declared, and memory gets allocated for the cursors and datatypes.
- Execution Section: The PL/SQL block’s execution section starts with the ‘BEGIN’ keyword and ends with the ‘END’ keyword.
- Exception Section: The exception section of the PL/SQL block starts with the keyword ‘EXCEPTION.’ It is the section where exceptions are caught and handled.
In the PL/SQL block, the execution section is mandatory, and the other two blocks, i.e., the Declaration section and Exception Section, are optional.
PL/SQL Interview Questions And Answers
51. What is Invalid_number, Value_Error in PL/SQL?
The Invalid-number and Value-Error are some predefined exception handling in PL/SQL.
Invalid-Number: This exception arises when the compiler fails to convert a character string into a number.
Value-Error: This exception arises when an arithmetic calculation, size constraint, or truncation error occurs.
52. How many triggers can you apply to a table in MYSQL? Explain?
Six triggers apply to a table in MYSQL. They are
- Before Insert.
- After Update.
- After Insert.
- Before Update.
- After Delete.
- Before Delete.
53. List a few cursor attributes in PL/SQL.
The cursor attributes in PL/SQL are:
- %FOUND Attribute: It returns true if it finds any rows.
- %ISOPEN Attribute: It checks for the open cursor.
- %NOTFOUND Attribute: It returns true if it finds no rows.
- %ROWCOUNT Attribute: It returns the number of rows modified, i.e., updated, deleted, or fetched.
54. Explain DBMS_OUTPUT and DBMS_DEBUG?
DBMS_OUTPUT is defined as a built-in package of the PL/SQL that enables the display of output, debug the information, and sends messages from packages, triggers, and subprograms.
DBMS_DEBUG defines the PL/SQL interface to the debugging layer of PL/SQL in the Oracle server. Here the primary intention is to implement server-side debuggers and provides a mechanism to debug PL/SQL server-side program units.
55. What is the purpose of NVL in PL/SQL?
The PL/SQL NVL function lets you substitute a parameter value whenever a null value encounters.
NVL( String1, replace_with)
56. Explain the purpose of the UTL_FILE package in PL/SQL?
UTL_FILE is a package supplied by Oracle PL/SQL, used for ‘Read’ and ‘Write’ file operations in association with underlying OS(Operating Systems). It works for server and client machine systems. You need to create a directory on the server to point to the target file.
57. Explain SYS.ALL_DEPENDENCIES w.r.t PL/SQL?
SYS.ALL_DEPENDENCIES specify dependencies between packages, functions, package bodies, and triggers accessible to the present user, including dependencies on views.
58. Explain the error ORA-03113 w.r.t PL/SQL?
ORA-03113 is a tricky error to fix. ORA-03113 caused due to the presence of a firewall, which can lead to an end of the Oracle connection. This error usually occurs in the process of connecting.
59. How can you run a query faster in PL/SQL?
- First, you need to clarify your goals.
- You need to identify High-Impact SQL statements.
- You need to identify your execution plan.
- Make sure to avoid full table scans, which slows down.
- You need to optimize your SELECT queries.
- Use the best third-party tool.
60. What is an SQL select query?
SQL select statements fetch data from the database. Whenever you use a select query to obtain results, the received data stores in a result table’.
SELECT Name, EMAIL from Employees.
PL/SQL Interview Questions And Answers
61. How is a DECLARE statement used w.r.t PL/SQL? Explain?
In a Declare statement, the variable gets initialized by assigning it a name and a data type.
Declare @Employee_Year SMALLINT
62. Can you tell us about SQLCODE and SQLERRM?
SQLCODE and SQLERRM both are Oracle’s built-in reporting error functions. When a runtime error occurs, SQLCODE returns the number(Last encountered error). SQLERRM, in turn, returns the message related to its error-number argument.
63. Differentiate between actual and formal parameters?
|Actual Parameters||Formal Parameters|
|The values that get passed to a function call are Actual Parameters.||Formal parameters are the parameters used in the function definition statement.|
|Subprogram makes use of Actual parameters.||A called subprogram uses formal parameters.|
|The actual parameter does not specify a data type.||The data type has to be specified.|
|In a function call, the actual parameters get called.||In the function definition, the Formal parameters get called.|
|The parameters here can be variables or constant values.||The parameters here are the local variables of a function.|
64. Differentiate between functions and procedures w.r.t PL/SQL?
Both functions and procedures are PL/SQL statements.
A procedure performs a specific task. Here the procedure may or may not return a value.
A function is similar to procedure w.r.t PL/SQL, but the difference is, the function has to return a value.
65. What are Character Functions?
A character function takes one or more character values as parameters and returns either a number or character value.
The different data types for character data are:
66. Explain SYSDATE? Explain with examples?
SYSDATE specifies the current server system data. SYSDATE is a PL/SQL function, which returns the current system date and time.
SELECT TO_CHAR (sysdate, ‘DD-MM-YYY HH2:MT: SS’) AS System_date_time
The output would be:
System_date_time 05-03-2021 10:09:22
67. Differentiate between %TYPE and %ROWTYPE? Give an example?
%Type is an attribute that lets you define a constant, field, parameter, or variable of the same type as the previously described variable or database column.
DECLARE EmployeeId Employees.Employee_id%Type;
%Rowtype is an attribute that lets you define a record that represents a specific row in a table or view.
68. Name the various predefined exception handling in PL/SQL?
Predefined exceptions define an internal exception that occurs during program execution.
Few Predefined exceptions of PL/SQL are:
69. What is Raise_application_error?
Raise_application_error is a DBMS Standard Package procedure that allows user_defined error messages through database trigger or stored sub-program.
70. Explain the differences between Syntax and runtime errors PL/SQL?
|You cannot execute a syntax error program.||You can execute run time errors with certain conditions specified.|
|They are static errors.||They are dynamic errors.|
|In syntax errors, errors occur due to incorrect syntax.||In dynamic errors, eros occurs due to mistakes in logical coding.|
PL/SQL Interview Questions And Answers
71. How to find an open cursor in the PL/SQL block?
To check the status of the open cursor, you can make use of the %ISOPEN function.
72. Name the two PL/SQL cursor exceptions?
The two types of PL/SQL cursor exceptions are:
73. Can you explain the three major essential parts of a trigger?
The three major parts of a trigger are:
- The Triggering event.
- The restriction.
- The action.
74. Can you explain TTITLE and BTITLE?
The report header and footer gets controlled by ‘TTITLE’ and ‘BTITLE.’
75. Can you explain INTERSECT w.r.t tables?
INTERSECT is a process of comparing two or more tables, and the result lists the matching rows of the tables.
76. Can you explain Number Sequences?
Sequences are defined to generate a number sequence. These number sequences are useful to create unique numbers. For example, the EmployeeID column generates using a number sequence. One of the significant drawbacks of a number sequence is that if a sequence number is lost, the whole transaction must be rolled back.
77. Can you explain the uses of the Control File?
The control file is a type of binary file that records the database structure. The control file contains names, timestamps, and locations of log files.
78. Can you differentiate between Decode and Case statements?
|Decode Statement||Case Statement|
|Decodes are used only inside SQL functions.||The PL/SQL blocks make use of CASE statements.|
|It is a system function.||It is a statement.|
|They cannot work with subqueries and predicates.||They can work with subqueries and predicates.|
|Stored procedures cannot use Decode functions.||Stored procedures can make use of Code statements.|
79. Can you explain data consistency?
Consistency w.r.t database systems relate to the requirement that any given data transaction should change the specified data only without affecting the other parts of the database.
80. Can you differentiate between Anonymous blocks and sub-programs?
|Anonymous blocks cannot get stored in the database.||Subprograms get saved in the database.|
|Anonymous blocks cannot get called.||Subprograms get called in a program.|
|They have to get compiled each time we call anonymous blocks.||There is no need for com[pilation each time a subprogram gets called.|
|They get created on the client-side.||They get stored on the server.|
|They do not return any value.||The subprograms that call functions have to return some value.|
PL/SQL Interview Questions And Answers
81. Differentiate between SGA and PGA?
|System Global Area||Program Global Area.|
|It occupies 40% of the RAM size.||It occupies 10% of the RAM size.|
|Here the memory is assigned at the time of declaring an instance,||It is a memory area that reserves the user-session information.|
82. Where are Predefined functions stored?
The predefined functions get stored in the standard packages called “Functions, Procedures, and Packages.”
83. Can you explain Polymorphism?
Polymorphism is a concept of Object-oriented language, and Polymorphism defines the object’s ability to take multiple forms.
83. Explain the Module procedure in PL/SQL?
Module procedure is converting all code lines into a definite program that consists of at least one procedural call.
The three arguments of a Module procedure are:
84. What is the use of PLVprs?
PLVprs is a string parsing functionality. It is a package provided by PL/SQL for the parsing of strings.
85. Are CREATE commands supported by PL/SQL?
The PL/SQL does not support the commands like CREATE.
86. What is a procedure in PL/SQL?
A procedure is a PL/SQL program or subprogram that does not return any direct value. Procedures in PL/SQL perform a specific action.
87. What is error handling?
Error handling defines a program that responds to unusual inputs and conditions. In error handling, the users are given a chance to resolve the problem.
88. What is P-code?
In Oracle, P-code is a pre-compiled code stored in the global area’s public cache memory when an oracle instance is defined. The Oracles every session access the p-code that has executable permissions of an object.
89. What are Named blocks in PL/SQL?
The PL/SQL blocks have a header or labels called Named blocks. The Named blocks can be subprograms like triggers, packages, functions, or procedures.
90. Define SQL?
SQL is defined as a domain-specific programming language used to manage data in a relational database management system. Here the SQL statements are used to do specific tasks such as update or add new data to the database.
91. Explain DML Statements?
DML stands for Data Manipulation Language. DML statement is a subset of SQL statement that modifies the data stored in the tables. DML statements include deleting the data, updating the data in the tables, or reversing transactions. Examples of DML statements are SELECT, DELETE, UPDATE, and INSERT.
92. Explain SQL statement?
Structured Query Language, i.e., SQL statements, are the queries used to perform specific tasks like updating the data on a database or retrieving the data from a database.
93. Explain Formal parameter declaration?
The formal parameter declaration appears as follows:
- Formal parameters appear in “Function Declaration and Definition.”
- They are specified in “Procedure declaration and Definition.”
- They can also appear in “ CREATE FUNCTION and CREATE PROCEDURE” statements.
94. Explain Schema objects with examples?
Logical structures are created by users called schema objects.
Examples of Schema objects are listed below:
- Database links.
95. What are control structures in PL/SQL?
Procedural languages use control structures. The selection structure testing a particular condition executes one sequence of statements instead of another statement, depending on true or false condition. Here the condition is an expression that returns a boolean value, i.e., true or false.
The three types of control structures in PL/SQL are:
- Conditional Selection statements, for example, IF and CASE.
- Loop statements, example LOOP, FOR LOOP, WHILE LOOP.
- Sequential Control Statements, example GOTO and NUL
96. Name the datatypes available in PL/SQL?
The two primary data types in PL/SQL are:
- Composite data types: These data types store multiple values, for example, Collection, Record.
- Scalar data types: these data types store single values like boolean, character. Examples are String, Integer, double, etc.
97. What does fetching a cursor and closing a cursor do?
The content of the current row is placed into the variables by a FETCH statement. Here the cursor reads the result set row by row.
The private SQL area is cleared, and the memory gets deallocated using the Close cursor.
98. Explain Autonomous transactions in PL/SQL?
If the PL/SQL block is defined as autonomous transactions, then the DML is separated from the block caller’s transaction context. Here the block becomes an independent transaction. The primary transaction is suspended from the transaction block. By performing SQL operations, you resume the prior transaction.
The syntax for Autonomous transactions.
99. Can you execute two queries simultaneously in a Distributed Database System?
The answer is yes. You can execute two queries simultaneously in a distributed database system. Here the first query is independent of the second query.
100. Can you tell us a function to transfer a PL/SQL table log to a database table?
PROCEDURE ps2db is used to transfer a PL/SQL table log to a database log table function.
Best of luck with your PL/SQL Interview, and I hope our PL/SQL interview questions and answers were of some help to you.