Domain 6: Developing on Autonomous Database (20%)

Domain 6 of the 1Z0-931-25 Oracle AI Autonomous Database 2025 Professional exam covers the developer-facing capabilities of Autonomous Database: AI-powered natural language queries, JSON document storage, full-text search, spatial analysis, graph analytics, object storage integration, and data lake analytics. At 20% of the exam, this is one of the two heaviest domains (tied with Domain 2 and Domain 4), representing approximately 10 questions on the 50-question exam (90 minutes, 68% passing score).

The exam syllabus defines seven objectives for this domain (Exam Syllabus):

1. Developing new and extending existing applications with Natural Language Queries using Generative AI
2. Use Autonomous JSON Database
3. Use Oracle Text
4. Incorporate location-based analysis (Autonomous Spatial)
5. Use Autonomous Graph
6. Integrate Object Storage data
7. Data Lake Analytics with Autonomous Database

Contrast with Domain 5: Domain 5 covers the tools layered on top of ADB (OML, APEX, Database Actions, Data Studio). Domain 6 covers the developer APIs and engines embedded inside ADB that applications consume directly: Select AI, SODA, Oracle Text, Spatial, Graph, DBMS_CLOUD, and ORDS/REST.

1. Generative AI and Natural Language Queries (Select AI)

This is the marquee new feature for the 2025 exam. Select AI enables natural language to SQL translation directly within SQL statements, powered by external LLMs. The core package is DBMS_CLOUD_AI. (Select AI Documentation)

How Select AI Works

1. An administrator creates a credential for an LLM provider using DBMS_CLOUD.CREATE_CREDENTIAL
2. A user creates an AI profile that specifies the provider, model, credential, and which database objects to expose
3. The profile is set for the current session using DBMS_CLOUD_AI.SET_PROFILE
4. The user issues SELECT AI statements with natural language prompts
5. ADB sends the prompt plus database metadata (table/column names, constraints, comments) to the LLM
6. The LLM returns generated SQL, which ADB can display, execute, explain, or narrate

Supported LLM Providers

Source: DBMS_CLOUD_AI Package Reference

Exam trap: OCI Generative AI uses provider value oci, not oracle or genai. The oci_apiformat attribute must be set to COHERE or GENERIC depending on the model being called.

DBMS_CLOUD_AI Core Procedures

SELECT AI Actions

After calling DBMS_CLOUD_AI.SET_PROFILE, any SQL statement prefixed with SELECT AI is treated as a natural language prompt:

The default action when none is specified is SHOWSQL. (DBMS_CLOUD_AI Reference)

Exam trap: NARRATE sends actual query result data to the LLM for natural language formatting. SHOWSQL sends only metadata (table/column names). This has data privacy implications. The DISABLE_DATA_ACCESS procedure blocks data-sending actions like NARRATE.

Key Profile Attributes

The GENERATE Function

DBMS_CLOUD_AI.GENERATE provides stateless AI translation without requiring SET_PROFILE:

SELECT DBMS_CLOUD_AI.GENERATE(
    prompt       => 'how many customers',
    profile_name => 'OPENAI',
    action       => 'runsql'
) FROM dual;

This function supports all actions (showsql, runsql, narrate, chat, explainsql, summarize, translate) and accepts optional attributes and params parameters for overriding profile settings and managing conversations. (DBMS_CLOUD_AI Reference)

RAG and Vector Indexes

Select AI supports Retrieval-Augmented Generation (RAG) through vector indexes. DBMS_CLOUD_AI.CREATE_VECTOR_INDEX builds a vector store from documents in object storage. When a profile references a vector index, the LLM's responses are grounded in the indexed documents, improving accuracy for domain-specific questions.

Feedback System

DBMS_CLOUD_AI.FEEDBACK lets users submit positive or negative feedback on generated SQL. Negative feedback requires a corrected SQL response. Select AI automatically creates a feedback vector index (<profile_name>_FEEDBACK_VECINDEX) to store and retrieve feedback for improving future generations.

