Mastering Oracle PL/SQL: Advanced Concepts and Techniques

12 min readJun 11, 2023

Introduction

Oracle PL/SQL, a versatile tool for database management, blends perfectly with SQL. In our previous articles, we explored its basics and intermediate aspects, laying a firm foundation for understanding this complex language.

Now, we’re ready to delve into advanced Oracle PL/SQL. This article covers advanced cursor usage, complex exception handling, native compilation, dynamic SQL, and Oracle-supplied packages. We’ll also explore performance-tuning techniques, SQL*PLUS interactions, and database-level triggers.

With practical examples, we aim to unlock the advanced capabilities of Oracle PL/SQL for both experienced developers and eager beginners. Let’s explore the potential of advanced Oracle PL/SQL together.

Let’s dive in!

Advanced Cursors Usage

Introduction to advanced cursors

In Oracle PL/SQL, a cursor is a database object that allows you to retrieve multiple rows of data and manipulate them as you would in a set-based operation. Beyond the simple explicit cursors, we have advanced cursor concepts like parameterized cursors, REF cursors, and cursor variables that bring more flexibility and dynamism to your operations.

Parameterized Cursors

Parameterized cursors, also known as cursors with parameters, allow you to pass parameters into cursors and make them more flexible. You can use these parameters in the WHERE clause to filter the results of the cursor.

Here is a simple example of a parameterized cursor:

DECLARE 
   CURSOR c_emp (p_deptno NUMBER) 
   IS 
      SELECT ename, job FROM emp WHERE deptno = p_deptno; 
   
   v_ename emp.ename%TYPE; 
   v_job emp.job%TYPE; 
BEGIN 
   OPEN c_emp(20); 
   LOOP 
      FETCH c_emp INTO v_ename, v_job; 
      EXIT WHEN c_emp%NOTFOUND; 
      DBMS_OUTPUT.PUT_LINE(v_ename || ' - ' || v_job); 
   END LOOP; 
   CLOSE c_emp; 
END; 
/

In this example, the cursor c_emp takes a parameter p_deptno. When we open the cursor, we pass a value to this parameter to retrieve employees from the specific department number 20.

REF Cursors

REF cursors or cursor variables are more flexible than static cursors. Unlike static cursors, which are fixed at compile time, REF cursors can be opened for any query at runtime, making them highly flexible for programs that need to compile queries dynamically.

Here is a simple example of a REF cursor:

DECLARE 
   TYPE emp_ref IS REF CURSOR; 
   c_emp emp_ref; 
   v_ename emp.ename%TYPE; 
   v_job emp.job%TYPE; 
BEGIN 
   OPEN c_emp FOR SELECT ename, job FROM emp WHERE deptno = 20; 
   LOOP 
      FETCH c_emp INTO v_ename, v_job; 
      EXIT WHEN c_emp%NOTFOUND; 
      DBMS_OUTPUT.PUT_LINE(v_ename || ' - ' || v_job); 
   END LOOP; 
   CLOSE c_emp; 
END; 
/

In this example, we define a REF cursor type emp_ref and declare a cursor variable c_emp of that type. We then open the cursor variable for a query to retrieve employees from department number 20.

Mastering these advanced cursor concepts can significantly enhance your capability to handle and manipulate data in Oracle PL/SQL.

Advanced Exception Handling

Introduction to advanced exception handling

Exception handling is a crucial part of any PL/SQL program. It allows developers to anticipate and address errors or exceptions that may arise when the program runs. While you’ve already seen basic error handling, advanced exception-handling techniques provide more control and flexibility in dealing with unexpected situations.

User-Defined Exceptions

In addition to Oracle’s predefined exceptions, PL/SQL allows developers to define and raise their own exceptions. This is especially useful when you want to check for a specific condition and raise an error if that condition is met.

Here’s an example of user-defined exceptions:

DECLARE
  emp_count INTEGER;
  too_many_employees EXCEPTION;
BEGIN
  SELECT COUNT(*) INTO emp_count FROM emp WHERE deptno = 20;
  IF emp_count > 50 THEN
    RAISE too_many_employees;
  END IF;
