There has long been a trade-off between code reuse and performance when it comes to using common logic in Oracle SQL. For example, say your company’s fiscal year runs from 1 July – 30 June. You can find the start of a fiscal year for a calendar date by:

  • Subtracting six months from the date
  • Find the start of the calendar year (1st Jan) after the subtraction
  • Adding six months back onto the result

This is logic you may want to use in many SQL statements, so you create a PL/SQL function like so:

But using this function in SQL – particularly in the WHERE clause – brings performance challenges:

  • The optimizer loses visibility of the expression, making it harder to produce accurate row estimates
  • The SQL and PL/SQL languages have different runtime engines. Whenever one calls the other there is a context switch. When run on every row this can slow your queries down

This query demonstrates the first problem. It finds the number of employees hired in each fiscal year that contain the dates 1 Jan 2015 – 1 Jan 2018:

The plan highlights the issues above.

Notice that the query processes (17+31+20 =) 68 rows, but the optimizer estimates just one row. Also note the function fiscal_year_start appears several times in the predicate and projection sections. So it’s likely there are many context switches when you run this query.

When you optimize this query, you face a tough choice: is query performance or code reuse more important?

The addition of the automatic SQL transpiler in Oracle AI Database 26ai resolves this dilemma.

How to enable the SQL transpiler: set the sql_transpiler pararmeter to on

Enabling the SQL transpiler

The transpiler automatically converts (transpiles) the contents of PL/SQL functions into SQL expressions. This means the PL/SQL function call doesn’t exist at runtime; i.e. you’re running pure SQL. This gives you code reuse and optimal performance at the same time.

To use the transpiler, all you need to do is set the parameter sql_transpiler to on:

The optimizer will then convert the function into SQL whenever possible. Re-run the query above with the transpiler enabled and the plan will now be:

The row estimates in the plan are now accurate. The function has gone from the predicate and projection sections. In its place is the logic it contains.

Enabling the transpiler is a simple way to boost the performance of SQL calling PL/SQL. But it can only convert some functions. To get the full benefit of the transpiler, ensure you write functions that it can convert.

Which functions will be transpiled?

The transpiler will only extract SQL expressions. This means if the function contains any PL/SQL constructs (e.g. %rowtype, pls_integer), it won’t be converted. It also won’t transpile functions that themselves contain SQL statements (select, insert, etc.).

It’s possible to transpile functions that use intermediate variables. For example, you could break up the formula in the fiscal year function by assigning each operation to a variable:

The transpiler will still convert this as it can be simplified back to the original expression. However if you use these variables in unrelated expressions, e.g. to log them for debugging, the database will no longer be able to transpile the function:

See the docs for a complete list of the transpiler eligibility criteria.

At first glance, the transpiler looks similar to a feature added in Oracle Database 21c: scalar macros. These define reusable expressions the database resolves to SQL.

So you may wonder: how does the transpiler differ from scalar macros?

Syntax outline for creating a SQL macro: return a character data type, add the sql_macro clause, and return a string

Automatic SQL transpiler vs scalar macros

Scalar macros enable you to define functions that the database converts to pure SQL when you call them in SQL. To create one:

  • Set the return type of the function to a string type (varchar2 or clob)
  • Add the sql_macro ( scalar ) clause
  • Wrap the expression in quotes to make it a string

So you could write the fiscal year function as a macro like so:

As with the transpiler, the database resolves this to SQL at parse time. So at runtime the function call doesn’t exist.

So the transpiler and scalar macros appear to do the same thing. But the transpiler has an advantage: you can use the function in SQL and PL/SQL.

The resolution of macros only happens in SQL. If you call a SQL macro in PL/SQL, you get the return string back. Not the result of evaluating it. So if you want to use macro logic in SQL and PL/SQL, you need two copies of it. A regular PL/SQL function and a scalar macro.

Which begs the question: why bother with scalar macros?

There are some expressions which are valid SQL, but invalid PL/SQL. For example, say you want to create a function to apply a case-insensitive collation to an expression.

However, the collate clause is invalid in PL/SQL, so this won’t compile:

