Are you preparing for PL/SQL Interviews, if yes then we are listing here a list of 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.
Amongst other questions during an interview, it is also important to know how SQL and PL/SQL are different. Some benefits of PL/SQL over SQL are:
Apart from the above benefits, PL/SQL has some more attractive features like functions, procedures, triggers, cursors that make it one of the most versatile relational databases.
- What are COMMIT, ROLLBACK and SAVEPOINT statements in PL/SQL? …
- How can you debug your PL/SQL code? …
- What is the difference between a mutating table and a constraining table? …
- In what cursor attributes the outcomes of DML statement execution are saved?
PL/SQL Interview Questions and Answers | Top PL/SQL Interview Questions | Intellipaat
Top Basic and Advanced PL/SQL Interview Questions and Answersby Interview Kickstart Team in
Our tried & tested strategy for cracking interviews
How FAANG hiring process works
The 4 areas you must prepare for
How you can accelerate your learnings
Oracle developed the well-known procedural language PL/SQL, which offers decision-making and iteration capabilities. The demand for PL/SQL developers has increased significantly as more businesses push to implement digital transformation strategies and become data-driven organizations. The time is right for you to advance your career in this industry. We go over some of the most typical PL/SQL interview questions in this article.
Check out our technical interview checklist, interview questions page, and salary negotiation ebook to prepare for technical interviews if you’re a software engineer, coding engineer, software developer, engineering manager, or tech lead.
We’ve trained over 9,000 software engineers, so we understand what it takes to succeed in even the most difficult technical interviews. Averaging a 49% pay raise, FAANG and Tier-1 tech companies have made lucrative offers to Interview Kickstart graduates since 2014. The highest offer ever made to an IK alum was a mind-blowing $933,000!
You have the exceptional chance to learn from knowledgeable instructors at IK who work as hiring managers and tech leads at leading Silicon Valley tech companies like Google, Facebook, Apple, and others. You can learn from our reviews how we’ve helped thousands of professionals launch their careers to new heights. Â.
Sign up for our FREE Webinar if you want to ace your upcoming tech interview.
In this article, we will cover:
What Does a PL/SQL Developer Do?
A PL/SQL developer creates and maintains network interfaces for PL/SQL packages based on fundamental requirements and operational needs. Because of this, PL/SQL developers must have in-depth expertise in building effective databases and applications with optimized infrastructure and navigational options.
Before delivering the finished product, PL/SQL developers must also create instruction manuals and conduct numerous quality assessments to guarantee stable systems. In addition, they are responsible for improving current databases and applications, anticipating potential delays, and fixing them.
You must pass several PL/SQL interview rounds in order to work as a PL/SQL developer in a FAANG company. Review PL/SQL interview questions, technical information, and interview techniques to ace your interview.
Best PL/SQL Interview Questions & Answers
Here are some typical interview questions and their associated responses for both basic and advanced pl/sql.
Answer: A procedural language that allows for block-based execution of code It is an extension of SQL.
Answer:
| SQL | PL/SQL |
| SQL is a query language to interact with the database. | It is an extension of SQL which supports procedures, functions and many more features. |
| Supports only simple queries that can perform insert, update, delete on tables. | Can perform complex tasks like a high-level programming language, for example, while loop, if-else statements, etc… |
| SQL statements can be executed only one at a time, thereby making it a time-consuming process. | The entire block of statements is sent to the database server at once to be executed, saving time and increasing efficiency. |
| No provision for error handling. | Customized error handling is possible. |
Answer:
The answer is that an Oracle cursor is a pointer to a memory location designated for processing SQL statements. Records returned by the SQL query are kept in the cursor. There are 2 types of cursors – implicit and explicit.
The trigger is a stored procedure that is automatically called whenever an event takes place, is the response. The event could be: insert, update, delete, etc… Syntax –.
Answer: Firstly, the syntax check is performed. Oracle binds all the variables holding data with a storage address when the developer fixes any syntax errors. Finally, the p-code generation process takes place.
Answer: PL/SQL offers customized exception handling. An error handling code is built into the program itself to respond to errors. There are 3 types of exceptions –.
Answer: There are many data types –
Answer:
| %TYPE | %ROWTYPE |
| The attribute that declares a variable of the same data type as of a table column. | The attribute that declares a variable of type RECORD having the same structure as a table row. The row is the RECORD that contains fields having the same data types and names as the columns of a table or view. |
Example –
DECLARE studentId students.student_id%TYPE; |
Example –
DECLARE stud_rec students.%ROWTYPE; |
Answer: Packages are schema objects that group together variables, functions, and other elements. Packages should have –.
Answer: Sequences, views, synonyms, stored procedures, functions and packages, database links, triggers, external procedure libraries, etc.
Check here: Basic SQL Commands Checklist
Predefined exceptions are internally defined exceptions that happen while a program is being executed. For instance, PL/SQL raises the error TOO_MANY_ROWS when more than one row is returned by a select statement and NO_DATA_FOUND when no rows are returned upon a select operation. Some more examples:
For the complete list of predefined exceptions, check out Oracle docs.
Answer:
| Syntax error | Runtime error |
| These are compile-time errors found by the compiler. | These are not detected by the compiler and cause the program to give an incorrect result. |
| The code doesnt build and run until these issues are resolved. | The code is compiled and run, and if an error occurs, the program stops halfway. |
| Some examples are missing semicolons or brackets (;, {}), incorrect spelling of classes, keywords etc. | Examples are null pointer exceptions, dividing a number by zero, array index out of bounds, etc. |
| int x = 9 String name = null; In the first line, a semicolon is missing which the compiler will catch | String name = null; if(name.equals(“hackr.io”)){….} Since name is null, the exception will be caught during runtime when the code is executed |
Answer: The several packages available for PL/SQL developers are:
| DBMS_ALERT | alert an application using triggers when particular database values change. The alerts are transaction-based and asynchronous. |
| DBMS_OUTPUT | display output from PL/SQL blocks, packages, subprograms and triggers. Mostly used for displaying PL/SQL debugging information. |
| DBMS_PIPE | different sessions communicate over named pipes using this package. The procedures PACK_MESSAGE and SEND_MESSAGE pack a message into a pipe, then send it to another session. |
| HTF and HTP | allow PL/SQL programs to generate HTML tags. |
| UTL_FILE | lets PL/SQL programs read and write OS text files. |
| UTL_HTTP | allows your PL/SQL programs to make hypertext transfer protocol (HTTP) callouts. The package has two entry points, each of which accepts a URL (uniform resource locator) string, contacts the specified site, and returns the requested data, which is usually in HTML format. |
| UTL_SMTP | allows PL/SQL programs to send emails over SMTP. |
Answer: Character functions are functions that manipulate character data. These are more popularly called as string functions. Example:
| LEFT | Returns the mentioned number of characters from left of a string. LEFT(value, NoOfChars). Example | LEFT(Hackr, 4) will give Hack. |
| RIGHT | return specified number of characters from right. RIGHT(value, NoOfChars). Example | RIGHT(banker, 2) will return er. |
| SUBSTRING | selects data from any part of the string. SUBSTRING(value, StartPosition, NoOfChars). Example | SUBSTRING(hackr.io,0,4) will return hackr. |
| LTRIM | trims white spaces from the left. Example | LTRIM( hackr.io) will return hackr.io. |
| RTRIM | trims white spaces from the right. Example | RTRIM(hackr.io ) will return hackr.io. |
| UPPER | converts all the characters to uppercase. Example | UPPER(hackr.io) returns HACKR.IO. |
| LOWER | converts all the characters to lowercase. Example | LOWER(HACKR.IO) returns hackr.io. |
the local database server’s SYSDATE command returns the current date and time. The syntax is SYSDATE. When only a portion of the date needs to be extracted, we use the TO_CHAR function. Examples:
USER: USER returns the user_id of the current session.
Example:
Answer:
| SGA | PGA |
| System Global Area | Program Global Area |
| Contains data and control information for one Oracle database instance | Contains data and control information exclusively for a single Oracle process |
| Shared memory region for components | Non-shared memory region |
| example: cached data blocks and SQL areas | Example: session memory, SQL work area |
In response, merge minimizes the number of table scans and, if necessary, executes parallel operations. MERGE conditionally updates or inserts data from one table into another. For example,.
In this instance, if a record matching the condition is discovered, the address of that record is updated; otherwise, a new row is added.
Answer: There are many benefits of using PL/SQL packages:
The ROLLBACK command reverses all changes made since the transaction’s start. Only up to a point known as the SAVEPOINT is the transaction rolled back (or undone) in ROLLBACK TO. The actions taken prior to the SAVEPOINT cannot be reversed, and they continue even after the command is given.
Answer:
Procedure for the function Each time it is called for execution, the function is compiled. Procedures are pre-compiled and saved. They execute the pre-compiled code whenever called. Can be called from SQL statements. Can not be called from SQL statements. The function has to return a value. Need not return any value. Generally used for computation purpose. Used for executing complex business logic. Alternatively, if there are other methods available, they can return more than one value. Can return multiple values Returns scalar data types. Returns an int by default. The procedure can call any other function. Functions can be embedded in a select statement, but a procedure cannot be called from within a select statement. It is not possible to handle exceptions Try/catch blocks can be defined inside of procedures
Answer:
| PROCEDURE | TRIGGER |
| Called explicitly by a user, trigger or an application | Executed by the DBMS whenever an event occurs in the database. |
| Can have parameters | Doesn’t have parameters |
| Cannot be inactive | Can be enabled or disabled on need basis |
| Creation – CREATE PROCEDURE | Creation – CREATE TRIGGER |
Answer: There are two types of cursors –
Answer:
Answer:
A constraint on a table is stored along with the definition of the table. TRIGGERS CONSTRAINTS STORED AS SEPARATE OBJECTS Constraints are fired as soon as the table is used, while triggers are fired upon an event. Perform memory location to table comparison, which is slow and results in poor performance, rather than table to table comparison, which is faster. They are just stored procedures that are automatically executed, so they don’t perform a data integrity check because the trigger applies to the entire table and the constraint applies to a table column. Prevent duplicate and invalid data entries .
The declaration, executable, and exception-handling sections make up a PL/SQL block. The executable section is mandatory. There are two types of blocks: named and anonymous.
Functions and processes that are stored in the database server and can be reused are known as named blocks. Anonymous blocks are temporary and aren’t kept on the server. Example:
In records, there are various types of data that can be related to one another as fields. Table-based records, programmer-based records, and cursor-based records are the three types of records that PL/SQL can support.
Answer:
COMMIT – is used to make the database changes permanent. All the save points are erased and the transaction ends. Once committed, a transaction cannot be rolled back.
SAVEPOINT is used to set transactional points that a programmer can later roll back. When a series of transactions can be grouped together with a savepoint, it is advantageous.
The parameters used to call a procedure are referred to as actual parameters, correct? Example –.
Formal parameters are variables that are declared in a procedure header and used in the body. Example –.
Answer: For standalone files that contain anonymous blocks of code that are not stored procedures, functions, or triggers, DECLARE is used as the first statement. Example –.
To track down exceptions that the program doesn’t explicitly handle, use SQLCODE and SQLERRM. These are globally defined variables. While SQLERRM returns the appropriate error message, SQLCODE returns the error code.
The response is that a rollback deletes all database changes, including all savepoints. It ends a transaction.
The command “Rollback to” undoes all changes made up until the savepoint specified in the code. The transaction will still be active.
In the right parameter mode, a value is passed to a subprogram and then returned with an updated value.
Answer: Yes, it is possible. Use ACCEPT keyword to take inputs from the user. Example –.
ACCEPT age number prompt ‘Enter your age:’
Answer: By using ROWID. It is the logical address of a row rather than a physical column. It reduces I/O time and speeds up query execution by containing the block, file, and row numbers.
Answer: ZERO_DIVIDE, NO_DATA_FOUND, TOO_MANY_ROWS, INVALID_CURSOR, DUP_VAL_ON_INDEX etc…
Answer: You can trace through DBMS_* methods like
Use CHAR (NUMBER) to give a variable a fixed length. Example – CHAR (10). The string will be padded with white spaces if its length is less than the desired number.
Using this package, programmers can obtain the code needed to read and write files to and from computers. The developer will require a DBA user’s access authorization to carry out this.
Answer: Both can be used for debugging the code. DBMS_DEBUG prints the output to a log file, while DBMS_OUTPUT prints it to the console.
Answer:
NVL enables a programmer to replace a value with a NULL value. Example –.
Answer: 12 is the maximum number.
Answer: By choosing the proper level of isolation, we can achieve consistency. For instance, the isolation level can be set to READ COMMITTED to provide read consistency.
Answer: Example code –
The end-of-file error on communication channel ORA-03113 indicates that the client and server channels are not connected. It might have been a timeout that caused the connection to be lost. By pinging the server and examining the connectivity, you can troubleshoot.
Answer: In versions 9 and up, we can accomplish this using the DECODE keyword. Example –.
Answer: SYS. All dependencies between packages, procedures, functions, and triggers that are currently accessible to the user are described by ALL_DEPENDENCIES. It shows columns like name, type, dependency_type, referenced_owner, etc….
Topics to Prepare for Your PL/SQL Interview
It is essential to review your fundamental concepts if you want to succeed in the PL/SQL interview. Most of the top PL/SQL interview questions center on the following subjects:
To master these concepts before your next PL/SQL interview, practice core coding problems!
FAQ
What is Advanced SQL interview questions?
- Explain Normalization? …
- Explain the Difference Between DDL and DML? …
- What are ACID Properties in SQL? …
- To find every duplicate in a table, how do I write a query?
- Difference Between a Clustered and non-clustered Index? …
- Explain Denormalisation? …
- What is Collation?
What are the 3 types of PLSQL statements?
Conditional selection statements, loop statements, and sequential control statements are the three types of control statements in PL/SQL.
Is PLSQL still in demand?
Ever since Oracle mentioned PL SQL, it has become a highly sought-after skill.
How is PLSQL Developer interview?
- Q #1) Differentiate PL/SQL and SQL?
- Q #2) Enlist the characteristics of PL/SQL?
- Q #3) What are the data types available in PL/SQL?
- Question #4: Can you explain, using an example, what %TYPE and %ROWTYPE data types are for?
- Q #5) What do you understand by PL/SQL packages?
