How to Define PL/SQL Functions in the WITH Clause

Sometimes a query needs a small calculation that SQL expresses awkwardly, such as turning an interval into minutes, and you do not want to create a stored function for it, or are not allowed to. Oracle lets you declare PL/SQL functions and procedures at the start of a query's WITH clause. They exist only for that query and are called like any SQL function.

This guide shows how to declare and call a WITH function, how to run it in SQLcl and SQL*Plus, and how to use it inside UPDATE and other statements.

Code for This Guide

The main example is in the examples/with-clause folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

It comes from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

with
  function name (parameters) return type is
  begin
    ...
  end;
  [more functions or procedures]
  [name as (subquery) ...]
select ...
/

The PL/SQL declarations come first, then any ordinary common table expressions, then the query. A procedure declared this way can be called from the functions, but only functions can be called from the SQL.

Declare and Call a Function

Subtracting two timestamps gives an INTERVAL DAY TO SECOND. This query declares a function that converts such an interval into whole minutes and uses it to show the scheduled block time of three flights.

Example:

with
  function minutes(p_interval interval day to second) return number is
  begin
    return extract(day from p_interval) * 1440 + extract(hour from p_interval) * 60
           + extract(minute from p_interval);
  end;
select flight_no, minutes(scheduled_arrival - scheduled_departure) as block_minutes
from   flights
where  flight_id in (1, 2, 3)
/

Output:

FLIGHT_NO       BLOCK_MINUTES
____________ ________________
NM128                     423
NM126                     230
NM113                     818

No CREATE FUNCTION was needed, so the query runs for anyone who can query the table, and nothing is left behind in the schema.

End the Statement with a Slash

The function body contains semicolons. SQLcl and SQL*Plus would normally treat the first semicolon as the end of the statement, so a query with a WITH function must end with a slash on its own line, as above, instead of a semicolon after the query.

In application code that sends the statement through a driver, such as JDBC or python-oracledb, there is no client-side parsing, and the statement is sent as one string without the slash.

Use It in UPDATE and Other Statements

A WITH function can also appear in a subquery of an UPDATE, DELETE, INSERT, or MERGE, but then the top-level statement needs the WITH_PLSQL hint. Without it, Oracle refuses the statement:

Example:

update block_times2 b
set    block_minutes = (
  with
    function plus_one(n number) return number is
    begin
      return n + 1;
    end;
  select plus_one(b.flight_id) from dual)
/

Output:

Error starting at line : 1
In command -
update block_times2 b
set    block_minutes = (
  with
    function plus_one(n number) return number is
    begin
      return n + 1;
    end;
  select plus_one(b.flight_id) from dual)
Error at Command Line : 3 Column : 3
Error report -
SQL Error: ORA-32034: unsupported use of WITH clause

With the hint right after the UPDATE keyword, the same kind of statement works. This one fills a column of a small work table, created first with three flights and an empty column.

Setup:

create table block_times as
select flight_id, flight_no, cast(null as number) as block_minutes
from   flights
where  flight_id in (1, 2, 3);

Example:

update /*+ with_plsql */ block_times b
set    block_minutes = (
  with
    function minutes(p_interval interval day to second) return number is
    begin
      return extract(day from p_interval) * 1440 + extract(hour from p_interval) * 60
             + extract(minute from p_interval);
    end;
  select minutes(f.scheduled_arrival - f.scheduled_departure)
  from   flights f
  where  f.flight_id = b.flight_id)
/

select flight_no, block_minutes from block_times order by flight_id;

Output:

3 rows updated.

FLIGHT_NO       BLOCK_MINUTES
____________ ________________
NM128                     423
NM126                     230
NM113                     818

A top-level SELECT does not need the hint. Drop the work table afterward with drop table block_times purge.

When to Use It

• Use a WITH function for logic that one query needs, in reports, ad hoc analysis, or scripts where you cannot create objects.
• Calling a WITH function avoids part of the context switch between SQL and PL/SQL that a call to a stored function costs. For a stored function called from SQL, PRAGMA UDF gives a similar benefit.
• If several queries need the same logic, create a stored function or a package instead, so it is written and tested once.

Related Guides

• How to Use the WITH Clause in Oracle SQL
• How to Write Recursive Queries with Recursive WITH in Oracle

Conclusion

Declare PL/SQL functions at the start of the WITH clause to use procedural logic in one query without creating stored objects. End the statement with a slash in SQLcl and SQL*Plus, add the WITH_PLSQL hint when the function sits inside an UPDATE, DELETE, INSERT, or MERGE, and move the logic into a stored function once several queries need it.