Exam trap: Feedback is stored in a vector index, not a traditional table. The default match_limit for feedback retrieval is 3, meaning the LLM sees the 3 most similar past corrections when generating new SQL.

2. Autonomous JSON Database

Autonomous JSON Database is a specialized ADB workload type optimized for JSON document storage and NoSQL-style development. It provides document store APIs without requiring SQL knowledge, while retaining full Oracle Database capabilities. (Oracle Autonomous JSON Database)

Key Technical Facts

SODA (Simple Oracle Document Access)

SODA is the NoSQL-style API for working with JSON collections. It provides schemaless document operations without SQL or PL/SQL knowledge. (Oracle SODA)

Available SODA implementations:

Core SODA operations: create collection, insert document, find documents, Query by Example (QBE), bulk operations, document ID retrieval.

Oracle Database API for MongoDB

ADB provides wire-protocol compatibility with MongoDB, allowing MongoDB applications to connect by changing only the connection string. The MongoDB API rewrites MongoDB requests into Oracle SQL or SODA operations internally. (MongoDB API Best Practices)

• Compatible with MongoDB drivers and tools (including MongoDB Compass)
• Supports migration from MongoDB Atlas or on-premises MongoDB using standard import/export tools
• Adds capabilities MongoDB lacks: full ACID transactions, spatial processing of GeoJSON, Oracle Text full-text search, machine learning, and SQL access to JSON data

JSON Relational Duality Views

Available in Oracle AI Database 26ai, JSON Relational Duality Views let developers access the same data as both JSON documents and relational tables simultaneously. Data is stored relationally (normalized, with constraints) but can be read and written as JSON documents through duality views. This eliminates the trade-off between document flexibility and relational integrity.

Exam trap: Autonomous JSON Database is a workload type, not a separate product. It uses the same ADB infrastructure and engine as ADW and ATP, but with storage-optimized pricing for JSON-heavy workloads. All JSON features (SODA, MongoDB API, Duality Views) are available on all ADB workload types, not just the JSON workload.

3. Oracle Text

Oracle Text provides full-text search and document indexing within Autonomous Database. On ADB, the primary use case is searching documents stored in object storage through DBMS_CLOUD. (Full-Text Search Documentation)

Creating a Text Index on Object Storage

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX(
    credential_name => 'DEFAULT_CREDENTIAL',
    location_uri    => 'https://objectstorage.../b/mybucket/o/documents/',
    index_name      => 'DOC_INDEX',
    format          => JSON_OBJECT('refresh_rate' value 10)
  );
END;
/

Key parameters:

• credential_name: Object storage credentials
• location_uri: Object storage path containing files to index
• index_name: Name for the text index
• format: JSON configuration, including refresh_rate (minutes between automatic index refreshes)

Querying with CONTAINS

The index creates a reference table named <INDEX_NAME>$TXTIDX. Use the CONTAINS operator to search:

SELECT object_name, object_path
FROM DOC_INDEX$TXTIDX
WHERE CONTAINS(object_name, 'oracle AND database') > 0;

Supported File Formats

• Text formats: CSV, JSON, plain text
• Binary/formatted: PDF, DOC (Microsoft Word)
• Binary format requires the binary_files format option in CREATE_EXTERNAL_TEXT_INDEX

Index Management

• DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEX(index_name => 'DOC_INDEX') removes the index
• Monitor index creation via ALL_SCHEDULER_JOB_RUN_DETAILS where job_name = '<INDEX_NAME>$JOB'
• The refresh_rate parameter controls automatic reindexing for new/deleted files

Exam trap: Oracle Text on ADB uses DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX, not the traditional CTX_DDL or CREATE INDEX ... INDEXTYPE IS CTXSYS.CONTEXT syntax used on-premises. The procedure creates both the index and the reference table automatically.

4. Autonomous Spatial (Location-Based Analysis)