EXCEPTION
  WHEN too_many_employees THEN
    DBMS_OUTPUT.PUT_LINE('Error: Too many employees in department 20.');
END;
/

In this example, a user-defined exception too_many_employees is raised when there are more than 50 employees in department 20.

Propagating Exceptions

Sometimes you might want an exception to be handled in a different block from where it was raised. In such cases, you can use exception propagation.

When an exception is raised and not handled in the current block, it propagates to the enclosing (or calling) block. This continues until the exception is either handled or reaches the outermost block. If the exception is not handled anywhere, the program terminates with an unhandled exception error.

Here’s an example:

DECLARE
  value_null EXCEPTION; 
  PRAGMA EXCEPTION_INIT(value_null, -1476); 
BEGIN
  -- This will cause an exception
  DECLARE
    a number := 0;
    b number := 1/a;
  EXCEPTION
    WHEN value_null THEN
       dbms_output.put_line('A value is null!');
END;

In this example, the exception is propagated from the inner block to the outer block, where it is handled.

Exception Information Functions

Oracle provides functions such as SQLCODE and SQLERRM that return useful information about exceptions. SQLCODE returns the numeric code of the exception, and SQLERRM returns the message associated with the exception.

Here’s an example that uses these functions:

DECLARE
  a number := 0;
  b number := 1/a;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Error code ' || SQLCODE || ': ' || SQLERRM);
END;
/

In this example, when an exception is raised, the error code and error message are outputted using the SQLCODE and SQLERRM functions, respectively.

Advanced exception handling in PL/SQL offers more control over the error management process, making your programs more robust and reliable.

Working with Oracle Supplied Packages

Introduction to oracle supplied packages

Oracle provides a set of precompiled object PL/SQL units known as Oracle Supplied Packages. These packages are built into Oracle Database and provide a wide range of functionality, from outputting messages to the screen, file I/O, job scheduling, to sending email and much more.

Let’s examine a few key Oracle Supplied Packages:

DBMS_OUTPUT Package

The DBMS_OUTPUT package enables you to display output, debug information, or send messages from PL/SQL blocks, subprograms, packages, and triggers.

Here’s a basic example:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;
/

In this example, ‘Hello, World!’ is displayed on the screen using the PUT_LINE procedure of the DBMS_OUTPUT package.

UTL_FILE Package

The UTL_FILE package allows your PL/SQL programs to read and write operating system text files. It provides a restricted version of standard operating system stream file I/O.

Here’s a simple example of writing to a file:

DECLARE
  file_handle UTL_FILE.FILE_TYPE;
BEGIN
  file_handle := UTL_FILE.FOPEN('MY_DIR', 'my_file.txt', 'W');
  UTL_FILE.PUT_LINE(file_handle, 'Hello, World!');
  UTL_FILE.FCLOSE(file_handle);
EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(file_handle) THEN
      UTL_FILE.FCLOSE(file_handle);
    END IF;
    RAISE;
END;
/

In this example, we use the UTL_FILE package to write ‘Hello, World!’ to a text file named ‘my_file.txt’ located in the directory ‘MY_DIR’.

DBMS_SCHEDULER Package

DBMS_SCHEDULER is a more advanced and flexible job scheduler than the older DBMS_JOB. It allows you to schedule jobs (i.e., PL/SQL anonymous blocks, PL/SQL stored procedures, and Java stored procedures) to run once at a specified time/date or at regular intervals.

Here’s a basic example of scheduling a job:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'my_test_job',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_OUTPUT.PUT_LINE(''Hello, World!''); END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
    enabled => TRUE);
END;
/

In this example, we create a job named ‘my_test_job’ that outputs ‘Hello, World!’ every 5 minutes starting immediately.

Working with Oracle-supplied packages can greatly increase your productivity by providing pre-built solutions to common requirements, from sending emails with UTL_MAIL to handling large objects with DBMS_LOB and much more. Their utilization can make your PL/SQL programming more efficient and effective.

Performance Tuning in PL/SQL

Introduction to performance tuning

Performance tuning is a vital aspect of PL/SQL programming. It ensures that your applications are responsive and can handle high workloads efficiently. While various optimization techniques exist, we will focus on some practical strategies that can have a significant impact on your application’s performance.

