SQL Tuning Tools   «Prev  Next»

Lesson 5Using SQL inside stored procedures
ObjectiveDescribe the reasons for placing SQL inside stored procedures in Oracle AI Database 26ai.

SQL Inside Stored Procedures in Oracle 26ai

A stored procedure gives an application a named operation to call in the database. Instead of requiring every client to implement the same sequence of SQL statements, the team can define a database API with explicit parameters, validation, privileges, and error behavior. In Oracle AI Database 26ai, PL/SQL procedures and packages are a practical way to build that API.

The benefits depend on the design. Encapsulation can improve consistency, reduce communication, and restrict direct table access. It does not automatically make SQL faster, prevent every security flaw, or freeze an execution plan. Well-designed client SQL remains appropriate for many applications. Choose the boundary that fits the operation, then apply the measured review process from working with SQL developers.

Centralize an operation with a clear contract

Start with the business operation rather than a mechanical rule that every select needs a wrapper. An inventory adjustment, account transfer, or coordinated update may involve rules shared by several applications. Putting those rules behind a stable procedure interface can reduce duplicated implementations and inconsistent behavior.

Define what the caller supplies, what the operation changes, what it returns, and which failures it reports. Specify whether it owns the transaction or participates in a larger one. A short parameter list is not a complete contract if callers cannot tell whether a failure leaves earlier work pending or whether a retry may perform the operation twice.

Packages help group related operations. The specification exposes the public interface, while the body holds its implementation and private helpers. Keeping implementation details behind that boundary can make changes easier to coordinate. It does not remove compatibility obligations: changing parameters, error behavior, or side effects can break applications even when they still call the same package name.

Keep the source and migration scripts in version control. Review changes as application code, with tests, ownership, and a rollout plan. A central API concentrates responsibility: one good correction can benefit many callers, but one incompatible change can also affect all of them.

Control privileges deliberately

A narrowly designed definer-rights API can let an application perform an approved operation without general access to its underlying tables. Granting EXECUTE does not give the caller table privileges. The unit executes SQL under its applicable privilege context.

AUTHID DEFINER uses the owner's execution context; AUTHID CURRENT_USER uses invoker-rights behavior. Choose explicitly. For static references to another schema, appropriate direct owner grants are needed for compilation. Ordinary session roles are disabled during definer-rights execution; code-based access control is a separate mechanism. Oracle explains these distinctions in Invoker's Rights and Definer's Rights.

Roles still help organize application access. An application role can receive permission to execute approved procedures. Package-level execute permission covers the public callable API, so keep administrative operations separate from ordinary application operations when their audiences differ. A broad package containing both routine updates and privileged maintenance can defeat an otherwise careful access design.

Review the complete privilege path for the application identity. Ownership, system privileges, inherited roles, and public grants can provide access beyond the intended API. Scope changes to that application after checking dependencies; do not remove every table grant in the database. Existing reporting, integration, and administrative users may have legitimate requirements.

Separate safe SQL construction from business authorization

Static SQL can use PL/SQL parameters as bound values, keeping those values separate from statement syntax. However, a procedure that concatenates untrusted text into dynamic SQL can still be vulnerable to injection. Use binds for values and a restricted, validated choice of identifiers when dynamic object selection is genuinely necessary. Do not accept arbitrary SQL fragments as trusted procedure input. Oracle provides examples and safeguards in its SQL injection documentation.

Input validation and authorization answer different questions. A positive quantity is well formed, but that alone does not establish that a caller may adjust the selected inventory. An account identifier can be syntactically valid while belonging to another customer. Design operation-level checks using trusted identity and ownership information where the application requires them.

Also assume that a compromised application credential can invoke whatever operations it legitimately exposes. Restricting direct table access limits possible actions, but a dangerous API can still cause damage. Review the authority of each operation, the data it returns, and the consequences of repeated calls.

Example: a controlled inventory decrement

The following illustrative example focuses on a small operation: decrement a part's available stock only when sufficient stock exists. Use a disposable teaching schema that owns the table and procedure, with appropriate CREATE TABLE, CREATE PROCEDURE, and tablespace quota. These statements are documentation-reviewed examples, not measured or executed results.

