Lesson 3	Syntax of a function
Objective	Identify the Correct Syntax of an Oracle Function

Syntax for Oracle Stored Function

A stored function is a named PL/SQL program unit that returns a single value. You can call a function from:

PL/SQL (assign its return value to a variable), and
SQL (use it in an expression), as long as the function follows SQL-usage rules.

This lesson focuses on recognizing correct function syntax, including where parameters, the return datatype, declarations, and the executable body must appear.

Mouse over the keyword CREATE in the diagram below (if enabled on your site) to receive additional information.

Syntax examples of stored functions — **Correct PL/SQL function structure (standalone function):**

`CREATE [OR REPLACE] FUNCTION function_name (parameter_name [IN | OUT | IN OUT] datatype [DEFAULT expr], ...) RETURN return_datatype [AUTHID DEFINER | CURRENT_USER] IS -- or AS -- Declaration section (optional) variable_name datatype; BEGIN -- Executable section (required) -- Body statements ... RETURN return_value; -- required: must return exactly one value EXCEPTION -- Exception section (optional) WHEN OTHERS THEN -- Handle / re-raise as appropriate RAISE; END [function_name]; /`

**Minimal skeleton (showing the required keywords):**

`CREATE [OR REPLACE] FUNCTION function_name RETURN datatype IS BEGIN RETURN value; END; /`

Use `CREATE FUNCTION` (optionally `OR REPLACE`) to define the function. Replace `function_name` with a valid identifier.

Parameters are optional. If you include parameters, you may declare `IN`, `OUT`, or `IN OUT` parameters in PL/SQL.
**Important:** if you intend to call the function from a *SQL* statement, use **`IN` parameters only**. SQL cannot pass or receive `OUT`/`IN OUT` parameters.

The `RETURN` clause is required and defines the datatype of the single value returned by the function.

The keywords `IS` and `AS` are interchangeable here; they introduce the declaration section.

The declaration section is optional. If you declare variables (or local types, cursors, exceptions, etc.), they appear **after** `IS`/`AS` and **before** the main `BEGIN`.

The `BEGIN` keyword starts the executable section. This section is required.

A function must return a value. Practically, this means your executable path must reach a `RETURN` statement. The return value can be a variable, an expression, or a literal—so long as it matches the declared return datatype.

The `EXCEPTION` section is optional. Use it to handle anticipated failures cleanly; for unexpected issues, re-raise the exception so calling code can react appropriately.

The final `END` completes the function definition. You may optionally repeat the function name: `END function_name;`. The trailing `/` is used in tools such as SQL*Plus and SQLcl to execute the CREATE statement.

**Syntax verification note (figcaption):** The original diagram transcription that placed a `DECLARE` block *inside* a `BEGIN` block was not correct for a standard standalone function definition. In PL/SQL, the declaration section belongs between `IS`/`AS` and the first `BEGIN`. (You can nest a separate `DECLARE ... BEGIN ... END;` block inside the executable section, but that is a different construct and should not be presented as the default function syntax.)

Example function you can compile in Oracle 23ai

The example below shows a simple function that returns a numeric value. It also demonstrates a clean exception pattern.

CREATE OR REPLACE FUNCTION calculate_total(p_price    NUMBER,
                                          p_quantity NUMBER)
RETURN NUMBER
IS
  v_total NUMBER;
BEGIN
  v_total := p_price * p_quantity;
  RETURN v_total;
EXCEPTION
  WHEN VALUE_ERROR THEN
    -- invalid numeric conversion / overflow scenarios
    RETURN NULL;
END calculate_total;
/

If you plan to call a function from SQL (for example, inside a SELECT), keep it “SQL-friendly”:

Use IN parameters only.
Avoid performing transaction control (COMMIT, ROLLBACK) inside the function.
Avoid DDL inside the function.
Keep side effects predictable (SQL expects expressions to behave like expressions).

Modern datatype guidance: avoid LONG and LONG RAW

Legacy Oracle datatypes such as LONG and LONG RAW are deprecated for new development and are restricted in many contexts. For maintainability and modern feature support, use LOB types instead:

Use CLOB instead of LONG for large text.
Use BLOB instead of LONG RAW for large binary data.

As a practical rule for modern PL/SQL development, do not design new stored functions that return LONG/LONG RAW. Prefer returning CLOB/BLOB (or a structured result via a SQL object type / pipelined function when appropriate).

Session safety: ENABLE | DISABLE COMMIT IN PROCEDURE

Oracle provides session controls that can prevent procedures and functions from issuing COMMIT or ROLLBACK during your session. This can protect applications that require transaction control to occur only in specific layers of the stack.

If your environment enforces this policy, you may encounter errors when a stored program unit attempts transaction control. In that case, the correct fix is usually to refactor transaction boundaries into the calling application or a designated service layer.

The next lesson gives you a chance to create your own stored function.