APEX, ORDS and Application Development

64.1 Oracle APEX (Application Express)

A low-code platform that runs inside the database. Pages, regions (interactive reports and grids, charts, forms, faceted search), dynamic actions, processes, validations, authorization schemes, REST data sources, PWA, and generative AI assistants.

Workspaces, the App Builder, SQL Workshop, Gallery apps, Universal Theme.

64.2 ORDS (Oracle REST Data Services)

Hosts Database Actions, the APEX runtime, the SODA REST API, the MongoDB API, OAuth2 security, and the DB API for administration.

BEGIN
  ORDS.ENABLE_SCHEMA(p_schema => 'SHOP', p_url_mapping_pattern => 'shop');
  ORDS.ENABLE_OBJECT(p_schema => 'SHOP', p_object => 'PRODUCTS');            -- AutoREST
  ORDS.DEFINE_SERVICE(p_module_name => 'orders.v1', p_base_path => '/orders/',
                      p_pattern => ':id', p_method => 'GET', p_source_type => ORDS.source_type_collection_item,
                      p_source => 'SELECT * FROM orders WHERE order_id = :id');
END;
/

64.3 Connecting from Applications

JDBC (thin driver), UCP connection pool, R2DBC, Spring Data python-oracledb (thin or thick mode), SQLAlchemy

ODP.NET Core / Managed, Entity Framework Core provider godror / go-ora OCI (Oracle Call Interface), ODPI-C Oracle ODBC driver (Instant Client)

Instant Client, connection pooling (UCP, DRCP, client-side pools), statement caching, array fetch size (arraysize, prefetchrows), batched DML (executemany), Application Continuity, and FAN-aware pools.

Best practices:

Always use bind variables.

Avoid committing in loops.

Instrument with module/action/client identifier.

Watch for ORM N+1 queries.

import oracledb
with oracledb.connect(user="shop", password=pwd, dsn="localhost:1521/FREEPDB1") as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT order_id, status FROM orders WHERE customer_id = :cid", cid=42)
        for row in cur:
            print(row)