The example assumes authorized callers may adjust any part exposed by this API. A multi-tenant or per-part permission model needs an additional trusted authorization check. This is not a complete ordering system or a retry-safe reservation service.

CREATE TABLE parts_inventory (
    part_id       NUMBER PRIMARY KEY,
    available_qty NUMBER(10,0) DEFAULT 0 NOT NULL,
    CONSTRAINT parts_inventory_qty_ck CHECK (available_qty >= 0)
);

CREATE OR REPLACE PROCEDURE decrement_part_stock (
    p_part_id IN NUMBER,
    p_qty     IN NUMBER
)
AUTHID DEFINER
AS
BEGIN
    IF p_part_id IS NULL
       OR p_qty IS NULL
       OR p_qty <= 0
       OR p_qty <> TRUNC(p_qty) THEN
        RAISE_APPLICATION_ERROR(-20001,
            'Provide a part ID and a positive whole-number quantity.');
    END IF;

    UPDATE parts_inventory
       SET available_qty = available_qty - p_qty
     WHERE part_id = p_part_id
       AND available_qty >= p_qty;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20002,
            'Part not found or insufficient stock.');
    END IF;
END decrement_part_stock;
/

The modifying statement checks availability itself. It does not rely on an earlier unlocked select whose result another session could make outdated. The primary key limits the update to at most one row, and the check constraint provides an additional nonnegative-stock invariant. Immediately inspecting SQL%ROWCOUNT distinguishes an update from a request that matched no eligible row.

This uses normal Oracle locking. Competing updates can wait, and some isolation settings can raise concurrency errors that callers must handle. It is not a lock-free operation. The procedure contains no commit, allowing the application to combine this change with other work before deciding the outcome of the transaction.

For a deployment in which APP_OWNER owns the objects and INVENTORY_APP_ROLE already exists, an authorized administrator could grant this operation as follows:

GRANT EXECUTE ON app_owner.decrement_part_stock
TO inventory_app_role;

Assigning that role to the intended application identity and removing unnecessary direct DML access are separate provisioning decisions. Verify the resulting access with the actual application identity. Testing only as the owner cannot demonstrate that the restricted account has the intended permissions.

Make transaction ownership explicit

A normal procedure participates in the caller's transaction. A commit inside it commits that transaction, including earlier changes made by the caller. Likewise, a broad rollback can undo work beyond the operation the procedure appears to represent. Oracle describes these controls in Transaction Processing and Control.

For a reusable operation such as the inventory decrement, caller-owned commit or rollback usually makes composition clearer. An order workflow can decrement stock, record order details, and then decide whether the combined operation succeeds. A top-level API may intentionally own the entire transaction, but that responsibility must be stated and tested. Do not quietly add an autonomous transaction to bypass the design.

Distinguish an error response from a complete transaction policy. Do not swallow exceptions and report success, and do not assume every exception automatically undoes all earlier work in the caller's transaction. Decide how the application handles expected business failures, unexpected database errors, and pending work.

A lost connection after a possible commit creates a different problem: the caller may not know whether the operation completed. Repeating this example can decrement stock again. A production operation that must tolerate retries needs an idempotency strategy, such as a validated request identifier and recorded outcome. That requires more schema and transaction design than the compact teaching procedure supplies.

Reduce communication where the workload benefits

Grouping database work can reduce application-to-database calls and avoid transferring intermediate results that the client does not need. This is most useful when an application currently coordinates many small dependent operations across a network. Measure the business request before and after moving the orchestration, including the result consumption that the user actually requires.

Do not promise one round trip per transaction. Output fetching, large values, commit behavior, and external services still affect traffic. Nor does database-side execution mean everything stays in memory: storage access and temporary work can remain substantial. A procedure returning a huge unnecessary result can be inefficient despite a simple call interface.

Compare against a competent client implementation. Drivers can support bind variables, prepared statements, statement caches, and array operations. Encapsulation is valuable when it simplifies the contract or removes demonstrated communication, not because client SQL is inherently incapable of reuse.

Understand compilation, caching, and plans

Compiling PL/SQL and optimizing its SQL statements are related but distinct activities. Static SQL enables checks of referenced objects during compilation, while SQL cursor reuse can reduce repeated parsing. Neither guarantees that the same execution plan will remain valid forever. Object changes, privilege changes, cache aging, and execution conditions can require further work or cause errors.