Oracle Spatial is included in every Autonomous Database instance with no additional licensing. It provides geospatial data storage, indexing, and analysis through the MDSYS schema. (Spatial on ADB)

Core Spatial Capabilities on ADB

Spatial Studio

Oracle Spatial Studio is a web-based tool for visualizing, exploring, and analyzing geospatial data. On ADB, it is available as a separate deployment from OCI Marketplace (not built into Database Actions). Spatial Studio provides no-code geospatial analysis including:

• Map visualization of spatial data
• Spatial analysis workflows (proximity, containment, intersection)
• Geocoding through Oracle's Elocation service
• Data import and export

Limitations on ADB

• Router functionality is not available
• Spatial Studio, Map Visualization, and Network Data Model require separate OCI Marketplace installation
• OGC Web Services require separate deployment

Exam trap: Spatial capabilities (SDO_GEOMETRY, spatial operators, spatial indexing) are built into every ADB instance. Spatial Studio is not built in — it must be deployed separately from OCI Marketplace.

5. Autonomous Graph

Oracle Graph on Autonomous Database supports graph analytics through two models: property graphs and RDF graphs. Graph Studio is the integrated development environment for graph work on ADB. (Graph on ADB)

Graph Models

Graph Studio

Graph Studio is a browser-based tool included in every ADB instance (accessible from Database Actions). It provides:

• Graph modeling from existing database tables
• Interactive notebook with 9 interpreters
• Native graph visualization
• 80+ built-in graph algorithms (ranking, community detection, pathfinding, link prediction, clustering)
• Graph machine learning (DeepWalk, supervised/unsupervised GraphWise)

SQL Property Graph (Oracle Database 23ai / 26ai)

The SQL:2023 standard introduced graph pattern matching directly in SQL using CREATE PROPERTY GRAPH and the MATCH clause. On ADB with Oracle AI Database 26ai, a property graph acts as a view on existing relational data, unifying graph queries with JSON, Vector, and Spatial data in a single SQL statement.

Graph Algorithms by Category

RDF Graph Limitations on ADB

• SPARQL federated queries (querying external data sources) are not supported
• Triple-level security using Oracle Label Security is not supported

Exam trap: Graph Studio is included in ADB at no additional cost and is accessed from Database Actions. It supports both property graphs and RDF graphs. The SQL:2023 property graph syntax (CREATE PROPERTY GRAPH, MATCH) is the modern approach; PGQL remains supported but SQL is the strategic direction.

6. Object Storage Integration (DBMS_CLOUD)

The DBMS_CLOUD package is the primary interface for accessing data in cloud object storage from Autonomous Database. It handles credential management, data loading, external tables, and file operations. (DBMS_CLOUD Documentation)

Credential Management

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'OBJ_STORE_CRED',
    username        => 'user@example.com',
    password        => 'auth_token_or_api_key'
  );
END;
/

Credentials are stored encrypted in the database. Supported cloud providers:

External Tables

External tables query data in object storage directly without loading it into the database:

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name      => 'SALES_EXT',
    credential_name => 'OBJ_STORE_CRED',
    file_uri_list   => 'https://objectstorage.../b/bucket/o/sales/*.csv',
    format          => JSON_OBJECT('type' value 'csv', 'skipheaders' value '1'),
    column_list     => 'sale_id NUMBER, sale_date DATE, amount NUMBER'
  );
END;
/

External tables include two invisible columns: file$path and file$name to identify which file a record originates from.

Key DBMS_CLOUD Procedures for Object Storage

Exam trap: CREATE_EXTERNAL_TABLE does not copy data into the database. It creates a metadata definition that queries object storage on the fly. To load data physically, use COPY_DATA. This distinction is commonly tested.

7. Data Lake Analytics

Data Lake Analytics extends ADB's query capabilities to external data sources, enabling a data lakehouse pattern where a single SQL engine queries both internal database tables and external data lake files. (Oracle Autonomous AI Lakehouse)

