Procedures and Functions, Parameter Modes, Overloading, Determinism, and SQL Integration

Learning outcomes

ServiceHub has copied the same validation block into several jobs. A developer turns it into a function, marks it DETERMINISTIC, and then adds an audit-table insert inside the function. Another developer calls that function from a query over many rows and assumes Oracle invokes it exactly once per row. Reusability alone is not enough: procedures and functions need explicit parameter contracts, SQL-callability rules, and truthful side-effect semantics.

1. Procedures perform an action; functions return a value

A procedure can expose zero or more OUT/IN OUT results and is called as a statement. A function has a return type and every normal execution path must return a value. Use a function when “compute a value” is the natural contract; use a procedure for an operation with action-oriented outputs or orchestration.

CREATE OR REPLACE PROCEDURE servicehub_adjust_amount (  p_case_id      IN     NUMBER,  p_multiplier   IN     NUMBER DEFAULT 1,  p_new_amount      OUT NUMBER) AUTHID DEFINERISBEGIN  UPDATE servicehub_subprogram_case  SET amount = amount * p_multiplier  WHERE case_id = p_case_id  RETURNING amount INTO p_new_amount;  IF SQL%ROWCOUNT = 0 THEN    RAISE_APPLICATION_ERROR(-20001,'Unknown case_id');  END IF;END;/

IN is the default mode. OUT returns a value through a parameter. IN OUT lets the subprogram read and then replace/modify the caller's value; use it sparingly because it increases coupling.

2. Overloading is one API name with distinguishable parameter profiles

Package subprograms can be overloaded when PL/SQL can distinguish calls by formal parameter profiles. Overloads should represent the same conceptual operation, not unrelated behaviors hidden behind one name.

CREATE OR REPLACE PACKAGE servicehub_format_api AS  FUNCTION format_key(p_case_id NUMBER) RETURN VARCHAR2;  FUNCTION format_key(p_external_ref VARCHAR2) RETURN VARCHAR2;END servicehub_format_api;/CREATE OR REPLACE PACKAGE BODY servicehub_format_api AS  FUNCTION format_key(p_case_id NUMBER) RETURN VARCHAR2 IS  BEGIN    RETURN 'CASE-' || TO_CHAR(p_case_id);  END;  FUNCTION format_key(p_external_ref VARCHAR2) RETURN VARCHAR2 IS  BEGIN    RETURN UPPER(TRIM(p_external_ref));  END;END servicehub_format_api;/

Avoid overloads whose types are easily confused through implicit conversion. Stable API design is more valuable than clever dispatch.

3. SQL-callable functions have a stricter contract

A stored function referenced from a SQL expression must have only IN formal parameters and must obey restrictions intended to control side effects. A query-invoked function cannot perform ordinary DML in the same logical SQL context. A function called by DML also cannot read or modify the table being modified by that invoking statement.

CREATE OR REPLACE FUNCTION servicehub_bad_touch (  p_case_id IN NUMBER) RETURN NUMBERAUTHID DEFINERISBEGIN  UPDATE servicehub_subprogram_case  SET touched_at = SYSTIMESTAMP  WHERE case_id = p_case_id;  RETURN p_case_id;END;/SELECT servicehub_bad_touch(case_id)FROM servicehub_subprogram_case;-- ORA-14551: cannot perform a DML operation inside a query

Oracle's ORA-14551 message notes that an autonomous transaction can make DML independent of the invoking query, but that is not a general recommendation for row-by-row logging. It creates independent commits and side effects whose execution count/order can be surprising in declarative SQL.

4. DETERMINISTIC is a promise you make—not a proof Oracle performs

DETERMINISTIC asserts that the same inputs always return the same result and that the function obeys required semantics. Oracle documentation explicitly warns that the database does not verify the assertion. A false declaration can silently produce wrong results, especially with function-based indexes, virtual columns, materialized views, or optimizer reuse.

CREATE OR REPLACE FUNCTION servicehub_net_amount (  p_gross IN NUMBER,  p_discount_pct IN NUMBER) RETURN NUMBERDETERMINISTICAUTHID DEFINERISBEGIN  RETURN p_gross * (1 - NVL(p_discount_pct,0)/100);END;/

5. A PL/SQL function inside SQL can be invoked an unspecified number of times

SQL is declarative. Oracle can transform, reorder, eliminate, or repeat expression evaluation while preserving SQL semantics. Do not rely on a SQL-invoked PL/SQL function being called exactly once for every returned row. If invocation count itself is a business requirement, orchestrate explicitly outside the declarative SQL expression.

Even a pure function can add SQL-to-PL/SQL call overhead when evaluated for many rows. Before wrapping a built-in SQL expression, compare a direct set-based expression, virtual column, SQL macro where appropriate, or another design that keeps optimization visible to SQL.

6. Reproducible lab

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_subprogram_case PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_subprogram_case (  case_id      NUMBER PRIMARY KEY,  amount       NUMBER(10,2) NOT NULL,  discount_pct NUMBER(5,2) NOT NULL,  touched_at   TIMESTAMP);INSERT INTO servicehub_subprogram_case VALUES (1,100,10,NULL);INSERT INTO servicehub_subprogram_case VALUES (2,200,5,NULL);COMMIT;

CREATE OR REPLACE FUNCTION servicehub_net_amount (  p_gross IN NUMBER,  p_discount_pct IN NUMBER) RETURN NUMBERDETERMINISTICAUTHID DEFINERISBEGIN  RETURN p_gross * (1 - NVL(p_discount_pct,0)/100);END;/SELECT  case_id,  amount,  discount_pct,  servicehub_net_amount(amount,discount_pct) AS net_amountFROM servicehub_subprogram_caseORDER BY case_id;

SELECT object_name, object_type, statusFROM user_objectsWHERE object_name IN (  'SERVICEHUB_NET_AMOUNT',  'SERVICEHUB_BAD_TOUCH',  'SERVICEHUB_ADJUST_AMOUNT')ORDER BY object_name;

BEGIN EXECUTE IMMEDIATE 'DROP FUNCTION servicehub_bad_touch'; EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN EXECUTE IMMEDIATE 'DROP FUNCTION servicehub_net_amount'; EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN EXECUTE IMMEDIATE 'DROP PROCEDURE servicehub_adjust_amount'; EXCEPTION WHEN OTHERS THEN NULL; END;/BEGIN EXECUTE IMMEDIATE 'DROP PACKAGE servicehub_format_api'; EXCEPTION WHEN OTHERS THEN NULL; END;/DROP TABLE servicehub_subprogram_case PURGE;

7. Production judgment

Stored subprograms are schema APIs. Keep parameter types and side effects explicit, choose definer/invoker rights deliberately, grant EXECUTE narrowly, and avoid hidden commits. Mark a function DETERMINISTIC only when you can prove its semantic promise across sessions and time. Do not use row-by-row function calls from SQL merely to make code look modular.

No special edition, pack, restart, or COMPATIBLE change is required. Lesson 3 packages these subprograms into stable public specifications and hidden bodies, then makes package session state and invalidation behavior visible.

Authoritative references

• Subprogram Parts — procedures/functions and return semantics
• Coding PL/SQL Subprograms and Packages — SQL-callable function restrictions and side effects
• CREATE FUNCTION — stored-function syntax and SQL-callability
• Using Indexes in Database Applications — DETERMINISTIC assertion is not database-verified
• ORA-14551 — DML-inside-query failure semantics