PRAGMA INLINE in Oracle Database

Understanding PRAGMA INLINE in PL/SQL

What is PRAGMA INLINE?

PRAGMA INLINE is a compiler directive in PL/SQL that tells Oracle to replace a function call with the actual function code during compilation, eliminating the usual runtime overhead of creating a new stack frame and passing parameters. This makes execution faster, especially for small, frequently used functions like mathematical or utility operations. Unlike normal function calls, where control shifts in and out of the function, pragma inline executes the logic directly at the call site, improving performance without changing the program’s behavior.

---

In PL/SQL, frequent function calls introduce performance overhead due to repeated context switching between SQL and PL/SQL engines. To address this, PRAGMA INLINE directs the compiler to replace the function call with its actual code, thereby eliminating call overhead. This approach improves execution efficiency by embedding the function logic directly into the calling block.

---

In PL/SQL, PRAGMA INLINE is a compiler directive that instructs the PL/SQL compiler to inline a subprogram call. The term pragma itself means a compiler directive, which is not a SQL statement but an instruction for the compiler on how to treat certain code. Inlining helps improve performance by embedding the subprogram’s code directly at the call site, instead of invoking it through the normal function call mechanism.

---

Without PRAGMA INLINE (Normal Function Call)

When you call a function in PL/SQL without PRAGMA INLINE, the following happens:

1. Control switches from the caller to the callee function.

2. The PL/SQL engine sets up a new execution context for the function.

3. Parameters are passed, and the function executes its logic.

4. The return value is passed back to the caller.

5. The execution context is cleared and control returns to the caller.

This context switching introduces overhead, especially in performance-critical code or when the function is called multiple times in loops.

Example (without INLINE):

CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER) RETURN NUMBER IS
BEGIN
RETURN a + b;
END;
/

DECLARE
result NUMBER;
BEGIN
result := add_numbers(10, 20); -- Normal call
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
END;
/

Here, every call to add_numbers incurs a context switch.

---

With PRAGMA INLINE

When you use PRAGMA INLINE, the compiler replaces the function call with the actual body of the function during compilation. This means no separate context is created at runtime, and the code runs as if it was written inline.

Example (with INLINE):

CREATE OR REPLACE FUNCTION add_numbers(a NUMBER, b NUMBER) RETURN NUMBER IS
PRAGMA INLINE(add_numbers, 'YES');
BEGIN
RETURN a + b;
END;
/

DECLARE
result NUMBER;
BEGIN
result := add_numbers(10, 20); -- Inlined call
DBMS_OUTPUT.PUT_LINE('Result: ' || result);
END;
/

Here, the compiler embeds the logic RETURN a + b; directly at the call site. Thus, context switching is avoided, and execution becomes faster.

---

Behind the Scenes

Without INLINE: Function call → Context setup → Parameter passing → Execution → Return value → Context teardown.

With INLINE: Compiler substitutes function logic directly in the caller → No context switching → Reduced overhead.

Effectively, PRAGMA INLINE transforms the call add_numbers(10, 20) into 10 + 20 at compile time, making execution seamless and faster.

---

Why Use PRAGMA INLINE?

Eliminates runtime overhead of function calls.

Optimizes performance in frequently invoked functions.

Best for small, computation-heavy, or frequently called functions inside loops.

However, it should be used judiciously, as inlining very large functions can lead to code bloat and reduced readability.

---

✅ In summary: PRAGMA INLINE is a powerful performance optimization tool in PL/SQL that instructs the compiler to replace a function call with the function body itself, reducing context switching overhead and speeding up execution.