Core Concepts

Apache Iceberg Integration (New in 2025)

Oracle Autonomous AI Lakehouse provides native support for Apache Iceberg tables:

• Integrates with catalogs including Databricks Unity, AWS Glue, and Snowflake Polaris
• Queries Iceberg tables in place without data movement
• All ADB capabilities (Select AI, JSON Relational Duality, Property Graph, AI Vector Search) work on Iceberg tables
• Available on OCI, AWS, Azure, Google Cloud, and Exadata Cloud@Customer

Hybrid Partitioned Tables

Hybrid partitioned tables allow a single table definition to span both internal database storage and external object storage files. Older/cold data resides in object storage at low cost, while recent/hot data stays in the database for optimal performance. SQL queries transparently access both partitions.

Exam trap: Data Lake Analytics on ADB uses standard SQL. There is no separate query language or engine. External tables, hybrid tables, and Iceberg tables are all queried using the same Oracle SQL that queries internal tables. The DBMS_CLOUD package handles the connectivity layer.

8. ORDS and Connectivity

While not listed as a separate exam objective, ORDS (Oracle REST Data Services) and language driver connectivity are tested as part of the development topic.

Oracle REST Data Services (ORDS)

ORDS is pre-deployed on every Autonomous Database instance. It provides RESTful API access to database objects and services. (ORDS on ADB)

Exam trap: ORDS is pre-installed and always running on ADB. You do not install, configure, or manage ORDS on Serverless. On Dedicated, customer-managed ORDS is an option but not the default.

Language Drivers and Connectivity

TLS vs. mTLS Authentication

Exam trap: mTLS is always on and cannot be disabled. TLS can be additionally enabled but only when network protection (ACL or private endpoint) is configured. A question asking "which authentication is available by default" — the answer is mTLS.

Exam Traps Summary

1. Select AI default action is SHOWSQL, not RUNSQL. If no action is specified, the generated SQL is displayed but not executed.
2. NARRATE sends data to the LLM, unlike SHOWSQL which sends only metadata. Use DISABLE_DATA_ACCESS to prevent data leakage.
3. OCI Generative AI provider value is oci, not oracle, genai, or oci_genai.
4. Autonomous JSON Database is a workload type, not a separate service. All JSON features work on all ADB workload types.
5. SODA does not require SQL knowledge. It is a NoSQL-style API. DBMS_SODA is the PL/SQL implementation.
6. MongoDB API compatibility uses wire-protocol translation, not data migration. Applications change only the connection string.
7. CREATE_EXTERNAL_TABLE does not load data. Use COPY_DATA to physically load. External tables query in place.
8. Oracle Text on ADB uses DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX, not traditional CTX_DDL syntax.
9. Spatial capabilities are built in; Spatial Studio is not. SDO_GEOMETRY and spatial operators work out of the box. Spatial Studio requires OCI Marketplace deployment.
10. Graph Studio is included and supports both property graphs and RDF. SQL:2023 property graph syntax is the modern direction; PGQL is still supported.
11. mTLS is always enabled and cannot be disabled. TLS is optional and requires network protection.
12. ORDS is pre-deployed on Serverless ADB. No installation or configuration needed.
13. Select AI feedback uses vector indexes, not traditional tables. Feedback improves future SQL generation accuracy.
14. Hybrid partitioned tables span internal and external storage in a single table definition. This is how the lakehouse pattern works.

References

• Select AI Documentation
• DBMS_CLOUD_AI Package Reference
• Oracle Autonomous JSON Database
• Oracle SODA Documentation
• Oracle Database API for MongoDB
• Full-Text Search on ADB
• Oracle Spatial on ADB
• Oracle Graph on ADB
• Oracle Autonomous AI Lakehouse Announcement
• ORDS on Autonomous Database
• python-oracledb Driver
• 1Z0-931-25 Exam Syllabus
• Oracle Education: 1Z0-931-25