When the Obscure Becomes Essential: A Function Inside a WITH Clause in Oracle SQL

Recently, we found ourselves working on a data migration project that required not only moving data, but transforming it based on complex conditions and external inputs. That’s when we turned to an often-overlooked feature of Oracle SQL: the WITH FUNCTION clause. This small but powerful bit of syntax made the task significantly easier and, just as importantly, kept our migration scripts clean, readable, and maintainable, without any temporary objects needing to be installed.

Introduced in 12c this is an almost 13 year old feature however has been vastly underutilized. It can massively reduce the complexity of your queries and make them much easier to read. It also has a few common pitfalls and caveats so make sure you read on to learn how to use this feature effectively.

How do you use it?

Most Oracle developers are familiar with using WITH clauses to make queries easier to read, but Oracle also allows you to define a PL/SQL function directly inside the WITH block. This function only exists for the lifetime of that query, meaning you can use reusable logic without creating any permanent database objects like packages or standalone functions.

The syntax is very straightforward. You define the function first inside the WITH clause, then use it throughout the query just like any normal Oracle function:

WITH
   FUNCTION normalize_phone (p_phone VARCHAR2)
       RETURN VARCHAR2
   IS
   BEGIN
       RETURN REGEXP_REPLACE(p_phone, '[^0-9]', '');
   END;
SELECT
   normalize_phone(phone)
FROM users;

This is particularly useful when the same logic is repeated multiple times in a query, such as validation, sanitisation, or complex calculations. Instead of duplicating expressions everywhere, you centralise the logic into a single reusable function, making the query cleaner and easier to maintain.

Common Pitfalls

One important caveat is exception handling. If an unhandled exception occurs inside the inline function, Oracle can stop returning rows partway through the query. Because of this, it is good practice to handle exceptions properly inside the function, especially when working with messy or unpredictable data during migrations or reporting tasks.

One subtle syntax detail to be aware of when combining inline functions with inline views in a WITH clause is that they are separated differently. Inline functions use the normal PL/SQL terminator, a semicolon (;), whereas inline views are separated using commas. Because of this, any inline functions must be declared before your inline views. The final inline function is terminated with a semicolon, and the next line begins directly with the inline view name; there is no comma between them. For example:

WITH
    FUNCTION now_date_fn RETURN DATE IS
    BEGIN
        RETURN SYSDATE;
    END;
    now_date AS (
        SELECT SYSDATE AS dt FROM dual
    ),
    ystrdy_date AS (
        SELECT TRUNC(SYSDATE) - 1 AS dt FROM dual
    )
SELECT
    n.dt,
    y.dt,
    now_date_fn()
FROM now_date n
CROSS JOIN ystrdy_date y;

This difference can be easy to miss because the syntax looks similar to a normal WITH clause, but the parser treats the PL/SQL function declarations and SQL subqueries as separate sections.

Why should you use it?

This is a very simple query that gets a normalised phone number from the database, the database might contain bad data and we just want to get the sanitised numbers. This means that the REGEXP_REPLACE logic is copied over 4 times in the query. This goes against the DRY programming principle (don’t repeat yourself) and means if you need to update the regex, you have to change it in 4 places.

SELECT
    u.user_id,
    REGEXP_REPLACE(u.phone, '[^0-9]', '') AS normalized_phone
FROM users u
JOIN sms_subscribers s
    ON REGEXP_REPLACE(u.phone, '[^0-9]', '') =
       REGEXP_REPLACE(s.phone, '[^0-9]', '')
WHERE
    LENGTH(REGEXP_REPLACE(u.phone, '[^0-9]', '')) = 10;

This problem can be solved by using the inline function, having the inline function also allows for standard PL/SQL code and logic, this makes it more powerful to do complex validation, perfect for sanitising a phone number.

WITH
    FUNCTION normalize_phone (p_phone VARCHAR2)
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN REGEXP_REPLACE(p_phone, '[^0-9]', '');
    END;
SELECT
    u.user_id,
    normalize_phone(u.phone) AS normalized_phone
FROM users u
JOIN sms_subscribers s
    ON normalize_phone(u.phone) = normalize_phone(s.phone)
WHERE
    LENGTH(normalize_phone(u.phone)) = 10;

Another useful example on when you should use it is for complex calculations and reports that are infrequently run. In this case, it does not make sense to make a package as a self-contained SQL script can handle all of it for you. In this example, I require a SQL query that gets the total in the last 7, 30 and 90 days. The standard query looks complex and has lots of repetition:

