| Lesson 3 | Creating and Enforcing SQL Standards |
| Objective | Describe methods for creating and enforcing SQL standards in Oracle AI Database 26ai. |
A useful SQL standard helps developers produce code that is correct, secure, maintainable, and suitable for its workload. In Oracle AI Database 26ai, standards are enforced through a combination of database authorization, reviewed application interfaces, code review, and testing. No single client setting can establish all of these properties.
For example, a team may require typed bind parameters for customer searches, restrict the runtime account to approved operations, and test response time under a representative load. Each requirement has a different enforcement mechanism. The database can deny access to an object, while a review or integration test must determine whether an allowed query returns the correct orders. Keeping these responsibilities distinct makes a standard easier to implement and verify.
DBAs and developers should create these rules together. The DBA contributes knowledge of privileges, database behavior, and operational risks. Developers contribute application semantics, interface requirements, and failure-handling needs. The goal is a repeatable engineering process, not a competition over who controls every SQL statement.
For each standard, document the rule, its purpose, the verification method, and the process for an exception. “Write efficient SQL” is too vague to review. “Measure the complete order-search operation with representative parameters and concurrency before release” identifies an activity and evidence that a reviewer can assess.
Separate formatting conventions from behavioral requirements. Consistent indentation, aliases, and naming make queries easier to maintain. They do not establish authorization, prove performance, or guarantee reuse of an existing cursor. A team may standardize keyword capitalization, but that is a readability convention rather than a security control.
| Standard | Reason | Verification |
|---|---|---|
| Bind input data values. | Keep values separate from SQL structure. | Review parameterized calls and test unusual input. |
| Use least-privilege runtime accounts. | Limit reachable data and operations. | Test both allowed and denied actions. |
| Define transaction ownership. | Avoid unexpected commits and incomplete work. | Test success, failure, rollback, and connection cleanup. |
| Specify data types and conversions. | Avoid ambiguous or lossy value handling. | Test NULLs, boundaries, dates, and numeric precision. |
| Define result shape and ordering. | Keep application interfaces predictable. | Assert expected columns, rows, and ordering. |
| Set performance acceptance criteria. | Protect important business operations. | Compare timings and resource use under realistic load. |
| Label application operations. | Make database activity diagnosable. | Check module/action labels and pooled-session cleanup. |
These are proposed team requirements, not rules the Oracle optimizer automatically enforces. Avoid arbitrary bans on joins, subqueries, full scans, or functions. A rule that prohibits a valid technique without considering its purpose can encourage more complicated and slower SQL. Permit documented exceptions when evidence supports them, and identify who reviews those exceptions.
Use explicit columns for stable application interfaces so that an added table column does not silently change the returned result shape. Choose meaningful aliases and qualify ambiguous references. Where the operation requires ordered results, specify an appropriate ORDER BY; an observed access path is not an ordering guarantee.
The following illustrative query assumes an application table named app_owner.orders with the listed columns and a unique order_id. These are example objects, not built-in Oracle tables. The client must bind a suitably typed customer identifier before execution.
SELECT o.order_id, o.order_date, o.status
FROM app_owner.orders o
WHERE o.customer_id = :customer_id
ORDER BY o.order_date DESC, o.order_id DESC;
The placeholder :customer_id is not enclosed in quotation marks. The application supplies a value separately from the SQL text. The final ordering key makes the order deterministic when several rows have the same order date. This fragment is not a complete driver program; placeholder syntax and binding configuration depend on the programming interface.
Bind variables help prevent input values from being interpreted as SQL syntax. They also support statement reuse by avoiding a new SQL text for every value. They do not guarantee one execution plan, eliminate all hard parses, or encrypt sensitive data. Diagnostic facilities may expose bind values, so logging and trace retention still require appropriate controls.
Parameters represent values, not arbitrary table names, column names, or SQL keywords. If a feature lets a user choose a sort column, map the choice to a small reviewed set of identifiers rather than inserting unrestricted input into the statement. Appropriate DBMS_ASSERT routines can assist particular validation tasks, but identifier validation is not authorization and does not make an entire dynamically constructed statement safe.
Define the query's NULL and boundary behavior as part of its contract. For example, decide whether a missing customer identifier is rejected or represents a different operation; do not assume an equality predicate will match NULL. Specify numeric precision and temporal requirements, and prefer properly typed parameters over locale-dependent strings. Tests should demonstrate the intended behavior rather than relying on a developer's default session settings.
An application may submit SQL through several clients, but the database evaluates that activity within its authentication and authorization context. Restricting access to one client executable does not establish a consistent database access policy. Design permissions around accounts, objects, and permitted operations instead.
Separate the runtime account from the schema owner and from accounts used for deployment or administration. A runtime account that only searches orders should not receive broad administrative privileges to simplify development. Review object grants and applicable roles, including access inherited through other grant paths. In a multitenant environment, verify the intended pluggable database and the scope of each grant.
A role groups privileges for administration. EXECUTE is an object privilege that can be granted to an appropriate user or role; it is not an alternative to roles. The GRANT statement assigns that privilege. This example assumes that the package and grantee already exist and that an authorized grantor runs it in the correct database context:
GRANT EXECUTE ON app_owner.order_api TO app_runtime;
This statement permits access to the package's public callable interface. It does not itself grant direct access to the package's underlying tables. It also does not remove access obtained through earlier grants, roles, or PUBLIC. Before claiming that the application can reach data only through this API, review the account's complete effective access and test it.
Keep package interfaces appropriately narrow. Granting execution on a package is not permission for just one selected public procedure inside it. If a package mixes ordinary application operations with powerful administrative functions, separate those responsibilities or redesign the exposed interface. An access standard should specify both the grant and the operations made reachable by that grant.
A carefully designed definer-rights API can let a caller perform approved operations without receiving direct privileges on the underlying tables. The routine executes in its applicable definer security context. This does not transfer every owner privilege to the caller for arbitrary use outside the routine.
In PL/SQL, AUTHID DEFINER and AUTHID CURRENT_USER identify different rights models. Invoker-rights code follows the current user's applicable security context and does not automatically provide the same encapsulation as a definer-rights API. Choose the model based on the intended callers and operations, rather than copying a package declaration without reviewing its implications.
For ordinary named definer-rights units, required privileges on referenced objects must be available directly to the owner rather than merely through an ordinary user role. Oracle also supports advanced code-based access control; that is a separate design consideration. The general statement that roles never matter anywhere in PL/SQL is therefore misleading.
Centralizing SQL in reviewed routines can make behavior easier to maintain, but stored code is not automatically secure or fast. Dynamic SQL within a procedure still requires safe construction and parameter handling. Input validation, object ownership, error handling, and the exposed business operations all remain review concerns. Test the routine with the actual runtime identity, not only as its owner or an administrator.
Specify which layer owns the business transaction. If a service operation updates an order and its audit record, the team must decide when those changes become permanent and how failures are handled. A helper routine that unexpectedly commits may prevent its caller from rolling back the complete operation.
A transaction standard should identify permitted commit points, rollback behavior, and how errors are propagated. Do not silently convert an unexpected database error into a successful application response. If retry logic is required, define which failures are retryable and whether repeating the operation could duplicate an effect. A retry policy cannot be inferred solely from the fact that a request timed out.
With pooled connections, return sessions in the state expected by the next borrower. Resolve transactions and clean up request-specific state using the application's documented lifecycle. Test failures as well as success. A connection that works in a developer's standalone test can behave differently when reused after a failed request.
Pro*C/C++ supports embedded SQL within C or C++ programs. Its precompiler translates embedded SQL into calls to Oracle runtime support before normal compilation. It does not replace the database optimizer or guarantee an optimal execution plan. Host variables follow supported declaration and datatype rules, not an unrestricted rule that any C expression can be used.
Within embedded SQL, a colon introduces a host-variable reference. Input variables supply values; output variables receive results. Declare-section requirements depend on precompiler mode and options. A coding standard should record the supported configuration, conversions, buffer handling, and error-checking conventions for that application instead of assuming every program uses identical settings.
Nullable and truncated outputs require deliberate handling. Indicator variables communicate conditions associated with a value, but do not replace SQL error and no-row checks. For relevant output cases, -1 indicates NULL, 0 indicates an intact value, and a positive value can report the original length of truncated character data. Oracle also documents -2 for truncated long-type output whose original length cannot be determined. Read these statuses according to the datatype and operation.
A robust implementation checks statement status before consuming output, handles NULL explicitly, and respects character-buffer size and termination rules. Financial values also need suitable precision rather than an integer chosen for convenience. These are interface-specific correctness standards; a short embedded-SQL example without its surrounding checks should not be presented as a complete production program.
Store SQL, PL/SQL, and database migrations in version control alongside their tests and design notes. A review should cover more than indentation. Check the required result, input handling, privilege assumptions, transaction effects, and the consequences for other operations. Automated checks can detect selected patterns and conventions, but cannot prove business correctness or acceptable performance.
Use a small release checklist that produces concrete evidence:
Performance evidence should describe its scope. An estimated plan is useful for discussion, but a lower optimizer cost does not prove a faster application request. Measure the relevant operation and examine database work where needed. Do not make production cache flushing or broad tracing a routine checklist step. Diagnostic collection itself needs appropriate scope and permissions.
For the example order search, test customers with no orders, a few orders, and many orders. Confirm that equal dates are ordered consistently and that invalid input is handled according to the contract. Test access with the runtime identity and repeat the workload under realistic concurrent activity. These checks connect the written standard to observable behavior.
Reviewers should also examine compatibility with callers. Renaming an output column, changing an exception, or altering commit behavior can break an application even when the SQL remains syntactically valid. Record these interface changes explicitly and coordinate deployment order when application and database releases depend on one another.
Older Oracle material describes PRODUCT_USER_PROFILE, or PUP, and the PUPBLD setup script. PUP provided SQL*Plus-specific product restrictions, not a database-wide access boundary. It was deprecated in Oracle Database 18c and desupported beginning with 19c, as recorded in the Oracle 26ai Upgrade Guide. Do not use its table definition or installation instructions as an Oracle 26ai enforcement procedure.
There is no single replacement table that enforces every SQL standard. Database privileges govern access across clients, while reviewed APIs, development practices, and tests establish the rest of the policy. When maintaining an old script, identify the control it was intended to provide and replace that control deliberately rather than recreating obsolete client-specific behavior.
Assign an owner to each standard and keep examples aligned with the supported database release and client interfaces. When an exception is necessary, record its reason, reviewer, test evidence, and review date. This makes deliberate departures visible and prevents temporary workarounds from becoming undocumented policy.
After deployment, compare the expected outcome with observed behavior. A standard that repeatedly causes unnecessary work should be revised; a new failure pattern may justify a clearer rule or test. Share those findings with developers so that the policy remains a practical tool. The next lesson considers working with developers who write SQL and applying these responsibilities collaboratively.