Optimizing SQL Statements

SQL is the language for interacting with the database in Oracle, and as such, it’s crucial that your SQL performs well. The Explain Plan tool in Oracle can provide insight into how your SQL statements are being executed, which can guide your tuning efforts. Proper indexing, choosing the correct SQL hints, and using partitioning where appropriate can significantly enhance your SQL performance.

Bulk Processing with BULK COLLECT and FORALL

These features of PL/SQL allow for “bulk” operations, which can significantly speed up the processing of large amounts of data. By reducing context switching between the PL/SQL and SQL engines, these features can dramatically improve performance.

DECLARE
   TYPE t_emp_tab IS TABLE OF employees%ROWTYPE;
   emp_tab t_emp_tab;
BEGIN
   SELECT *
   BULK COLLECT INTO emp_tab
   FROM employees;

   FOR i IN emp_tab.FIRST .. emp_tab.LAST LOOP
      emp_tab(i).salary := emp_tab(i).salary * 1.05;  -- Give a 5% raise
   END LOOP;

   FORALL i IN emp_tab.FIRST .. emp_tab.LAST
      UPDATE employees SET row = emp_tab(i) WHERE employee_id = emp_tab(i).employee_id;
   
   COMMIT;
END;
/

Using Bind Variables

Bind variables can help the database reuse execution plans, improving the performance of SQL statements within your PL/SQL code. Without bind variables, even similar queries with different literals would be considered different by the database, leading to multiple execution plans and reducing efficiency.

DECLARE
   v_employee_id employees.employee_id%TYPE := 100;
   v_first_name employees.first_name%TYPE;
BEGIN
   SELECT first_name INTO v_first_name FROM employees WHERE employee_id = v_employee_id;
   DBMS_OUTPUT.PUT_LINE('First name: ' || v_first_name);
END;
/

PL/SQL Optimizer

Introduced in Oracle 10g, the PL/SQL optimizer automatically tries to improve the performance of PL/SQL code. This feature can be leveraged by setting the optimization level to a higher setting:

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;

Avoiding Unnecessary Computations

One simple but effective strategy is to avoid unnecessary computations. If you find that your code is doing the same computation in multiple places, consider doing the computation once, storing the result, and then reusing that result as needed.

DECLARE
   v_base_salary employees.salary%TYPE := 5000;
   v_bonus employees.salary%TYPE := v_base_salary * 0.1;  -- Compute once
BEGIN
   -- Use the pre-computed bonus repeatedly
   INSERT INTO bonuses (employee_id, bonus) VALUES (100, v_bonus);
   INSERT INTO bonuses (employee_id, bonus) VALUES (101, v_bonus);
   INSERT INTO bonuses (employee_id, bonus) VALUES (102, v_bonus);
END;
/

Using Tools for Performance Analysis

Oracle provides tools such as TKPROF and AUTOTRACE, which can be used to analyze the performance of your SQL statements and identify bottlenecks. These tools give detailed reports on the execution of your SQL and PL/SQL code, making it easier to identify areas that could benefit from performance tuning.

Tuning performance is an iterative process. After one set of bottlenecks is resolved, others may become evident. However, understanding these techniques and properly implementing them can greatly enhance the speed and efficiency of your PL/SQL code. It is crucial to keep exploring and learning more about performance tuning, as it can make a significant difference in your Oracle Database applications’ efficiency.

PL/SQL and SQL*PLUS Interaction

Introduction to SQL*Plus

SQL*Plus is an interactive and batch query tool that comes with every Oracle Database installation and provides SQL, PL/SQL and scripting functionality. It is an important tool for interacting with the Oracle Database, and understanding how it interacts with PL/SQL can be beneficial.

Executing PL/SQL Blocks

PL/SQL anonymous blocks can be directly executed in SQL*Plus. Here’s an example of declaring a variable, assigning it a value, and then outputting it:

DECLARE
   v_message VARCHAR2(50) := 'Hello, SQL*Plus!';
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

Remember to use the forward slash (/) to execute a PL/SQL block in SQL*Plus.

Using SQL*Plus Variables in PL/SQL Blocks

You can define variables in SQL*Plus and then reference them in your PL/SQL blocks:

DEFINE v_message = 'Hello, SQL*Plus!'

DECLARE
   v_local_message VARCHAR2(50) := '&v_message';
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_local_message);
END;
/

In this example, the &v_message in the PL/SQL block is a substitution variable that gets replaced by the value of v_message defined in SQL*Plus.

Using the SET SERVEROUTPUT Command

By default, output from the DBMS_OUTPUT package is not displayed in SQL*Plus. To enable this, you need to use the SET SERVEROUTPUT ON command:

SET SERVEROUTPUT ON

DECLARE
   v_message VARCHAR2(50) := 'Hello, SQL*Plus!';
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

Executing Stored Procedures and Functions

Stored procedures and functions can also be executed directly from SQL*Plus. For procedures, you can simply call the procedure:

EXECUTE procedure_name(arguments);

For functions that return a value, you can select from the function:

SELECT function_name(arguments) FROM dual;

Scripting in SQL*Plus

SQL*Plus can also run scripts, which are simply text files with a list of SQL*Plus, SQL and PL/SQL commands. You can use this feature to execute a series of commands all at once.

For example, you can create a script file called script.sql with the following content:

SET SERVEROUTPUT ON

DECLARE
   v_message VARCHAR2(50) := 'Hello, SQL*Plus!';
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_message);
END;
/

You can then execute this script in SQL*Plus with the following command:

@script.sql

Mastering the interaction between SQL*Plus and PL/SQL can help you get the most out of Oracle Database and simplify your database development process.

PL/SQL Native Compilation

Oracle offers the ability to natively compile PL/SQL code into machine code, which can significantly improve the execution performance of PL/SQL programs. Native compilation is the process by which PL/SQL source code is compiled into a shared library in the form of a DLL on Windows or a SO (Shared Object) on Unix/Linux systems.

Here’s how you can enable and use PL/SQL native compilation:

Enabling Native Compilation

To use native compilation, you need to enable it at the system level. You can use the following command:

ALTER SYSTEM SET PLSQL_CODE_TYPE = 'NATIVE';

This command will make Oracle compile all new PL/SQL code natively.

Compiling Existing Code

If you want to compile existing PL/SQL code natively, you can use the following command:

ALTER PROCEDURE procedure_name COMPILE PLSQL_CODE_TYPE = NATIVE;

Replace procedure_name with the name of the procedure, function, package, or trigger you want to compile.

Verifying the Compilation

You can verify whether your PL/SQL code is compiled natively or interpreted with the following query:

SELECT name, type, plsql_code_type 
FROM user_code 
WHERE name = 'PROCEDURE_NAME';

Again, replace PROCEDURE_NAME with the name of your procedure, function, package, or trigger. The PLSQL_CODE_TYPE column will show NATIVE if the code is compiled natively and INTERPRETED if not.

Keep in mind that while native compilation can improve the execution speed of your PL/SQL code, it doesn’t affect the performance of SQL statements within your PL/SQL code. The performance of SQL statements is determined by the Oracle SQL optimizer.

Understanding and properly using PL/SQL native compilation can help you optimize your Oracle Database applications’ performance.

Advanced Database Triggers

Database triggers are named PL/SQL blocks that are stored in the database and are automatically executed (fired) when certain events occur. In previous articles, we have discussed simple triggers that are associated with DML events (insert, update, delete) on a particular table. However, there are more advanced types of triggers that you can utilize to enforce complex business rules or enhance the functionality of your database.

Instead of Triggers

These triggers are defined on views, and they fire instead of the triggering event. They can be used to modify the data in the base table when a DML operation is performed on the view.

Here’s an example of an instead of trigger:

CREATE OR REPLACE TRIGGER io_emp_view
INSTEAD OF INSERT ON emp_view
FOR EACH ROW
BEGIN
   INSERT INTO employees (employee_id, first_name, last_name)
   VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name);
END;
/

This trigger would insert data into the employees table whenever an insert is performed on the emp_view view.

System Event Triggers

These triggers fire when a system event occurs. The most common types of system events are logon and logoff events, server errors, startup or shutdown of the database, etc.

Here’s an example of a logon trigger:

