Boost Your Oracle SQL with PL/SQL Functions Inside the WITH Clause
Starting with Oracle 12c, a powerful new enhancement lets you define PL/SQL functions and procedures directly inside a SQL query’s WITH clause. This means you can embed procedural logic right within your query without creating standalone database objects. The result? Cleaner code, better encapsulation, and often improved performance.
What Makes This Feature Useful?
Traditionally, if you needed to use a PL/SQL function in your SQL, you had to create and store it separately in the database. And if, like many of us, you are working on Oracle Fusion Cloud Applications, you do not have the required access to create database options, this can result in overly complex SQL queries to achieve the desired results. But not anymore.
We have the ability to define a function inside the WITH clause, this compiles at runtime, scoped to your query execution, resulting in faster runs and easier code management.
Implementation Steps
How Does It Work? Let’s See an Example
Suppose you want to find the latest hire date in the employee table and use it repeatedly in your query. You can declare a PL/SQL function in the WITH clause like this:
01.
WITH (Common Table Expressions)
Copy this Query into a Data Set, and give it a spin.
WITH
FUNCTION get_trx_amt(p_trx_number IN VARCHAR2
) RETURN NUMBER AS
trx_amt NUMBER;
BEGIN
SELECT SUM(
DECODE(types.accounting_affect_flag,
'Y', schedules.amount_due_original + NVL(schedules.amount_adjusted,0),
'N', lines.extended_amount + NVL(schedules.amount_adjusted,0)
)
) AS total
INTO trx_amt
FROM ra_customer_trx_all header
INNER JOIN ra_cust_trx_types_all types
ON header.cust_trx_type_seq_id = types.cust_trx_type_seq_id
INNER JOIN
(
SELECT rcta.customer_trx_id
, rcta.org_id
, SUM(rctla.extended_amount) AS extended_amount
FROM ra_customer_trx_lines_all rctla
INNER JOIN ra_customer_trx_all rcta
ON rctla.customer_trx_id = rcta.customer_trx_id
AND rctla.org_id = rcta.org_id
AND rcta.complete_flag = 'Y'
GROUP BY rcta.customer_trx_id, rcta.org_id
) lines
ON header.customer_trx_id = lines.customer_trx_id
AND header.org_id = lines.org_id
INNER JOIN
(
SELECT rcta.customer_trx_id
, rcta.org_id
, SUM(arps.amount_adjusted) AS amount_adjusted
, SUM(arps.amount_due_original) AS amount_due_original
FROM ar_payment_schedules_all arps
INNER JOIN ra_customer_trx_all rcta
ON arps.customer_trx_id = rcta.customer_trx_id
AND arps.org_id = rcta.org_id
AND rcta.complete_flag = 'Y'
GROUP BY rcta.customer_trx_id, rcta.org_id
) schedules
ON header.customer_trx_id = schedules.customer_trx_id
AND header.org_id = schedules.org_id
AND header.complete_flag = 'Y'
AND header.trx_number = p_trx_number
GROUP BY header.trx_number;
RETURN NVL(trx_amt,0);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_trx_amt;
SELECT ArTrxHeader.customer_trx_id
, ArTrxHeader.trx_number
, get_trx_amt(ArTrxHeader.trx_number) as trx_amt_from_func
FROM ra_customer_trx_all ArTrxHeader
WHERE ArTrxHeader.trx_number between '9000225800' and '9000225806'
Need some assistance?
Ready to bring clarity to customer, supplier and employee communications? With BI Publisher, we specialise in customising customer, supplier, and employee-facing documents such as invoices, purchase orders, payslips, and statements to meet your exact branding, compliance, and operational needs. From layout design to data logic enhancements, we help you deliver clear, consistent, and impactful outputs across your organization. Contact us today to discover how our expertise in BI Publisher can streamline your document processes and elevate your business communications.
