Product Perfect

PL/SQL, which stands for Procedural Language for SQL, is a procedural extension of SQL developed by Oracle Corporation. It is integrated into the Oracle Database and combines the data manipulation power of SQL with the processing power of procedural languages. PL/SQL allows you to create robust and efficient database applications by providing features such as error handling, data encapsulation, array processing, and object-oriented programming. It also supports static and dynamic SQL, cursors, triggers, stored procedures and functions. This language is block-structured and can be used to create standalone programs or can be embedded in other Oracle software.

Ask the right questions secure the right PL/SQL talent among an increasingly shrinking pool of talent.

First 20 minutes

General PL/SQL app knowledge and experience

The first 20 minutes of the interview should seek to understand the candidate's general background in PL/SQL application development, including their experience with various programming languages, databases, and their approach to designing scalable and maintainable systems.

How would you explain a cursor in PL/SQL?

A cursor is a database object used by PL/SQL to retrieve data from a result set one row at a time. It is essentially a named control structure used to manipulate data in a set on a row-by-row basis.

What are the two types of cursors in PL/SQL?

There are two types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit cursors are automatically created by Oracle for each SQL statement, while explicit cursors are created by programmers to handle queries that return multiple rows.

Describe the difference between a function and a procedure in PL/SQL.

The main difference between a function and a procedure in PL/SQL is that a function must return a value, while a procedure does not. A function can be called from SQL statements, whereas a procedure cannot.

How would you handle an exception in PL/SQL?

In PL/SQL, exceptions can be handled using the EXCEPTION block in the PL/SQL block structure. You can either use predefined system exceptions or define your own exceptions, and use the RAISE statement to explicitly raise an exception.

What are the different types of exceptions in PL/SQL?

In PL/SQL, exceptions are divided into three types: Predefined exceptions, Non-predefined exceptions and User-defined exceptions. Predefined exceptions are raised implicitly by the runtime system, while Non-predefined and User-defined exceptions must be raised explicitly by RAISE statements.

The hiring guide has been successfully sent to your email address.

Oops! Something went wrong while submitting the form.

What you’re looking for early on

Does the candidate have a solid understanding of PL/SQL?

Has the candidate demonstrated problem-solving skills?

Is the candidate able to communicate effectively?

Does the candidate have experience with database design and management?

Next 20 minutes

Specific PL/SQL development questions

The next 20 minutes of the interview should focus on the candidate's expertise with specific backend frameworks, their understanding of RESTful APIs, and their experience in handling data storage and retrieval efficiently.

What is the purpose of the PL/SQL packages?

PL/SQL packages are schema objects that group logically related PL/SQL types, variables, and subprograms. They provide modularity, encapsulation, and persistence of declarations, and can improve performance by caching the package information.

How would you declare a variable in PL/SQL?

In PL/SQL, a variable is declared in the declaration section of a block. The syntax is: variable_name datatype [NOT NULL := value ]; For example: v_name VARCHAR2(50);

What are the different types of triggers in PL/SQL?

In PL/SQL, triggers can be classified into Row triggers and Statement triggers, and can also be classified as Before triggers and After triggers. Furthermore, triggers can be event-specific, such as Insert, Update, and Delete triggers.

Describe the difference between a nested table and a varray in PL/SQL.

In PL/SQL, both nested tables and varrays are types of collections. The main difference is that nested tables do not have a maximum size and can be stored in a database column, while varrays have a maximum size and are stored as a single object.

How would you use a bulk collect in PL/SQL?

Bulk collect is a method of fetching data in bulk from the database in PL/SQL. It is used to fetch multiple rows from a database into a collection in a single fetch, reducing the interaction between the PL/SQL and SQL engines and thus improving performance.

The hiring guide has been successfully sent to your email address.

Oops! Something went wrong while submitting the form.

At this point, a skilled PL/SQL engineer should demonstrate strong problem-solving abilities, proficiency in PL/SQL programming language, and knowledge of software development methodologies. Red flags include lack of hands-on experience, inability to articulate complex concepts, or unfamiliarity with standard coding practices.

Digging deeper

Code questions

These will help you see the candidate's real-world development capabilities with PL/SQL.

What does the following PL/SQL code do?
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;
This code prints the string 'Hello, World!' to the console.
What will be the output of the following PL/SQL code?
DECLARE
  num1 NUMBER := 100;
  num2 NUMBER := 200;
BEGIN
  DBMS_OUTPUT.PUT_LINE(num1 || ' + ' || num2 || ' = ' || (num1 + num2));
END;
This code will output '100 + 200 = 300' to the console.
What does the following PL/SQL code do?
DECLARE
  TYPE num_array IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  nums num_array;
BEGIN
  nums(1) := 10;
  nums(2) := 20;
  DBMS_OUTPUT.PUT_LINE('Sum = ' || (nums(1) + nums(2)));
END;
This code declares an associative array, assigns values to it, and prints the sum of those values to the console.
What does the following PL/SQL code do?
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  num NUMBER := 100;
BEGIN
  UPDATE table1 SET column1 = num WHERE column1 IS NULL;
  COMMIT;
END;
This code updates 'column1' in 'table1' to 100 wherever it is NULL, in an autonomous transaction, which means it will be committed independently of the main transaction.

Wrap-up questions

Final candidate for PL/SQL Developer role questions

The final few questions should evaluate the candidate's teamwork, communication, and problem-solving skills. Additionally, assess their knowledge of microservices architecture, serverless computing, and how they handle PL/SQL application deployments. Inquire about their experience in handling system failures and their approach to debugging and troubleshooting.

What is the use of the FORALL statement in PL/SQL?

The FORALL statement in PL/SQL is used for bulk binding, which allows for multiple insert, update, or delete statements to be processed at once. This can significantly improve performance when dealing with large amounts of data.

How would you debug a stored procedure in PL/SQL?

Debugging a stored procedure in PL/SQL can be done using the DBMS_OUTPUT package to print debug information, using EXCEPTION blocks to handle and log errors, or using a PL/SQL IDE that supports debugging, such as SQL Developer or TOAD.

What are the different types of collections in PL/SQL?

In PL/SQL, there are three types of collections: Associative arrays (or index-by tables), Nested tables, and Varrays. Associative arrays are unbounded, index-by binary_integer tables, Nested tables are unbounded tables with a primary key, and Varrays are bounded arrays.

PL/SQL application related

Product Perfect's PL/SQL development capabilities

Beyond hiring for your PL/SQL engineering team, you may be in the market for additional help. Product Perfect provides seasoned expertise in PL/SQL projects, and can engage in multiple capacities.

PL/SQL Consultive assessments

PL/SQL Application Conversion Assessment PL/SQL Application Integration Assessment PL/SQL Data & Database Integration Assessment

Custom PL/SQL development projects

Custom PL/SQL App Conversion PL/SQL Custom App Development Process App UI Design for PL/SQL Facelifts

Hiring guide for PL/SQL Engineers

PL/SQL Developer Hiring Guide

What you’re looking for early on

The ideal back-end app developer

What you’re looking to see on the PL/SQL engineer at this point.

PL/SQL Consultive assessments

Custom PL/SQL development projects