Whereas you can return this expression in a scalar macro. So you could create a macro to make an expression case-insensitive and use it in SQL like so:

You can also use scalar macros to return scalar subqueries. When you call the macro, the query is merged with the parent SQL. The transpiler won’t extract nested SQL, as these examples show:

So you should use scalar macros if the logic includes:

  • SQL only expressions that won’t compile in PL/SQL

  • SQL queries

For all other use cases, the transpiler is a simpler and more versatile solution.

Another question may occur to you: can transpiling a function change a query’s results?

The short answer is yes: there are limited cases where this happens.

Transpiling vs PL/SQL function calls

When SQL calls PL/SQL, the functions run in the PL/SQL context. So there are a few situations where transpiling a function can give different results to the untranspiled call.

The simplest example is a function that returns systimestamp. The database returns the current time when the function is invoked. This can be different for each row. So there is a slight variation in the times reported by this query (pay close attention to the fractional seconds):

When this function is transpiled, the value of systimestamp is when the SQL statement started. So it’s the same for every row, no matter how long the query takes:

Scalar macros resolve to SQL, so they work the same as the transpiler in these scenarios. So when calling time functions (sysdate, current_timestamp, etc.) you need to decide whether you want them to return statement start time or function runtime. Then use the transpiler or macros as needed.

Calling transpiled functions and macros also brings a subtle difference in parameter evaluation::

  • Calling (untranspiled) PL/SQL uses application order. This evaluates the parameters before passing them to the function.
  • Calling transpiled PL/SQL and macros uses normal order. This passes the parameters as-is and evaluates them as part of the expression in the function.

This difference is best shown by example. The function below is a wrapper for coalesce, which returns the first non-null value in its list:

Calling this as regular PL/SQL with 1/0 for the second parameter evaluates 1/0 first. So raises a divide by zero error:

Transpiling the function (or making it into a macro) pushes 1/0 into coalesce at run time. Provided the first parameter is non-null, the database doesn’t check 1/0 and the function runs without error:

While this is a contrived example, it clearly demonstrates the difference. Whenever actual parameter expressions can have side effects (e.g., raise errors, change database state), PL/SQL and SQL can give different outcomes. In these scenarios, test thoroughly to ensure your logic works as needed.

There’s one final situation to consider: what does the transpiler do if a function in query is used in a function-based index?

Creating an index on a function means the transpiler will not convert it to SQL when the index is a candidate for a query

Transpilation and function-based indexes

You could rearrange the query at the start of this post. Instead of checking if the hire dates are between the fiscal year starts, you could pass hire_date to the function. Then check the result is in the list of dates you want:

By default the transpiler will convert the function call to SQL, as the predicate section of the plan shows:

But what if you’ve created a function-based index on fiscal_year_start ( hire_date )?

Transpiling means that the function call is no longer present at runtime. So the database would be unable to use the index.

To account for this, the transpiler won’t convert functions used in a candidate index for the query. So the plan remains:

This highlights another difference between the transpiler and macros. You can’t create indexes on macros and they’re always converted into pure SQL. So unlike the transpiler, you need to index the expression inside the macro.

Remember that the original query is better. The optimizer can use a regular index on hire_date. You should avoid applying functions to columns in the where clause when possible. This reduces the number of indexes you need to create.

That said, some expressions can’t be rearranged. Meaning there are rare situations where function-based indexes are necessary. In these cases, it’s safe to enable the transpiler. The optimizer can still use any function-based indexes you’ve created on your PL/SQL.

Summary

Enabling the SQL transpiler is a simple and safe way to speed up PL/SQL in SQL. Give it a try today!

The statements in this post are based on the HR sample schema.

For a detailed discussion of the transpiler, scalar macros, and other options for optimizing PL/SQL in SQL, watch the recording of “Who can make PL/SQL function calls in SQL the fastest?”. In this SQL Office Hours session, Kim Berg Hansen, Oracle ACE Director, and I explored the many ways you can make these queries faster:

UPDATE 20 Oct 2025: Oracle AI Database 26ai replaces Oracle Database 23ai