CREATE OR REPLACE TRIGGER logon_audit
AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO logon_audit (username, logon_time)
   VALUES (USER, SYSDATE);
END;
/

This trigger would insert a record into the logon_audit table each time a user logs on to the database.

DDL Triggers

These triggers fire in response to DDL (Data Definition Language) statements like CREATE, ALTER, DROP, etc. They can be useful for tracking changes to the database schema.

Here’s an example of a DDL trigger:

CREATE OR REPLACE TRIGGER ddl_audit
AFTER DDL ON DATABASE
BEGIN
   INSERT INTO ddl_audit (username, timestamp, operation)
   VALUES (USER, SYSDATE, ora_sysevent);
END;
/

This trigger would insert a record into the ddl_audit table each time a user performs a DDL operation.

Advanced database triggers allow you to implement sophisticated logic in response to a variety of database events, enhancing the power and flexibility of your Oracle Database applications. Understanding these types of triggers can help you manage and control your database more effectively.

Dynamic SQL in PL/SQL

Introduction to Dynamic SQL

Dynamic SQL is a technique used to build SQL statements dynamically at runtime. This is particularly useful when you need to perform an operation, but you don’t know in advance which table or which columns you’ll be working with. Dynamic SQL is implemented in PL/SQL through the EXECUTE IMMEDIATE statement and the DBMS_SQL package.

EXECUTE IMMEDIATE

The EXECUTE IMMEDIATE statement allows you to execute a dynamic SQL statement or anonymous PL/SQL block.

Here’s an example of how you could use EXECUTE IMMEDIATE to insert data into a table:

DECLARE
   v_table_name  VARCHAR2(30) := 'employees';
   v_sql         VARCHAR2(1000);
BEGIN
   v_sql := 'INSERT INTO ' || v_table_name || ' VALUES (100, ''John'', ''Doe'')';
   EXECUTE IMMEDIATE v_sql;
END;
/

In this example, the SQL statement is constructed dynamically using the v_table_name variable, and then executed with EXECUTE IMMEDIATE.

DBMS_SQL Package

The DBMS_SQL package provides a more advanced interface for dynamic SQL, allowing you to parse any kind of SQL statement and bind and define placeholders.

Here’s an example of how you could use the DBMS_SQL package to execute a dynamic SELECT statement:

DECLARE
   v_cursor   NUMBER;
   v_count    NUMBER;
   v_sql      VARCHAR2(1000);
BEGIN
   v_sql := 'SELECT COUNT(*) FROM employees WHERE department_id = :dept_id';
   
   v_cursor := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(v_cursor, v_sql, DBMS_SQL.NATIVE);
   DBMS_SQL.BIND_VARIABLE(v_cursor, ':dept_id', 10);
   
   DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_count);
   DBMS_SQL.EXECUTE_AND_FETCH(v_cursor, v_count);
   
   DBMS_OUTPUT.PUT_LINE('Count: ' || v_count);
   
   DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

In this example, the DBMS_SQL package is used to parse the SQL statement, bind a value to the :dept_id placeholder, define a variable to hold the result of the SELECT statement, execute the statement and fetch the result, and finally close the cursor.

Dynamic SQL can be a powerful tool in your PL/SQL programming toolkit, allowing you to write flexible and adaptable code that can perform operations based on conditions that are only known at runtime. However, always be aware of the potential risks of SQL injection and take appropriate precautions when constructing dynamic SQL statements.

Conclusion

In this article, we ventured further into the depths of Oracle PL/SQL, focusing on advanced topics such as complex cursor usage, sophisticated exception handling, Oracle-supplied packages, performance tuning, SQL*Plus interaction, native PL/SQL compilation, intricate database triggers, and dynamic SQL. The insights and examples provided should have broadened your understanding and proficiency in Oracle PL/SQL, equipping you to handle more complex scenarios with aplomb.

As you continue to dissect and master these topics, your capability to efficiently manipulate and manage Oracle databases will undoubtedly skyrocket. Stay tuned for subsequent articles in this series, as we continue to unravel more complex themes, illustrate them with practical examples, and provide valuable insights to propel your Oracle PL/SQL development skills to greater heights.

Master Coding: Read, Analyze, Implement, Repeat