PL/SQL generally reuses suitable cursors within a session. OPEN_CURSORS limits open cursors, while SESSION_CACHED_CURSORS controls a session cursor cache. These should not be described as one unlimited cache or as proof that parsing disappears. See Oracle's cursor overview and session cache parameter.

Stable SQL text helps investigation, but procedural encapsulation does not guarantee one SQL ID, one child cursor, or one plan hash for every environment and revision. Inspect the cursor that served the workload. A procedure can execute a poor query repeatedly just as successfully as a well-designed query.

DBMS_SHARED_POOL.KEEP is a specialized shared-pool facility, not a standard step for every package. Keeping a PL/SQL object does not freeze all associated SQL plans or prevent every invalidation. Use it only for a diagnosed reason; Oracle documents its scope in DBMS_SHARED_POOL.

Prefer set-based work before procedural loops

First ask whether one SQL statement can express the operation correctly. Moving a row-by-row loop from the application into a procedure does not automatically remove the underlying repeated work. Review the required result and transaction semantics before introducing a loop.

When procedural processing is necessary, BULK COLLECT and FORALL can reduce communication overhead between PL/SQL and SQL by batching data transfer and DML. Manage collection memory, batch sizes, and error handling. Do not promise a fixed speedup or describe every multi-step workflow as a single engine switch. Oracle explains the mechanisms in Bulk SQL and Bulk Binding.

Test the contract, not just successful compilation

Compilation is an early check, not acceptance. For the inventory example, prepare controlled data and verify successful decrements, insufficient stock, unknown parts, null arguments, and fractional or nonpositive quantities. Check that failures leave the expected data state and that callers receive usable error information.

Test transaction behavior explicitly. After a successful call, verify that a caller rollback restores the expected state. Test concurrent callers competing for limited stock rather than relying on sequential tests alone. Validate the application account's execute access and its inability to issue direct DML when that restriction is part of the design. These are proposed checks, not results claimed for this lesson.

For a shared API, include compatibility tests from each important caller. A changed error code or an unexpected commit can matter as much as a renamed parameter. Review logging and audit requirements so that investigators can connect an application request to the database operation without unnecessarily recording sensitive inputs.

Retain baseline SQL, relevant inputs, execution evidence, and workload conditions when evaluating performance. SQL Trace/TKPROF and suitably entitled workload repositories can diagnose client-issued SQL as well as procedure-issued SQL. Having procedure source in the database is different from retaining historical execution evidence. Do not imply that only stored procedures can be analyzed after a cursor leaves memory.

Keep optional Oracle 26ai features in perspective

Oracle 26ai offers additional capabilities, but none is activated merely by wrapping SQL in a procedure. When enabled, SQL_TRANSPILER can translate eligible PL/SQL functions called from SQL into SQL expressions. It is not a promise that every function or procedure will be translated or that an entire application has zero procedural overhead.

SQL Firewall needs configured capture, reviewed allow-lists, and enforcement. Capture scope can include SQL issued inside PL/SQL. It complements safe code and authorization rather than replacing them. Check the current offering and licensing rules before recommending optional features.

Similarly, SQL tuning sets and plan-management policies have their own capture and eligibility rules. A stored procedure does not guarantee automatic workload capture or a verified plan baseline. MLE JavaScript is another server-side language option, but its platform, connection, and API constraints differ from an unrestricted client JavaScript runtime. Keep the architectural decision focused on the operation rather than the number of features attached to it.

Choose a database API for a specific reason

A stored procedure is a strong candidate when several callers need the same data operation, a narrow privileged interface is useful, or measured network orchestration is expensive. Direct parameterized SQL may be simpler when an application needs one straightforward operation and already has an appropriate access model. Both approaches require review, instrumentation, and testing.

Centralization can reduce duplicated logic, but it does not make Oracle-specific code automatically portable or maintenance-free. Choose the boundary deliberately, document its guarantees, and test those guarantees. The module conclusion brings together SQL standards, developer collaboration, diagnostic evidence, and controlled database APIs.

SEMrush Software 5 SEMrush Banner 5