SELECT
    CASE
        WHEN t.txn_date >= :as_of_date - 7  THEN 'LAST_7_DAYS'
        WHEN t.txn_date >= :as_of_date - 30 THEN 'LAST_30_DAYS'
        WHEN t.txn_date >= :as_of_date - 90 THEN 'LAST_90_DAYS'
        ELSE 'OLDER'
    END AS recency_bucket,
    COUNT(*) AS txn_count,
    SUM(t.amount) AS total_amount
FROM transactions t
WHERE
    CASE
        WHEN t.txn_date >= :as_of_date - 7  THEN 'LAST_7_DAYS'
         WHEN t.txn_date >= :as_of_date - 30 THEN 'LAST_30_DAYS'
        WHEN t.txn_date >= :as_of_date - 90 THEN 'LAST_90_DAYS'
        ELSE 'OLDER'
    END <> 'OLDER'
GROUP BY
    CASE
        WHEN t.txn_date >= :as_of_date - 7  THEN 'LAST_7_DAYS'
        WHEN t.txn_date >= :as_of_date - 30 THEN 'LAST_30_DAYS'
        WHEN t.txn_date >= :as_of_date - 90 THEN 'LAST_90_DAYS'
        ELSE 'OLDER'
    END;

When using an inline function, all of this logic is isolated and clear. It separates everything and makes it extremely easy to read.

WITH
    FUNCTION recency_bucket (
        p_txn_date DATE,
        p_as_of    DATE
    ) RETURN VARCHAR2
    IS
    BEGIN
        RETURN CASE
            WHEN p_txn_date >= p_as_of - 7  THEN 'LAST_7_DAYS'
            WHEN p_txn_date >= p_as_of - 30 THEN 'LAST_30_DAYS'
            WHEN p_txn_date >= p_as_of - 90 THEN 'LAST_90_DAYS'
            ELSE 'OLDER'
        END;
    END;
SELECT
    recency_bucket(t.txn_date, :as_of_date) AS recency_bucket,
    COUNT(*) AS txn_count,
    SUM(t.amount) AS total_amount
FROM transactions t
WHERE
    recency_bucket(t.txn_date, :as_of_date) <> 'OLDER'
GROUP BY
    recency_bucket(t.txn_date, :as_of_date);

Also, where you don’t have permission to install objects like packages or functions, using the WITH FUNCTION will allow you to still make use of PL/SQL in your queries. As discussed in the introduction, it also is good for one off scripts or queries, such as those used in a data migration as you do not have to install any objects to use the query, it just runs as a single standalone query.

When should you NOT use it?

For very simple operations, using an inline function may not always be the best choice. Similar to applying built-in functions such as LOWER() or TRIM() directly to a column, wrapping an indexed column in a function can prevent Oracle from using a normal index on that column unless a suitable function-based index exists. In large datasets, this can have significant performance implications.

However, this does not mean that functions should always be avoided when indexed columns are involved. Oracle can still use indexes when the function logic allows it, such as when appropriate function-based indexes are available, or when SQL inside the function can be optimised independently. The important consideration is whether the function changes the way Oracle can access the data. Functions containing expensive processing, loops, or additional SQL statements should be evaluated carefully because they can add execution overhead.

WITH
    FUNCTION normalize_email (p_email VARCHAR2)
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN LOWER(TRIM(p_email));
    END;
SELECT *
FROM users u
WHERE normalize_email(u.email) = 'john.doe@example.com';

You should also not put core business logic inside these functions, below is an example of a customer tier calculator. Whilst this query works, the function only lives inside that query and cannot be called from elsewhere, meaning if you want to reuse it you will have to copy and paste it everywhere. Instead you should have a central package that can be called to calculate the tier.

WITH
    FUNCTION customer_tier (p_spend NUMBER)
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN CASE
            WHEN p_spend >= 10000 THEN 'GOLD'
            WHEN p_spend >= 5000  THEN 'SILVER'
            ELSE 'BRONZE'
        END;
    END;
SELECT customer_tier(total_spend)
FROM customers;

Closing thoughts

Overall, the Oracle WITH FUNCTION clause is a small but extremely useful feature that deserves far more attention than it gets. While it should not replace proper reusable packages or be used for trivial operations, it provides a clean and powerful way to keep complex transformation logic close to the query itself without creating permanent database objects. This makes it especially valuable for data migrations, one-off scripts, reporting queries, and restricted environments where deploying PL/SQL objects is not practical. By reducing repetition, improving readability, and allowing standard PL/SQL logic to live directly inside SQL, WITH FUNCTION can make otherwise messy queries significantly easier to maintain and understand when used in the right scenarios.