Key Takeaways

• .NET applications can now run Oracle SQL and PL/SQL scripts from files, cloud storage, REST APIs, or other stream based sources.
• A new ASP.NET Core provider automatically applies Deep Data Security context with minimal application changes.
• ODP.NET Deep Data Security no longer requires a human user identity, supporting services and AI agents that operate independently.
• The release also adds current OpenTelemetry conventions, session state resets, and performance improvements for EF Core and asynchronous operations.

Introduction

I’m proud to announce that ODP.NET 23.26.4 is now available on NuGet with these features:

• Execute SQL and PL/SQL scripts from .NET
• Use the Deep Data Security end-user security context provider with ASP.NET Core
• Configure an optional user identity for Deep Data Security
• Incorporate new ODP.NET OpenTelemetry attribute tags and semantic conventions
• Reset each database session to the original application state
• Benefit from ODP.NET performance improvements

Let’s explore these enhancements.

SQL Script Execution for .NET

As more workloads move to cloud environments and AI agents administer them, organizations need programmatic interfaces for executing SQL and PL/SQL scripts. Command-line tools were generally designed for human operators with privileged access to on-premises databases. AI workloads instead rely on automated processes with least-privilege access, while cloud environments often restrict direct administrative access to database resources.

ODP.NET Core SQL script execution addresses these requirements. .NET applications can execute SQL and PL/SQL commands programmatically from a stream. They can bind script parameters as JSON and receive results as a JSON string.

Now in production with ODP.NET 23.26.4, this capability helps .NET applications apply and automate database changes. Scripts can come from files, blob storage, REST APIs, or any other source that can be read by a .NET StreamReader.

At runtime, an application passes a StreamReader to the OracleCommand.ExecuteStatements extension method. Parameters can be supplied as a JSON document through OracleCommand.SetParametersForExecuteStatements or through the OracleCommand.Parameters collection.

I described ODP.NET SQL script execution in a previous blog post if you want more details.

In short, this feature gives .NET developers and DevOps teams a flexible scripting tool for modern cloud and AI applications.

Deep Data Security: End-User Security Context Provider in ASP.NET Core

The new ODP.NET Deep Data Security (DeepSec) provider model lets ASP.NET Core applications adopt Oracle DeepSec with minimal code changes.

After a one-time setup, existing applications can use DeepSec without explicitly creating or assigning an end-user security context.

Oracle AI Database version 23.26.2 first introduced the feature. I provided an overview of ODP.NET Deep Data Security and its benefits in a previous blog post. Here’s how to use the new provider with ASP.NET Core.

First, install the Oracle.ManagedDataAccess.DependencyInjection NuGet package, configure its settings, and call builder.Services.AddOracleEndUserSecurityContextProvider(builder.Configuration) before builder.Build().

Applications provide end-user security information through the authenticated request. When enabled, ODP.NET discovers and uses the provider implicitly. Immediately before an eligible database operation, the provider builds the security context from the current request and attaches it to the database operation. Requests must use JSON Web Token (JWT) bearer authentication so the provider can access the request context.

Configure the provider in appsettings.json or through the ASP.NET Core IConfiguration model under Oracle.ManagedDataAccess:EndUserSecurityContext. The provider is enabled by default because the EnableEndUserSecurityContextProvider option defaults to true.

Here’s a sample appsettings.json configuration for the end-user security context provider:

{
  "Oracle.ManagedDataAccess": {
    "EndUserSecurityContext": {
      "EnableEndUserSecurityContextProvider": true,
      "Type": "AZURE",
      "TokenUri": "https://<identity-provider-host>/<tenant>/oauth2/v2.0/token",
      "GrantType": "client_credentials",
      "ClientId": "<client-id>",
      "ClientSecret": "<client-secret>",
      "Scope": "<database-token-scope>",
      "TokenRenewalOffsetSeconds": 60,
      "SessionDataRoles": "<role-1>,<role-2>",
      "EndUserContextAttribute": "{\"<namespace>\":{\"<key>\":\"<value>\"}}",
      "FetchGroupsOnScope": "<optional-oci-groups-scope>",
      "RoleClaimType": "ORACLE_DATA_ROLE",
      "AttributeClaimType": "ORACLE_CONTEXT_ATTRIBUTE"
    }
  }
}

Note: In a production app, you would supply the client secret in a more secure manner, such as from vault storage.

Once configured, import Oracle.ManagedDataAccess.DependencyInjection.EndUserSecurityContext and call its registration API before building the host to enable the provider. Here’s a code snippet of what that looks like:

using Oracle.ManagedDataAccess.DependencyInjection.EndUserSecurityContext;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddAuthentication(/* app authentication setup */);
builder.Services.AddAuthorization();

builder.Services.AddOracleEndUserSecurityContextProvider(builder.Configuration);

var app = builder.Build();

app.UseAuthentication();
app.UseAuthorization();

app.MapGet("/health", () => Results.Ok("ok"));
app.Run();

For more information, see the Deep Data Security End-User Security Context Provider section in the ODP.NET Developer’s Guide. For database set up, review the configuration chapter in the Oracle Deep Data Security Guide.

Deep Data Security: Optional User Identity

Previously, DeepSec required an end-user identity: an access token or username. This release makes the identity optional.

Some services host multiple applications and AI agents in a single database. Each application or agent may use its own connection pool and perform work independently, without acting on behalf of a human user. By removing the identity requirement, DeepSec now supports these services alongside AI agents and human users.

Two new OracleEndUserSecurityContext methods create contexts without a user identity:

• CreateWithDatabaseAccessToken(string) — creates an end-user security context using only a database access token.
• CreateWithoutEndUser() — creates a new end-user security context with the same database access token, data role, and attribute values as the current instance except for the end user identity.

New OpenTelemetry Attribute Tags and Semantic Conventions

ODP.NET 23.26.4 supports the latest OpenTelemetry database semantic conventions and attribute names. Deprecated attributes remain available for compatibility.

Developers can configure ODP.NET to emit traces using the latest attributes, deprecated attributes, or both. The changes include:

• db.name is deprecated; db.namespace replaces it.
• db.odp.connection.id is deprecated; oracle.db.connection.id replaces it.
• db.odp.sql_id is deprecated; oracle.db.sql.id replaces it.
• db.statement is deprecated; db.query.text replaces it.
• db.system is deprecated; db.system.name replaces it.
• db.user is deprecated.

To configure the behavior, use one of these settings:

• OTEL_SEMCONV_STABILITY_OPT_IN environment variable
• OpenTelemetrySemconvStabilityOptIn .NET configuration setting, or
• OracleConfiguration.OpenTelemetrySemconvStabilityOptIn property

The configuration sources support the same values. They are:

• database: emit the new tags only;

• database/dup: emit both the deprecated and new tags;

• any other value, or no value: emit the deprecated tags only.

Read more about ODP.NET OpenTelemetry in the ODP.NET Developer’s Guide.

Reset Each Database Session to the Original Application State

When an ODP.NET connection returns to the pool, ODP.NET can reset the client and database session state to the original login state. This prevents state from leaking to the next user who receives the connection. Enable this feature by setting the RESET_STATE database service attribute to LOGIN. ODP.NET Core, managed ODP.NET, and unmanaged ODP.NET support it starting with version 23.26.4.

With this setting, each pooled connection starts with the same session state, reducing artifacts that might persist after a failover.

To learn more about using the RESET_STATE attribute and LOGIN value, read the Application Continuity section of the Oracle Database Development Guide.

ODP.NET Performance Improvements

For this release, ODP.NET includes performance improvements across the provider, including faster EF Core SaveChanges operations and improved asynchronous execution. Performance remains a focus, and we plan to deliver further improvements in upcoming ODP.NET releases in 2027.

Coming Soon

In November, Microsoft will release .NET 11 and EF Core 11. ODP.NET will support both soon afterward, and I will announce availability in future blog posts.

Until then, read more about the ODP.NET 23.26.4 features in the ODP.NET Developer’s Guide and download ODP.NET packages from NuGet Gallery.

FAQs

How does SQL script execution exchange inputs and results?
Applications can bind parameters through JSON or the standard command parameter collection, while execution results are returned as JSON.

How is the Deep Data Security provider enabled?
Install the dependency injection package, configure the provider, and register it before building the ASP.NET Core application. The provider is enabled by default after setup.

Does the release remove older OpenTelemetry attributes?
No. It supports the latest attribute names and conventions while retaining deprecated attributes for compatibility.

What does the new session reset feature do?
It restores pooled database connections to their original login state, helping prevent one user’s session settings from affecting the next user.

ODP.NET SQL Scripting and the Deep Data Security End-User Security Context Provider was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Key takeaways

• The preview feature runs multiple SQL and PL/SQL statements directly from .NET applications without a separate scripting tool.
• Scripts can come from files, cloud storage, REST APIs, or any other source available as a .NET stream.
• Results, output parameters, metadata, and detailed errors are returned as JSON for easy processing and recovery.
• Developers can control error handling, parameter binding, transactions, output limits, and concurrent script execution using .NET.

ODP.NET Core introduces a scripting option for applying database changes. Developers can now programmatically manage schema changes, errors, and scale their workflows more easily without a separate tool. ODP.NET scripting benefits include:

• Cloud and AI-friendly

As more applications move to the cloud and adopt AI, management tools must support cloud automation and AI-driven operations. ODP.NET scripting can run in the cloud and enable both AI agents and human operators to execute database tasks.

· Enhanced security and safety

ODP.NET scripting can retrieve credentials at runtime from encrypted vaults or secure environment configurations, so they do not appear in command history.

· Robust error handling and conditional logic

.NET applications can inspect detailed statement execution errors to automate recovery workflows, such as retries after a temporary network outage or rolling back changes. Developers can inspect the execution results to determine application-level failures and take the appropriate next steps.

· Seamless data sharing and state management

Programmers can capture script output as JSON, parse it, and manipulate it as variables or objects. Keeping intermediate results in memory can avoid disk I/O, which can improve performance and reduce exposure of sensitive data.

· Parallelism and concurrency

.NET provides APIs for asynchronous programming, threads, and parallel processing, making it easier to run multiple scripts concurrently.

ODP.NET SQL Script Execution Features

For these reasons, we have introduced ODP.NET SQL scripting. Available on NuGet as a preview release, the Oracle.ManagedDataAccess.SQLScriptExecution package supports:

• Executing SQL scripts with OracleCommand.ExecuteStatements
• Running multiple SQL and PL/SQL statements from one stream
• Returning result sets, output parameters, and errors as JSON
• Binding parameters through JSON or APIs
• Choosing whether to stop after an error or continue execution
• Setting LOB and LONG fetch limits for JSON results
• Getting statement-level metadata with detailed error information

Let’s explore ODP.NET SQL script execution.

Using SQL Script Execution

ODP.NET Core can execute SQL and PL/SQL from a StreamReader so that developers can apply and automate their database changes. The scripts can originate from a file, blob storage, a REST API, or any other source that can be represented by a .NET StreamReader.

Programmatically, a script is represented by a .NET StreamReader object and passed to an OracleCommand.ExecuteStatements extension method to run.

Parameters can be provided either as a JSON document via OracleCommand.SetParametersForExecuteStatements method or through the OracleCommand.Parameters collection. If both are provided, the parameter collection takes precedence. JSON-based parameters support bind-by-name only, while the parameter collection supports bind-by-name and bind-by-position.

SQL script execution supports binding all the parameter data types ODP.NET can bind except for associative arrays, array binds, and user-defined types.

Output result sets, parameters, and metadata, such as rows affected are all returned as JSON strings.

For every error, its related information is returned, including its number, message, and statement parsing metadata. Developers can configure whether the script aborts upon an error (default) or continues to execute after the error. After an error, previously committed statements are not undone. Retries must account for partially committed scripts.

These scripts can only contain SQL and PL/SQL syntax. A forward slash (/) on a line by itself is supported as a PL/SQL block terminator. It is recognized by the SQL script execution parser as a statement terminator and is not included in the statement text returned in the result JSON. Other SQL*Plus and SQLcl syntax is not supported.

By default, statements execute with the connection’s auto-commit behavior. Transactions are supported. However, if a DDL statement is supplied within a transaction, its execution implicitly commits the current transaction. The remaining statements are executed in connection’s auto-commit mode.

ODP.NET SQL script execution is backwards compatible with the same database versions as the ODP.NET version it depends on. For example, ODP.NET 26ai is backwards compatible with Oracle Database 19c.

Sample Scripts and Output

Here is a C# code sample that supplies a script and its input parameters through StreamReader objects, sets the script error option behavior, and executes the script. The results are returned as a JSON string. The script and parameters are retrieved from Oracle Cloud infrastructure Object Storage.

const string namespaceName = "<OCI_NAMESPACE>";
const string bucketName = "<BUCKET_NAME>";
const string sqlObjectName = "oci_sql_script.sql";
const string jsonParametersObjectName = "oci_sql_parameters.json";

// Authenticate using the DEFAULT OCI profile.
var provider = new ConfigFileAuthenticationDetailsProvider("DEFAULT");

using var objectStorageClient = new ObjectStorageClient(provider);

// Retrieve the SQL script from OCI Object Storage.
var sqlRequest = new GetObjectRequest
{
    NamespaceName = namespaceName,
    BucketName = bucketName,
    ObjectName = sqlObjectName
};

var sqlResponse = await objectStorageClient.GetObject(
    sqlRequest,
    completionOption: HttpCompletionOption.ResponseHeadersRead);

using var sqlReader = new StreamReader(
    sqlResponse.InputStream,
    Encoding.UTF8,
    detectEncodingFromByteOrderMarks: true);

// Retrieve the JSON parameters from OCI Object Storage.
var jsonParametersRequest = new GetObjectRequest
{
    NamespaceName = namespaceName,
    BucketName = bucketName,
    ObjectName = jsonParametersObjectName
};

var jsonResponse =
      await objectStorageClient.GetObject(jsonParametersRequest);

using var jsonParametersReader = new StreamReader(
    jsonResponse.InputStream,
    Encoding.UTF8,
    detectEncodingFromByteOrderMarks: true);

// Open the database connection.
using var connection = new OracleConnection("<CONNECTION_STRING>");
connection.Open();

using var command = connection.CreateCommand();

// Set parameters from the JSON stream.
command.SetParametersForExecuteStatements(jsonParametersReader);

// Set execution behavior.
var executionSettings = new OracleStatementExecutionSettings
{
    ExecutionOption = OracleExecutionOption.ContinueOnError
};

// Execute the SQL/PLSQL script from the stream.
string results = command.ExecuteStatements(
    sqlReader,
    executionSettings);

The SQL script contains a series of SQL and/or PL/SQL statements.

insert into EMPTABLE(EMPNO, ENAME) values (5,'John');
insert into INVALIDEMPTABLE(EMPNO, ENAME) values (10, 'Max') returning EMPNO into :param1;
select * FROM EMPTABLE where ENAME = :param2;

BEGIN
:p_message := NVL(:p_message, '') || 'is an anonymous PL/SQL block.';
END;
/

A JSON stream provides the parameter binding information for the SQL script. The JSON below supplies the parameters for the SQL script above.

[
  {
    "name": "param1",
    "type": "NUMBER",
    "direction": "Output"
  },
  {
    "name": "param2",
    "type": "VARCHAR2",
    "size": 32,
    "value": "John",
    "direction": "Input"
  },
  {
    "name": "p_message",
    "type": "VARCHAR2",
    "size": 40,
    "value": "This ",
    "direction": "InputOutput"
  }
]

After the script executes, the results from the SELECT statement are returned as a JSON output string. Here are several examples of the output.

Note: The JSON document returned from SQL script execution does not escape the characters <, >, &, and ‘. If the JSON is embedded in HTML or ‘<script>’ content, apply the appropriate context-specific encoding before inserting it to avoid any security vulnerabilities.

DML Statement Output

{ 
    "statement": "insert into EMPTABLE(EMPNO, ENAME) values (5,'John')",  
    "errors": null,
    "rowsAffected": 1,
    "result": null
}

Execution Error Output

{
  "statement": "insert into INVALIDEMPTABLE(EMPNO, ENAME) values (10, 'Max') returning EMPNO into :param1",
  "rowsAffected": null,
  "result": null,
  "errors": {
    "startLine": 2,
    "endLine": 2,
    "startColumn": 1,
    "endColumn": 90,
    "items": [
      {
        "number": 942,
        "message": "ORA-00942: table or view does not exist"
      }
    ]
  }
}

SELECT Output

{
    "statement": "select * FROM EMPTABLE where ENAME = :param2;",
    "errors": null,
    "rowsAffected": -1,
    "result": [
      {
        "columns": [
          { 
            "name": "EMPNO", 
            "type": "NUMBER",
            "precision": 0,
            "scale": -127,
            "columnDefinition": "NUMBER"
          },
          { 
            "name": "ENAME", 
            "type": "VARCHAR2",
            "size": 32,
            "isByteSemantic": true,
            "columnDefinition": "VARCHAR2(32)"
          }
          ],
        "rows": [
          [
            5, 
            "John"
          ],
          [
            20, 
            "John"
          ]
        ]
      }
    ]
}

PL/SQL Output

{
    "statement": "BEGIN\r\n:p_message := NVL(:p_message, '') || 'is an anonymous PL/SQL block.';\r\nEND;\r\n",
    "errors": null,
    "rowsAffected": -1,
    "result": [
      {
        "columns": [
          {
            "name": "p_message",
            "type": "VARCHAR2"
          }
        ],
        "rows": [
          [
            "This is an anonymous PL/SQL block."
          ]
        ]
      }
    ]
}

Oracle Data Type Representation in JSON Results

This table defines how Oracle database data types are mapped to JSON value types (JsonValueKind) in .NET for accurate JSON representation

Now that the capabilities and features have been covered, let’s explore the ODP.NET SQL scripting APIs in detail.

SQL Script Execution APIs

OracleCommand.ExecuteStatements Method

This method executes a stream of SQL and PL/SQL statements and returns any results as a JSON string.

Declaration

public static string ExecuteStatements (this OracleCommand cmd, StreamReader streamReader, OracleStatementExecutionSettings executionSettings = null);

Parameters

• cmd — The OracleCommand instance
• streamReader — The stream of SQL and PL/SQL statements to execute.
• executionSettings — The mode the statements are executed and output size limits for LONG/LOB columns.

Return Value

A JSON string with result sets, result metadata, and error information, if applicable.

Remarks

By default, each statement execution is auto-committed. App can use ExecuteStatements in a transaction by starting the transaction before calling this method.

executionSettings determines behavior when a statement returns an error.

Statement Execution Result Format

The return value is output in the following JSON schema format:

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "urn:oracle:manageddataaccess:sqlscriptexecution:schemas:statement-result:1.0",
  "title": "ScriptExecutionResults",
  "type": "array",
  "items": {
    "type": "object",
    "required": [
      "statement",
      "errors",
      "rowsAffected",
      "result"
    ],
    "properties": {
      "statement": {
        "type": "string",
        "description": "SQL or PL/SQL command text"
      },
      "errors": {
        "description": "Error details if execution failed; null otherwise",
        "oneOf": [
          {
            "type": "null"
          },
          {
            "type": "object",
            "required": [
              "startLine",
              "endLine",
              "startColumn",
              "endColumn",
              "items"
            ],
            "properties": {
              "startLine": {
                "type": "integer",
                "minimum": 1
              },
              "endLine": {
                "type": "integer",
                "minimum": 1
              },
              "startColumn": {
                "type": "integer",
                "minimum": 1
              },
              "endColumn": {
                "type": "integer",
                "minimum": 1
              },
              "items": {
                "type": "array",
                "minItems": 1,
                "items": {
                  "type": "object",
                  "required": [
                    "number",
                    "message"
                  ],
                  "properties": {
                    "number": {
                      "type": [
                        "integer",
                        "null"
                      ],
                      "description": "Oracle error number or null for non-SQL errors"
                    },
                    "message": {
                      "type": "string"
                    }
                  },
                  "additionalProperties": false
                }
              }
            },
            "additionalProperties": false
          }
        ]
      },
      "rowsAffected": {
        "description": "-1 for successful SELECT, DDL, and PL/SQL statements; a nonnegative affected-row count for successful DML; null when statement execution fails",
        "oneOf": [
          {
            "type": "integer"
          },
          {
            "type": "null"
          }
        ]
      },
      "result": {
        "description": "Query result sets or OUT / IN OUT bind values",
        "oneOf": [
          {
            "type": "null"
          },
          {
            "type": "array",
            "items": {
              "type": "object",
              "required": [
                "columns",
                "rows"
              ],
              "properties": {
                "columns": {
                  "description": "Metadata for each column in the result set, in the same order as values appear in each row.",
                  "type": "array",
                  "items": {
                    "$ref": "#/$defs/columnMetadata"
                  }
                },
                "rows": {
                  "description": "Result set rows; each row is an array of column values",
                  "type": "array",
                  "items": {
                    "type": "array",
                    "items": {}
                  }
                }
              },
              "additionalProperties": false
            }
          }
        ]
      }
    },
    "additionalProperties": false
  },
  "$defs": {
    "oracleNativeType": {
      "type": "string",
      "description": "Oracle native data type or PL/SQL data type.",
      "enum": [
        "NUMBER",
        "INT",
        "INTEGER",
        "FLOAT",
        "LONG",
        "BINARY_FLOAT",
        "BINARY_DOUBLE",
        "VARCHAR2",
        "NVARCHAR2",
        "CHAR",
        "NCHAR",
        "CLOB",
        "NCLOB",
        "BLOB",
        "BFILE",
        "DATE",
        "TIMESTAMP",
        "TIMESTAMP WITH TIME ZONE",
        "TIMESTAMP WITH LOCAL TIME ZONE",
        "INTERVAL DAY TO SECOND",
        "INTERVAL YEAR TO MONTH",
        "RAW",
        "LONG RAW",
        "ROWID",
        "BOOLEAN",
        "JSON",
        "VECTOR",
        "REFCURSOR",
        "SYS_REFCURSOR",
        "XMLTYPE"
      ]
    },
    "columnMetadata": {
      "type": "object",
      "required": [
        "name",
        "type"
      ],
      "properties": {
        "name": {
          "type": "string",
          "description": "Column name for SELECT result sets, or parameter name for OUT/INOUT/ReturnValue bind results"
        },
        "type": {
          "$ref": "#/$defs/oracleNativeType"
        },
        "precision": {
          "type": "integer",
          "minimum": 0,
          "description": "Numeric precision (optional)"
        },
        "scale": {
          "type": "integer",
          "minimum": -127,
          "description": "Numeric scale (optional)"
        },
        "size": {
          "type": "integer",
          "minimum": 1,
          "description": "Size of the column in bytes or chars depending on isByteSemantic (optional)"
        },
        "isByteSemantic": {
          "type": "boolean",
          "description": "True for byte semantics, false for character semantics (optional)"
        },
        "vectorDimensions": {
          "type": "string",
          "description": "Vector dimension count for VECTOR types (optional)"
        },
        "vectorType": {
          "type": "string",
          "enum": [
            "DENSE",
            "SPARSE"
          ],
          "description": "Vector storage type for VECTOR columns (optional)"
        },
        "vectorDimensionFormat": {
          "type": "string",
          "description": "Vector dimension format for VECTOR columns (optional)"
        },
        "columnDefinition": {
          "type": "string",
          "description": "The column definition that matches what SQL*Plus returns when describing a table with this column"
        }
      },
      "additionalProperties": false
    }
  }
}

OracleCommand.SetParametersForExecuteStatements Method

This method binds a JSON stream of parameters for SQL script execution.

Declaration

public static void SetParametersForExecuteStatements(this OracleCommand cmd, StreamReader jsonParameters)

Parameter

• cmd — The OracleCommand instance
• jsonParameters — The parameter information to bind to the script’s SQL and PL/SQL statements.

Remarks

Parameters can only be bound by name using this method.

Bind Parameter Format

SQL script execution parameters are bound using the following JSON schema format:

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "$id": "urn:oracle:manageddataaccess:sqlscriptexecution:schemas:bind-parameters:1.0",
  "title": "BindParameters",
  "type": "array",
  "items": {
    "type": "object",
    "required": [
      "name",
      "type",
      "direction"
    ],
    "properties": {
      "name": {
        "type": "string"
      },
      "type": {
        "$ref": "#/$defs/oracleNativeType"
      },
      "direction": {
        "type": "string",
        "enum": [
          "Input",
          "Output",
          "InputOutput",
          "ReturnValue"
        ]
      },
      "value": {
        "description": "Value for the bind parameter."
      },
      "size": {
        "type": "integer",
        "minimum": 1
      },
      "precision": {
        "type": "integer",
        "minimum": 0
      },
      "scale": {
        "type": "integer",
        "minimum": -127
      }
    },
    "allOf": [
      {
        "if": {
          "properties": {
            "direction": {
              "enum": [
                "Output",
                "InputOutput",
                "ReturnValue"
              ]
            },
            "type": {
              "enum": [
                "VARCHAR2",
                "NVARCHAR2",
                "CHAR",
                "NCHAR",
                "RAW",
                "LONG RAW"
              ]
            }
          },
          "required": [
            "direction",
            "type"
          ]
        },
        "then": {
          "required": [
            "size"
          ]
        }
      },
      {
        "if": {
          "properties": {
            "direction": {
              "enum": [
                "Input",
                "InputOutput"
              ]
            }
          },
          "required": [
            "direction"
          ]
        },
        "then": {
          "required": [
            "value"
          ]
        }
      }
    ],
    "additionalProperties": false
  },
  "$defs": {
    "oracleNativeType": {
      "type": "string",
      "description": "Oracle native data type or PL/SQL data type.",
      "enum": [
        "NUMBER",
        "INT",
        "INTEGER",
        "LONG",
        "BINARY_FLOAT",
        "BINARY_DOUBLE",
        "VARCHAR2",
        "NVARCHAR2",
        "CHAR",
        "NCHAR",
        "CLOB",
        "NCLOB",
        "BLOB",
        "BFILE",
        "DATE",
        "TIMESTAMP",
        "TIMESTAMP WITH TIME ZONE",
        "TIMESTAMP WITH LOCAL TIME ZONE",
        "INTERVAL DAY TO SECOND",
        "INTERVAL YEAR TO MONTH",
        "RAW",
        "LONG RAW",
        "BOOLEAN",
        "JSON",
        "VECTOR",
        "REFCURSOR",
        "SYS_REFCURSOR",
        "XMLTYPE"
      ]
    }
  }
}

OracleStatementExecutionSettings Class

An OracleStatementExecutionSettings object provides options for configuring the execution mode of SQL and PL/SQL statements from a script and output size limits for LONG/LOB columns.

Class Inheritance

System.Object

• Oracle.ManagedDataAccess.SQLScriptExecution.OracleStatementExecutionSettings

Declaration

public sealed class OracleStatementExecutionSettings

Thread Safety

Instance members are not guaranteed to be thread-safe.

OracleStatementExecutionSettings.ExecutionOption Property

This property specifies ODP.NET SQL script execution exception behavior.

Declaration

public OracleExecutionOption ExecutionOption {get; set;}

Property Value

An OracleExecutionOption enumeration value

Property Type

OracleExecutionOption

Remarks

This property determines ODP.NET behavior when SQL script execution encounters an error. Either the execution aborts on error, OracleExecutionOption.AbortOnError, (default) or continues to execute the remaining statements after the error is encountered, OracleExecutionOption.ContinueOnError.

OracleStatementExecutionSettings.MaxLOBFetchSize Property

This property specifies the maximum amount of LOB data included in the JSON result.

Declaration

public long MaxLOBFetchSize { get; set; }

Property Value

The maximum number of characters for CLOB/NCLOB data or the maximum number of bytes for BLOB/BFILE data to include in the result JSON.

Property Type

Long

Remarks

This property limits how much CLOB/NCLOB/BLOB/BFILE data is fetched and serialized into the JSON result for each LOB column value. If the LOB value exceeds this limit, only up to MaxLOBFetchSize characters for CLOB/NCLOB or bytes for BLOB/BFILE are included. The default is 65536.

OracleStatementExecutionSettings.MaxLONGFetchSize Property

This property specifies the maximum amount of LONG data (LONG/LONG RAW) included in the JSON result.

Declaration

public long MaxLONGFetchSize { get; set; }

Property Value

The maximum number of characters for LONG data or the maximum number of bytes for LONG RAW data to include in the result JSON.

Property Type

Long

Remarks

This property limits how much LONG/LONG RAW data is fetched and serialized into the JSON result for each LONG column value. If the value exceeds this limit, only up to MaxLONGFetchSize characters for LONG or bytes for LONG RAW are included. The default is 65536.

OracleExecutionOption Enumeration

This enumeration values specifies the SQL script execution behavior after encountering an exception.

OracleExecutionOption Enumeration Values

AbortOnError — When ODP.NET encounters an exception, SQL script execution stops with no further statements executed. This is the default value.

ContinueOnError — When ODP.NET encounters an exception, SQL script execution continues on.

Get Started with ODP.NET SQL Scripting

After this whirlwind tour, try ODP.NET SQL scripting for yourself. Download the SQL scripting package from NuGet and share feedback with the Oracle team on GitHub or in the ODP.NET discussion forum.

#OracleDotNet

FAQs

How can developers access the feature?
It is available as a preview release through the Oracle.ManagedDataAccess.SQLScriptExecution package on NuGet.

What syntax can a script contain?
Scripts may contain SQL and PL/SQL, including / as a PL/SQL block terminator. Other SQL*Plus and SQLcl commands are unsupported.

What happens when a statement fails?
Execution stops by default, but it can be configured to continue. Each error includes its number, message, and location in the statement.

How are database changes committed?
Statements are auto-committed by default. Transactions are supported, although a DDL statement implicitly commits the current transaction and causes the remaining statements to run in auto-commit mode.

Run Oracle SQL Scripts in .NET was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Key takeaways

• Two directions, one package. DBMS_CLOUD_PIPELINE provides load pipelines (object storage → table) and export pipelines (table or query → object storage). The database schedules, tracks and monitors both.
• Loads track files by object name. Each object name is loaded once. Changing a file’s content under the same name does not reload it, and a copy under a new name is treated as a new file.
• Incremental exports use a time watermark. With a date or timestamp key_column, each run exports rows whose key falls after the previous run and up to the current run. This suits append-style data. Rows that arrive late with older key values, and updates to rows already exported, are not picked up.
• Freshness follows the schedule, not a fixed delivery time. The interval attribute sets how often a pipeline runs (in minutes, default 15). Data arrives on the next run after it lands.
• Credentials depend on where the data lives. In OCI, the database’s resource principal avoids stored keys. Amazon S3, Azure Blob Storage and Google Cloud Storage need their own credentials, and databases on private endpoints may need outbound network routing.

Why this feature matters

Machine learning models, property graphs, dashboards and natural-language queries are only as useful as the data underneath them. In most architectures that data arrives as files in object storage: device telemetry, application logs, partner extracts, change-data-capture output, exports from other systems. Getting those files into the database continuously usually takes a chain of services:

a file lands → an event rule fires → a serverless function starts → it calls a loader → custom code records which files were loaded → more code retries the failures

Each link has to be built, secured and monitored. The same is true in the other direction, when fresh results must flow out of the database to a data lake, a partner or an archive.

Oracle Autonomous AI Database has a built-in alternative that is still little known: Data Pipelines, delivered through the DBMS_CLOUD_PIPELINE package. A pipeline is a database object. It continuously loads new files from object storage into a table, or continuously exports new data from a table or query to object storage. Scheduling, file tracking, parallelism and retries are handled by the database, without event rules, functions or an external scheduler.

This post explains what Data Pipelines do, why they matter, how to use them for loading and for full and incremental exports, how they behave at the edges, and where they fit.

What Data Pipelines are

A Data Pipeline is a scheduled, self-tracking data mover that runs inside Autonomous AI Database. There are two types.

Load pipeline: object storage → table

• Each run moves the files it has not loaded before.
• Progress is tracked by object name, in a per-pipeline status table.
• Formats: CSV, JSON, XML, Avro, ORC, Parquet.
• Failures are retried on later runs. A file that does not match the table is marked FAILED, and the remaining files keep loading.

Export pipeline: table or query → object storage

• Each run moves either the rows whose key_column value falls in the window since the last run (incremental), or the complete result (full snapshot).
• Progress is tracked by the time of the last run, compared with a date or timestamp key_column.
• Formats: CSV, JSON, Parquet, XML.
• Failures are repeated by the next run.

Both types share the same lifecycle: create → configure → test → start, then monitor, stop, restart or drop as needed. Everything is done in SQL and PL/SQL.

Pipelines read from and write to OCI Object Storage, and also Amazon S3, Azure Blob Storage and Google Cloud Storage. For OCI Object Storage, the database’s resource principal removes the need to store keys. The other providers need a credential of their own (see the FAQs).

Why this feature matters

The database manages the schedule. A pipeline runs on the interval you set and checks for new data itself. Nothing has to notify it, so there is no event infrastructure to build or operate.

File tracking is built in. A load pipeline records every object it has seen, with its status, row count and timing, in a status table. There is no bookkeeping table to design and no custom logic to stop a file loading twice.

Retries and error isolation are built in. Failures are retried automatically on later runs. A malformed file does not stop the pipeline: it is marked FAILED for review while the other files keep loading.

Incremental exports need one attribute. Point an export pipeline at a table or query and name a timestamp column. Each run exports only the rows in the window since the last run. Leave the column out and each run exports a complete snapshot instead.

Monitoring is in the data dictionary. Pipelines have views for status, history and per-file results, and follow the same security, monitoring and change-control practices as the rest of the database.

Fewer moving parts. The data movement runs on the database’s own scheduler and compute, so there are no event rules, functions or external schedulers to deploy. Resource use is controlled with the priority attribute and the run interval.

It feeds everything else in the converged database. Once data lands in a table, it is available to Oracle Machine Learning, Property Graph, AI Vector Search, Select AI, Spatial, JSON, APEX and ORDS, without another copy or another pipeline.

Load pipelines

How a load pipeline works

The producer does not need to know the database exists, and the database does not need the producer to tell it anything. Any process that can put a file into the folder participates.

Getting files into object storage

A load pipeline only cares that a new object appears under its folder. Common ways to get it there:

• Write-only pre-authenticated request (PAR) with an HTTP PUT from curl, PowerShell, Python, any language or an IoT device. The producer needs only the URL: no OCI account, SDK or IAM policy. Ideal for partners, devices, scripts and external systems.
• OCI Console upload. Needs an OCI user. Good for ad-hoc files and testing.
• OCI CLI (oci os object put, bulk-upload). Needs an OCI user and API key. Good for batch jobs, servers and CI/CD.
• OCI SDKs (Python, Java, Go, .NET, JavaScript and more). Use an API key, or an instance or resource principal. Good for applications in or out of OCI.
• Amazon S3 Compatibility API (AWS CLI, boto3, rclone, most ETL tools). Needs a customer secret key. Lets existing S3 tooling work unchanged.
• From a database with DBMS_CLOUD.PUT_OBJECT, DBMS_CLOUD.EXPORT_DATA or an export pipeline. Uses a credential, resource principal or write PAR. Good for database-to-database feeds.
• OCI Connector Hub from Streaming, Logging or Monitoring. Configuration only. Turns streams and logs into files.
• Kafka or OCI Streaming with an S3-compatible sink connector. Needs a customer secret key. Lands event streams as files.
• OCI GoldenGate, Data Integration or Data Flow. Service configuration. Lands CDC and ETL output.
• OCI Data Transfer (disk or appliance). For very large one-time loads.

The write-only PAR deserves a special mention because it removes the identity problem for external producers. A PAR scoped to one folder with write access lets the holder upload objects with a single HTTP request:

curl -X PUT --data-binary @readings.csv "<upload-link>readings_20261024_101500.csv"

Invoke-WebRequest -Method Put -InFile .\readings.csv -Uri "<upload-link>readings_20261024_101500.csv"

The holder can add files, but cannot list, read or delete anything, and cannot write outside the folder. Access ends when the PAR expires or is deleted. Treat the URL as a secret, issue one per producer, and renew it before it expires. A PAR can be created in the console (Bucket → Pre-Authenticated Requests) or from SQL with the OCI PL/SQL SDK:

DECLARE
  r dbms_cloud_oci_obs_object_storage_create_preauthenticated_request_response_t;
BEGIN
  r := dbms_cloud_oci_obs_object_storage.create_preauthenticated_request(
         namespace_name  => '<namespace>',
         bucket_name     => '<bucket>',
         create_preauthenticated_request_details =>
           dbms_cloud_oci_object_storage_create_preauthenticated_request_details_t(
             name         => 'meter-readings-upload',
             object_name  => 'meter_readings/',   -- folder scope
             access_type  => 'AnyObjectWrite',    -- write-only
             time_expires => SYSTIMESTAMP + INTERVAL '90' DAY),
         region          => '<region>',
         credential_name => 'OCI$RESOURCE_PRINCIPAL');
  DBMS_OUTPUT.PUT_LINE(r.response_body.full_path || 'meter_readings/');
END;
/

Creating a load pipeline

1. Allow the database to read the bucket. For OCI Object Storage, the resource principal avoids stored keys:

EXEC DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();   -- as ADMIN, once

with an OCI policy such as Allow dynamic-group <adb-dg> to read objects in compartment <compartment>. For other providers, create the matching credential instead (see the FAQs).

2. Create the target table.

CREATE TABLE meter_readings (
  meter_id    VARCHAR2(20),
  region      VARCHAR2(20),
  reading_ts  TIMESTAMP,
  kwh         NUMBER(10,3),
  voltage     NUMBER(6,2),
  status      VARCHAR2(10)
);

3. Create and configure the pipeline. Attribute values are passed as strings, including the format JSON. Attributes such as location, table_name and query are fixed once the pipeline has run (RUN_PIPELINE_ONCE or START_PIPELINE); changing them afterwards requires RESET_PIPELINE.

BEGIN
  DBMS_CLOUD_PIPELINE.CREATE_PIPELINE(
    pipeline_name => 'METER_LOAD',
    pipeline_type => 'LOAD',
    description   => 'Load smart-meter CSV files as they arrive');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('METER_LOAD', 'credential_name', 'OCI$RESOURCE_PRINCIPAL');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('METER_LOAD', 'location',
    'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/meter_readings/');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('METER_LOAD', 'table_name', 'METER_READINGS');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('METER_LOAD', 'format',
    '{"type":"csv","skipheaders":1,"timestampformat":"YYYY-MM-DD HH24:MI:SS"}');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('METER_LOAD', 'interval', '1');   -- run every minute (default 15)
END;
/

4. Test, then start.

EXEC DBMS_CLOUD_PIPELINE.RUN_PIPELINE_ONCE('METER_LOAD');   -- one run, in the current session
EXEC DBMS_CLOUD_PIPELINE.START_PIPELINE('METER_LOAD');      -- scheduled from now on

From this point, each new object name under meter_readings/ is loaded by the next scheduled run.

Export pipelines: incremental and full

Export pipelines move data the other way: from a table or query to object storage, on a schedule, in parallel, with retries. Output can be CSV, JSON, Parquet or XML. Each run writes one or more files under the location prefix, named <prefix>_<n>_<timestamp>.<ext>. For CSV, JSON and XML, a large result is split into several files of up to 10 MB each by default; the maxfilesize format option raises the limit to as much as 1 GB.

Incremental export: a time-based watermark

Name a date or timestamp column as the key_column. The pipeline tracks the time of each run. On every run it exports the rows whose key falls after the previous run and up to the current run. A run with no rows in that window writes no file.

The example exports an events table that records when the database received each row:

CREATE TABLE voltage_alarms (
  meter_id    VARCHAR2(20),
  region      VARCHAR2(20),
  reading_ts  TIMESTAMP,                            -- when the event happened
  voltage     NUMBER(6,2),
  load_ts     TIMESTAMP DEFAULT SYSTIMESTAMP        -- when the row arrived: the export key
);

BEGIN
  DBMS_CLOUD_PIPELINE.CREATE_PIPELINE(
    pipeline_name => 'ALARMS_EXPORT',
    pipeline_type => 'EXPORT',
    description   => 'Publish new voltage alarms to the data lake');

  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('ALARMS_EXPORT', 'credential_name', 'OCI$RESOURCE_PRINCIPAL');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('ALARMS_EXPORT', 'location',
    'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/exports/alarms');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('ALARMS_EXPORT', 'query',
    'SELECT meter_id, region, reading_ts, voltage, load_ts FROM voltage_alarms');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('ALARMS_EXPORT', 'key_column', 'LOAD_TS');   -- incremental
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('ALARMS_EXPORT', 'format', '{"type":"json"}');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('ALARMS_EXPORT', 'interval', '15');
END;
/
EXEC DBMS_CLOUD_PIPELINE.START_PIPELINE('ALARMS_EXPORT');

Because the watermark is time-based, choose the key column carefully:

• Prefer an insert timestamp set by the database (for example load_ts TIMESTAMP DEFAULT SYSTIMESTAMP) over a business event time. An event time can be older than the last run when a row arrives late, and such a row falls outside every future window. An insert timestamp always lands in the next window.
• Rows dated in the future are exported by the first run after their timestamp is reached, not before.
• Updates are not tracked. A row that has already been exported is not exported again when its other columns change. For change capture, maintain an “updated at” column and export on it, or use a CDC tool such as GoldenGate.
• RESET_PIPELINE clears the tracked time, so the next run exports from the beginning again.

Full (snapshot) export

Leave key_column unset and each run exports the entire result of the table or query. This suits reference tables, aggregates and "current state" extracts. Adding the run time as a column makes each snapshot self-describing:

BEGIN
  DBMS_CLOUD_PIPELINE.CREATE_PIPELINE(
    pipeline_name => 'REGION_SUMMARY_EXPORT',
    pipeline_type => 'EXPORT',
    description   => 'Hourly snapshot of regional consumption');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('REGION_SUMMARY_EXPORT', 'credential_name', 'OCI$RESOURCE_PRINCIPAL');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('REGION_SUMMARY_EXPORT', 'location',
    'https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/exports/region_summary');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('REGION_SUMMARY_EXPORT', 'query',
    'SELECT region, COUNT(*) readings, ROUND(AVG(voltage),2) avg_voltage, ROUND(SUM(kwh),1) kwh,
            SYSTIMESTAMP AS snapshot_ts
       FROM meter_readings GROUP BY region');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('REGION_SUMMARY_EXPORT', 'format', '{"type":"csv","header":true}');
  DBMS_CLOUD_PIPELINE.SET_ATTRIBUTE('REGION_SUMMARY_EXPORT', 'interval', '60');
END;
/
EXEC DBMS_CLOUD_PIPELINE.START_PIPELINE('REGION_SUMMARY_EXPORT');

Choosing between them

• Incremental export: set key_column to a date or timestamp column, ideally an insert timestamp. Each run writes only the rows in its time window. Best for events, logs, transactions and other append-only facts. Consumers process every file in order.
• Full export: no key_column. Each run writes the complete result, possibly as several files. Best for reference data, aggregates and current-state extracts. Consumers read the complete set of files from the latest run (see the FAQ on identifying a run's files).

Oracle-maintained export pipelines

Autonomous AI Database ships with two preconfigured export pipelines that ADMIN can start:

• ORA$AUDIT_EXPORT exports database audit records to object storage as JSON.
• ORA$APEX_ACTIVITY_EXPORT exports the APEX workspace activity log as JSON.

Both run every 15 minutes once started and use the resource principal by default. They are a ready-made way to feed a SIEM, a log analytics service or long-term retention without writing any code.

Use cases

With load pipelines

• IoT and telemetry analytics. Devices upload readings through a write-only PAR, and each file is queryable after the next scheduled run, ready for dashboards and anomaly detection.
• Continuous ML scoring and retraining. Fresh rows feed OML PREDICTION queries and periodic retraining without a separate ingestion job.
• Graphs that stay current. Property graphs defined over continuously loaded tables reflect relationships as of the latest run.
• Partner and supplier data drops. Each partner gets its own upload link, with no accounts and no shared credentials.
• CDC and ETL landing zones. GoldenGate, Data Integration or other tools write files, and the pipeline loads each new file with retries and per-file tracking.
• Migration from other databases. Export source tables to CSV, upload them, and run one pipeline per table for fault-tolerant, resumable loading.
• Multicloud ingestion. Read directly from S3, Azure Blob or GCS without copying files into OCI first.

With export pipelines

• Feeding a data lake or lakehouse (incremental). New rows land as Parquet or JSON for Spark, Data Flow or other engines.
• Sharing results with partners (full or incremental). Publish curated extracts to a bucket the partner can read.
• Security and compliance (Oracle-maintained). ORA$AUDIT_EXPORT sends audit records to object storage for a SIEM or long-term retention.
• Archiving aged data (incremental). Use a scheduled job to set an archive_ready_ts timestamp when rows pass the retention threshold. Set archive_ready_ts as the export key_column so newly eligible rows enter a later export window. Purge source rows only after the corresponding export run succeeds; Data Pipelines do not delete source rows.

With both

• Database-to-database feeds. One Autonomous AI Database exports new rows, and another loads them, each on its own schedule.

Operating pipelines

Monitoring. Each pipeline has a row in USER_CLOUD_PIPELINES (status, last run, status table name), run history in USER_CLOUD_PIPELINE_HISTORY, and its settings in USER_CLOUD_PIPELINE_ATTRIBUTES. A load pipeline also has a status table listing every file with its status, rows loaded and timings, which makes it straightforward to build an operational dashboard.

SELECT pipeline_name, status, last_execution, status_table
FROM   user_cloud_pipelines;

SELECT pipeline_name, status, start_date, end_date, error_message
FROM   user_cloud_pipeline_history
ORDER  BY start_date DESC FETCH FIRST 20 ROWS ONLY;

Control. STOP_PIPELINE and START_PIPELINE pause and resume. RESET_PIPELINE clears the pipeline's tracking (optionally purging loaded data with purge_data => TRUE), and also allows fixed attributes such as location to be changed. DROP_PIPELINE removes the pipeline. A reset gives a load pipeline a new status table, so views over the status table are best written against a synonym.

Design recommendations:

• Choose the interval for the freshness you need, and budget for it. interval is a scheduling setting in minutes (default 15). Shorter intervals mean data lands sooner, but each run lists the folder and consumes database resources, so a one-minute interval suits near-real-time feeds rather than every pipeline.
• Size priority deliberately. HIGH processes files (or export fetches) in parallel based on the database's ECPU count and finishes sooner, but consumes more database resources than MEDIUM (the default) or LOW (serial).
• Name files uniquely, for example with a timestamp (readings_YYYYMMDD_HH24MISS.csv). Load pipelines track files by name.
• Prefer fewer, larger files for high-volume feeds. Each run lists the folder, so batching rows keeps listing fast.
• Plan retention. Pipelines do not delete anything. Use an Object Storage lifecycle policy or a scheduled job for old files, and partitioning or a scheduled DELETE for old rows.
• Scope and rotate upload links. Use write-only, folder-scoped PARs with an expiry, one per producer, and issue the replacement before the old one expires.

FAQs

What happens if a file is replaced, or uploaded again? Load pipelines track files by object name. Overwriting an already-loaded object with new content under the same name does not reload it. Uploading the same content under a different name loads it again as a new file, which can duplicate its rows. To correct or deliberately reload one file, upload it under a new name and reconcile the rows from the earlier version. Resetting is not a single-file reprocess: it clears tracking, so the next run loads every object in the pipeline location. With purge_data => TRUE, the reset also truncates the target table before reloading.

What happens when a file fails to load? The file is marked FAILED in the pipeline's status table with the error, and the remaining files continue to load. Failed operations are retried on subsequent scheduled runs. A file that cannot succeed as it is (for example, a column mismatch) keeps failing until the file or the table definition is corrected.

Does an incremental export pick up rows that arrive late, or rows updated after they were exported? No. Each run exports rows whose key_column value lies after the previous run's time and up to the current run's time. A row inserted later with an older key value falls outside every future window and is not exported. An update to a row that has already been exported is not exported again. Using an insert timestamp set by the database (DEFAULT SYSTIMESTAMP) as the key avoids missing late arrivals. Exporting updates requires an "updated at" column maintained on every change. For full change data capture, including updates and deletes, see Change Data Capture with LogMiner on Autonomous AI Database.

What about rows dated in the future? They are exported by the first run that starts after their key value is reached.

What credentials and network access does non-OCI storage need? OCI Object Storage can use the database’s resource principal with an OCI policy. Amazon S3, Azure Blob Storage and Google Cloud Storage need a credential for that provider: a credential object created with DBMS_CLOUD.CREATE_CREDENTIAL (access keys), or a keyless option such as an AWS Amazon Resource Name (ARN), an Azure service principal or a Google service account. Databases that use a private endpoint may also need an outbound network route to the provider's storage endpoint, for example through a NAT gateway.

How does a consumer identify all files that belong to one snapshot run? A large export run writes several files, and each file name carries its own timestamp rather than a shared run identifier. Two reliable approaches: include the run time in the data (SYSTIMESTAMP AS snapshot_ts in the export query gives every row of a run the same value, even across multiple files), or read every file created since the previous run, using the object creation times. Runs are separated by the pipeline interval, which makes the boundaries easy to find.

Can the location, table or query be changed after a pipeline has run? Not directly. These attributes are fixed once the pipeline has run, including through RUN_PIPELINE_ONCE. To change them, stop the pipeline, run RESET_PIPELINE, set the new values, and start it again. A reset also clears the pipeline's tracking, so plan how existing files or rows should be handled.

How fresh is the data? Data is processed on a scheduled run after it lands. The interval attribute sets how often runs are scheduled (default 15 minutes); actual delivery time also depends on when the file arrives in that cycle, run duration, and database workload.

See it running

A live example shows a load pipeline at work. Smart-meter files of varying size are uploaded through a write-only link every five minutes, and the pipeline runs every minute. In the example, files are typically loaded between a few seconds and about 40 seconds after upload, depending on where the upload falls in the pipeline’s one-minute cycle. The example also includes a self-renewing upload link, 90-day file retention and an automatic reset when the table reaches 4 million rows. Visitors can upload a file themselves and watch it move from the bucket into the table. The page is a single static HTML file in Object Storage, reading ORDS endpoints on the database.

👉 Live pipeline monitor

Summary

Data Pipelines make continuous data movement a database feature. Load pipelines bring each new file in on a schedule, tracked by name and retried on failure. Export pipelines publish the rows in each run’s time window, or full snapshots, on a schedule. With write-only upload links for producers and the converged capabilities of Autonomous AI Database, they keep analytics, machine learning and graph workloads working on current data, with the scheduling, tracking and monitoring managed by the database itself.

References

• About Data Pipelines on Autonomous AI Database
• DBMS_CLOUD_PIPELINE package and attributes
• Monitor and troubleshoot pipelines
• Use Oracle-maintained pipelines
• Configure policies and roles to access resources (resource principal, ARNs, Azure service principal, Google service account)
• DBMS_CLOUD format options for EXPORT_DATA
• DBMS_CLOUD subprograms and REST APIs
• Using pre-authenticated requests (OCI Object Storage)

Continuous Data Movement in Oracle Autonomous AI Database with Data Pipelines was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Key Takeaways

• Build a private Python API foundation using FastAPI, OCI Kubernetes Engine, Terraform, and OCI DevOps.
• Track the serving release through version, commit, and environment metadata, preparing for canary verification.
• Package repeatable containers with pinned dependencies, unique release identifiers, and restricted runtime permissions.
• Use OCI Bastion for temporary administrative access while keeping the cluster and application private.
• Connect source, images, deployment manifests, and delivery permissions ahead of the canary rollout in Part II.

A successful container build is only one part of releasing an application. We also need somewhere to run it, a controlled way to reach it, and a way to identify the version answering our requests.

We built a small Python API and developer portal on Oracle Cloud Infrastructure (OCI), with a private Oracle Kubernetes Engine (OKE) cluster and OCI DevOps delivery services. This post explains the foundation. Part 2 follows a commit through testing, a 10% canary rollout, approval, and production promotion.

Naming note: Resource names such as demo-compartment, demo-production, and demo-canary are illustrative pseudonyms. Actual compartment, region, tenancy, repository, cluster, identity, network-address, and release identifiers are omitted.

Choose a small, private deployment

We placed the resources in a dedicated compartment, represented here as demo-compartment, within one OCI region. A compartment groups resources and provides a scope for access policies. Keeping regional resources together simplifies the demo's connectivity.

Our OKE Basic cluster used one VM.Standard.E4.Flex worker with 1 OCPU and 4 GB RAM, plus a 50 GB boot volume. This was sufficient for our stateless application’s production, candidate, and router pods.

That is a demonstration footprint, not a general sizing rule. The workloads share one worker, so a worker failure affects all of them. Namespace separation does not provide host redundancy.

We kept the Kubernetes API, worker, and application private. OCI Bastion provided temporary administrative access from an allowlisted client address.

Make the application identify itself

We used FastAPI to handle HTTP requests and Uvicorn to run the application server. The same application serves the portal page and API endpoints, keeping the demo to one application image.

The portal describes the delivery path and displays the serving revision. Pipeline execution and approval records remain in OCI DevOps.

EndpointWhat it tells us/The portal is reachable/api/helloThe example API contract behaves correctly/healthzThe application process responds/readyzThe application is ready for traffic/api/releaseWhich version, commit, and environment answered/docsThe API contract can be explored interactively

Liveness and readiness serve different purposes. Repeated liveness failures can cause Kubernetes to restart a container. Readiness failures exclude an unready pod from normal Service traffic.

Our readiness signal follows application startup and shutdown. There is no database or other application dependency to check. If we added one, we would need to reconsider the readiness contract.

Release metadata becomes especially useful when two versions run simultaneously. Both may return HTTP 200, but only one is the candidate. The release endpoint reports version, commit, and environment, allowing us to attribute each response without exposing credentials.

The image records the source and version; deployment configuration supplies the environment. This creates a traceable connection from source to the request we observe.

Package a repeatable container

A container image packages the application, runtime, and dependencies. A container is a running instance of that image. Kubernetes runs containers inside pods.

Our Dockerfile starts with a digest-pinned Python 3.12 base image and installs dependencies with package-hash verification. It copies in the application, selects a non-root user, and starts Uvicorn on port 8080.

Pinning versions and hashes controls what enters the build. It also means updates must be deliberate: a pinned dependency does not update itself when a fix becomes available.

Each build creates a version using this structure:

<source-commit-prefix>-<generated-build-suffix>

The version connects the image tag, manifest artifact, and application metadata. Separate builds receive distinct identifiers, and deployment avoids latest.

We verified execution with a read-only root filesystem, dropped Linux capabilities, and no privilege escalation. These restrictions reduce what the process can change and are also reflected in the OKE workload configuration.

A tag names an image reference; a digest identifies image content. We later compared the running production digest with OCIR metadata. This provided evidence beyond the application’s own version string.

The container repository did not accept enforced immutability in this exercise. We used unique tags and restricted writers; immutable versions were enforced for the separate manifest repository.

Build the private foundation with Terraform

Terraform describes both the infrastructure and delivery services. A plan previews proposed changes, an apply carries them out, and later plans reveal differences between configuration and deployed resources.

We separated the network and OKE configuration from the DevOps configuration so their responsibilities remained clear.

The private network used an isolated Virtual Cloud Network (VCN), with subnets for the Kubernetes API, workers, and access resources. Outbound connectivity came from two gateways:

ComponentRoleNAT gatewayAllows private resources to initiate internet connections without public worker addressesService gatewayProvides access to supported OCI services through the OCI service network

Network rules constrain those paths. Outbound connectivity supports image retrieval and service communication without publishing the application for inbound internet traffic.

OKE manages the Kubernetes control plane, while the worker supplies CPU and memory for pods. We organized workloads into namespaces represented here as demo-production, demo-canary, and demo-router. These names will also appear in the release discussion.

Reach OKE through OCI Bastion

The private Kubernetes API cannot be reached directly from a laptop across the public internet. We created a time-limited OCI Bastion session restricted to one allowlisted client address.

An SSH tunnel forwarded a laptop port to the private Kubernetes API. Through that connection, kubectl port-forward forwarded a second local port to the router Service.

Laptop browser
      ↓ local forwarded port
kubectl port-forward
      ↓ API connection through Bastion
Private OKE router Service
      ↓
Application Service and pod

The browser used localhost, but the application ran in OCI. A dedicated kubeconfig kept the connection separate from existing Kubernetes contexts, and API certificate verification remained enabled.

Access requires an active Bastion session and forwarding processes. This is a temporary administrative access path rather than a permanent application URL.

Connect the OCI delivery services

The delivery foundation gives each artifact and operation a home:

OCI resourceResponsibilityDevOps project and Code RepositoryGroup delivery resources and store sourceManaged build pipelineTest source and build release artifactsPrivate OCI Container Registry (OCIR)Store application imagesGeneric Artifact repositoryStore versioned deployment manifestsOKE deployment environmentIdentify the target cluster and connectivityBaseline and canary pipelinesEstablish production and release later candidates

The image contains the application; the manifest tells Kubernetes how to run it. It describes the image reference, replicas, probes, security settings, and routing resources. Delivering both lets promotion consume the artifact evaluated during the canary.

OCI dynamic groups identify authorized pipeline resources, and IAM policies grant their required access. Image pulling uses a separate auth-token-only identity with read access restricted to the application repository. Kubernetes pull secrets provide that credential in both application namespaces, outside the image, source control, and Terraform state.

The foundation now connects identifiable application code, a versioned image, private compute, controlled access, and delivery permissions. The next step is to prove that a source change can move through all of them.

Continue to part 2: Release through an OCI DevOps canary

FAQs

What does Part I cover?

Part I establishes the Python application, container image, private OKE infrastructure, administrative access, and OCI delivery services. Part II follows a source change through canary deployment, approval, and production promotion.

Is this architecture production-ready?

It is a demonstration foundation. All workloads share one worker, so a worker failure affects the application and router. Production use requires additional resilience, capacity planning, and operational validation.

How do I access the private application?

A time-limited OCI Bastion session provides an SSH tunnel to the private Kubernetes API. From there, kubectl port-forward makes the router Service accessible through localhost while the application remains in OCI.

Why does the application expose release metadata?

Successful HTTP responses do not identify which version served them. The release endpoint reports the version, commit, and environment so requests can be attributed to the baseline or candidate during a canary rollout.

How are application images and deployment manifests different?

The image contains the application, runtime, and dependencies. The manifest defines how Kubernetes runs it, including replicas, probes, security settings, and routing. Versioning both supports promotion of the release evaluated during the canary.

Building a Canary Release Pipeline with OCI DevOps (Part I) was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Key Takeaways

• Build a commit-triggered release pipeline with OCI DevOps: test, package, publish artifacts, and deploy a canary to private OKE.
• Verify which release actually serves requests; healthy pods and a configured traffic weight do not prove the traffic split works.
• Evaluate the candidate with 10% of traffic, then approve promotion using the same release artifacts without rebuilding.
• Retain known-good artifacts for recovery. Deployment-stage rollback does not detect every application regression.

A delivery pipeline becomes useful when we can follow a source change into a running application, check its behavior, and control its promotion. A green build is a starting point; the serving revision and traffic path provide the next evidence.

In part 1, we built a Python API, packaged it, and established private OKE infrastructure with OCI delivery services. Here we follow the release: baseline deployment, commit-triggered build, 10% canary, approval, and production verification.

Naming note: Namespace names such as demo-production and demo-canary are pseudonyms. Actual environment names, locations, network addresses, and release identifiers are omitted. Results describe the exercise verified on October 4, 2026, rather than current cloud health.

Establish production before introducing a candidate

A canary rollout needs a working production version to receive most requests. We first ran the managed build with automatic canary delivery disabled, then deployed its artifacts through the separate baseline pipeline into demo-production.

Several Kubernetes objects work together:

ObjectResponsibilityDeploymentMaintains application pods and coordinates updatesPodRuns the application containerServiceGives ready pods a stable destinationIngressDescribes HTTP routing rulesRouterImplements those rules and forwards requests.

An Ingress needs a controller to implement its rules. A Service remains stable as individual pods are replaced and their addresses change.

The private demo runs one application replica per application namespace. Startup, liveness, and readiness probes check the application, and the Service selects ready pods.

Through the private router, we verified health, readiness, the example API response, and the expected release identity. This established a known working baseline before we enabled the complete commit-triggered path.

Turn a source push into a release

We enabled the trigger for pushes to main in OCI Code Repository and the build stage that starts canary delivery.

The resulting path connected source, artifacts, and deployment:

Push to main
↓
Test the API
↓
Build and smoke-test the container
↓
Publish image and manifest
↓
Deploy candidate to OKE
↓
Shift 10 percent of traffic
↓
Wait for approval
↓
Promote the tested release

The build specification directed the runner to:

1. Install hash-verified dependencies and run API tests.
2. Create a unique release version and build the amd64 image.
3. Start the image with the intended runtime restrictions.
4. Run smoke checks and verify the reported version.
5. Render a manifest containing the release’s image reference and metadata.

OCI pipeline stages then delivered the image to OCIR, delivered the manifest to Artifact Registry, and triggered the deployment pipeline with that version. The verified source push started this process automatically, and every build stage succeeded.

This connects continuous integration with continuous delivery. Tests and packaging happen automatically, while production promotion retains an approval point.

Use native OCI DevOps canary stages

The deployment pipeline has four stages:

StageBehaviorCandidate deploymentApplies the candidate into demo-canaryTraffic shiftAssigns 10% of normal traffic to itApprovalWaits for one recorded approvalProduction deploymentApplies the evaluated release into demo-production

The established version continues serving most requests while the candidate is checked. The configured traffic-shift batch delay is not an automated error-rate or latency gate; observation remains necessary.

OCI manages NGINX-style canary annotations. Our private router used Traefik v3.7.13’s NGINX compatibility provider to interpret them. This integration needed verification with live requests.

Diagnose the traffic path before approving

Our first sample revealed a mismatch: the candidate was healthy and its ingress showed a 10% weight, yet every sampled request reached the baseline.

The annotation described the desired behavior. The release endpoint revealed the behavior actually occurring.

Router logs and its implementation showed the cause: this provider matches primary and canary ingress rules within the same namespace. Our initial cross-namespace arrangement did not meet that requirement.

We changed the routing layout while preserving separate application namespaces:

Router watches demo-canary
    │
    ├── Primary ingress
    │       ↓
    │   ExternalName Service alias
    │       ↓
    │   Production Service in demo-production
    │
    └── Candidate ingress
            ↓
        Candidate Service in demo-canary

An ExternalName Service supplies a DNS alias. Here it points to the production Service’s internal Kubernetes name. It does not expose a public endpoint or move production pods.

The matching ingress rules now reside together in demo-canary, while OCI manages the candidate ingress and traffic weight. The router's workload permissions are scoped to that watched namespace.

This is why ingress compatibility must be tested beyond checking that deployment resources exist. Controller matching rules can determine whether the configured split takes effect.

Evaluate the candidate using release identity

After correcting the route, we checked candidate health, readiness, API behavior, release identity, and updated portal content. We then sampled the router’s release endpoint:

Response before approvalCountCandidate20Baseline180HTTP errors0Total requests200

The observed split matched the configured 10% share. Future samples need not divide exactly the same way.

We also verified that the redirect-to-canary: always header selected the candidate, allowing deliberate checks through the router without waiting for normal traffic selection.

Counting successful responses alone would have missed the original problem. Identifying which revision answered demonstrated that the candidate was actually receiving traffic.

Approve and promote without rebuilding

After the checks passed, we recorded approval in OCI DevOps with the verification reason. OCI reset normal candidate traffic to zero during the transition and continued to production deployment.

The production stage consumed the delivered release manifest. No new application build occurred between canary evaluation and promotion.

We verified the resulting production workload through the private router:

CheckRecorded resultAll four deployment stagesSucceededProduction podReady, with zero restarts at verificationHealth and readinessPassedPost-promotion sample50 of 50 requests served the new production releaseHTTP errors in that sample0Running image digestMatched OCIR metadata

The digest comparison supplemented the application-reported version with a check of the running image content.

Keep recovery and future delivery ready

The production stage has automatic rollback configured for deployment-stage failure. Retained baseline artifacts also allow a known-good release to be redeployed without rebuilding.

These controls have limits. A ready application can contain a business-logic defect. This demo has no automated error-rate or latency analysis gate, and stage rollback cannot detect every regression after a successful deployment. Its traffic samples demonstrate functionality rather than production capacity. The single worker also provides no host redundancy.

For a later regression, the recovery path is to remove candidate traffic and redeploy a retained known-good release. Deployment runs should not overlap.

We saved the routing fix and verification record, briefly pausing the trigger for that audit commit. After restoring it, we checked that the trigger was active and Terraform reported no infrastructure drift.

The verified recurring workflow is edit → commit → push → OCI build and artifact delivery → private OKE canary → review → approval → production verification. The key operational habit is to check the revision actually serving requests before deciding a release is ready to promote.

FAQs

What do I need before following Part II?

Part I establishes the Python API, container packaging, private OKE infrastructure, and OCI delivery services. A working production baseline is required before introducing the canary.

Does a 10% canary weight guarantee exactly 10% of requests?

No. Individual samples can vary. Check the release identity returned by each request to confirm that both versions receive traffic and the observed split is consistent with the configured weight.

Why did the ingress routing need adjustment?

The Traefik NGINX compatibility provider used in this exercise matched primary and canary ingress rules within the same namespace. An ExternalName Service alias allowed those rules to sit together while the application workloads remained in separate namespaces.

Is the application rebuilt after approval?

No. The production stage uses the release manifest and image already evaluated during the canary phase.

What happens if a release fails?

Automatic rollback is configured for production deployment-stage failure. For a regression discovered after successful deployment, the recovery path is to remove candidate traffic and redeploy retained known-good artifacts. This demo does not include automated error-rate or latency gates.

Read part 1: Build the private OCI foundation for the application and infrastructure design.

Building a Canary Release Pipeline with OCI DevOps (Part II) was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

With python-oracledb 26.1.0, your Python applications connecting to Oracle Database can pick up connection configuration changes in Amazon S3 or AWS Secrets Manager shortly after they are made, without shortening cache lifetimes or waiting for the normal refresh cycle.

With python-oracledb 26.1.0, your Python applications can pick up connection configuration changes in Amazon S3 or AWS Secrets Manager shortly after they are made, without shortening cache TTLs or waiting for the normal refresh cycle.

Key Takeaways

• Configuration changes reach your application without waiting for the cache to expire. python-oracledb caches centralized configuration for 24 hours by default. Starting with python-oracledb 26.1.0, a change in Amazon Simple Storage Service (Amazon S3) or AWS Secrets Manager expires the cached copy when it happens.
• One connection string option turns it on. Add the URL-encoded Amazon Simple Queue Service (Amazon SQS) queue URL as the queueurl parameter of a config-awss3:// or config-awssecretsmanager:// Data Source Name (DSN). Your application code doesn’t change.
• Updates are safe, and deletions are strict. After an update, the new configuration is retrieved on the next use, and the grace period still covers a brief AWS outage. After a deletion, the cached configuration is removed, so the next connection fails instead of using stale settings.
• Existing connections are left alone. Notifications only update the cache. New connections and pools use the new configuration, and open ones keep working as they are.
• Your secrets stay where they are. Notifications only name the changed resource. The configuration is still retrieved directly from Amazon S3 or AWS Secrets Manager with your existing AWS credentials.

Starting with python-oracledb 26.1.0, configuration change notifications take the python-oracledb AWS configuration provider plugin a step further. In our earlier post, you saw how python-oracledb can read Oracle AI Database 26ai connection configuration from Amazon S3 or AWS Secrets Manager through a single DSN. Now, when that configuration changes in AWS, your application finds out without waiting for its cache to expire.

This post walks you through why this matters, how the feature works, and how to set it up for Amazon S3 and AWS Secrets Manager.

Why Wait for the Cache to Expire?

To avoid calling AWS on every connection, python-oracledb caches the configuration it reads. The config_time_to_live setting controls how long, and the default is 86,400 seconds, or 24 hours.

Picture your team moving a service to a new database endpoint. You upload the new config.json to Amazon S3, and the source of truth is updated in seconds. Your running applications keep using the old cached endpoint until their Time-To-Live (TTL) runs out, up to a day later.

You have two workarounds, and both have a cost. You can restart every application that uses the configuration, which turns a configuration change into a deployment task. Or you can shorten the TTL, which means more requests to AWS, while your applications still refresh on a timer rather than when something actually changed.

Configuration should be refreshed when it changes, not when a timer runs out. That’s what configuration change notifications do.

How Configuration Change Notifications Work

Configuration change notifications let the AWS configuration provider plugin tell python-oracledb that a cached configuration is out of date. They rely on a one-time setup in your AWS account: an Amazon Simple Queue Service (Amazon SQS) queue and an Amazon EventBridge rule that routes change events to it. For Amazon S3, you also enable EventBridge notifications on the bucket. For AWS Secrets Manager, AWS CloudTrail delivers the secret’s change events. You then enable notifications for each connection string with one option: queueurl, the URL of your queue.

Once this is set up, one change flows like this:

1. You update or delete the configuration in Amazon S3 or AWS Secrets Manager.
2. Amazon S3, or AWS Secrets Manager through CloudTrail, reports the change as an event.
3. Your EventBridge rule matches the event and sends it to your SQS queue.
4. The plugin, listening on that queue, finds the connection strings that use the changed configuration and tells python-oracledb to expire their cached copy, or to remove it if the configuration was deleted.
5. Once the plugin has processed the notification, the next oracledb.connect() or oracledb.create_pool() call with that connection string retrieves the new configuration.

The plugin never downloads configuration from a notification, and notifications about other resources leave your cached entry untouched. The AWS resources remain the source of truth: the notification is only a signal that says “look again”.

The plugin uses SQS long polling to receive notifications. It runs a background thread that keeps asking the queue for new messages. There is no fixed interval: each request waits up to 5 seconds, and returns immediately when a message arrives, so a change is picked up within seconds.

The figure shows the full path of a change. The queueurl option in the connection string tells the plugin which queue to listen on. Whatever the event, the plugin only updates the cache: it expires the entry for an update and removes it for a deletion. For an update, the new configuration is retrieved on the first connection after the notification has been processed.

Before You Start

You need:

• python-oracledb 26.1.0 or later, installed with the AWS configuration provider: python -m pip install oracledb[aws_config] (installation guide).
• A standard Amazon SQS queue whose policy allows Amazon EventBridge to send messages to it (Amazon SQS permissions for EventBridge).
• Permission for the application’s AWS identity to call sqs:ReceiveMessage and sqs:DeleteMessage on that queue, in addition to the read access it already has to the configuration. You grant these by adding them to an IAM policy attached to the user or role your application runs as. See Amazon SQS identity-based policy examples.

Set Up Notifications for Amazon S3

1. Enable Amazon EventBridge notifications on the bucket.
2. Create an EventBridge rule that matches Object Created and Object Deleted events for the bucket and targets your SQS queue.
3. Add the URL-encoded queue URL as the queueurl parameter of the connection string.

import oracledb
import oracledb.plugins.aws_config_provider
dsn = (
    "config-awss3://my-bucket/config.json"
    "?aws_region=us-east-1"
    "&queueurl=https%3A%2F%2Fsqs.us-east-1.amazonaws.com%2F123456789012%2Fconfig-updates"
)
with oracledb.connect(dsn=dsn) as connection:
    with connection.cursor() as cursor:
        cursor.execute("select user from dual")
        print(cursor.fetchone())

The only change from a regular AWS configuration DSN is the queueurl parameter, the URL of your SQS queue. Characters such as : and / have a special meaning in a URL, so inside a query parameter they must be percent-encoded: %3A stands for : and %2F stands for /. In Python, urllib.parse.quote(queue_url, safe="") produces the encoded value for you. When someone uploads a new version of config.json, the first oracledb.connect() call with this DSN after the notification has been processed uses the new configuration.

Set Up Notifications for AWS Secrets Manager

For a configuration stored in a secret, the change events come through AWS CloudTrail:

1. Make sure a CloudTrail trail records Secrets Manager management events.
2. Create an EventBridge rule that matches Secrets Manager events from CloudTrail and targets your SQS queue. Include both detail-type values, AWS API Call via CloudTrail and AWS Service Event via CloudTrail. AWS’s guide to matching Secrets Manager events with EventBridge shows example rule patterns.
3. Add the URL-encoded queue URL as queueurl:

dsn = (
    "config-awssecretsmanager://app-config?aws_region=us-east-1"
    "&queueurl=https%3A%2F%2Fsqs.us-east-1.amazonaws.com%2F123456789012%2Fconfig-updates"
)

The plugin reacts to events that change a secret’s value or lifecycle, such as PutSecretValue and UpdateSecret, by expiring the cached configuration. A DeleteSecret event removes it instead. The python-oracledb documentation lists every supported event.

What Happens When the Configuration Changes

A notification changes only the cache. It never opens a connection, retrieves configuration, or touches a connection that is already open. The table shows the two cases.

The difference is deliberate. An update should not cause an outage, so the grace period still covers a brief AWS problem. A deletion means the configuration should no longer be used, so stale settings are not kept.

The grace period starts when the notification is processed. From then on, the expired configuration is used only if AWS can’t supply the new one, for up to 30 minutes by default (config_time_to_live_grace_period). After that, the next connection attempt raises an error.

Existing connections and pools keep the settings they were created with. To pick up a new connect descriptor or pool size, the application creates a new pool or new connections.

TTL-based refresh still applies. Notifications make a change visible sooner; they do not replace config_time_to_live as a safety net if a notification is missed.

Things to Know Before Production

• Give each application process its own queue. Amazon SQS delivers each message to only one reader, and the plugin deletes messages after reading them. If two processes share a queue, each one misses some of the other’s notifications. One process can use several queues, and one queue can serve many connection strings in that process.
• Route only relevant events to the queue. Each queue should receive notifications only for the connection strings that use it. The plugin ignores unrelated events, but they still cost a delivery.
• Keep caching on. If config_time_to_live is set to 0, nothing is cached, so the plugin doesn’t listen for notifications even when queueurl is set.
• Watch for warnings. If the plugin can’t process a message, it issues a Python RuntimeWarning and keeps listening.

Conclusion

Centralized configuration moved connection details out of your code. Configuration change notifications close the remaining gap: when the configuration in Amazon S3 or AWS Secrets Manager changes, your application uses it on its next connection after the notification is processed, without a shorter TTL, a restart, or extra code.

Start with one connection string and one queue, keep your TTL as a safety net, and let AWS tell python-oracledb when to look again.

FAQs

1. Do I need to change my application code?
No. After the one-time AWS setup (an SQS queue and an EventBridge rule), you only add the queueurl parameter to the connection string. The plugin handles the rest: it polls the queue in the background and updates the cache when a notification arrives.

2. Does a notification reconnect my application?
No. A notification only updates the cache. Open connections and pools keep working with the settings they were created with, and new connections and pools use the new configuration.

3. What if AWS is unavailable when the next connection is made?
After an update, python-oracledb keeps using the expired configuration until the grace period ends, 30 minutes by default, counted from when the notification was processed. After a deletion, there is no fallback, and the connection attempt raises an error.

4. Do I still need queueurl and a TTL?
queueurl is optional: without it, the configuration is cached and refreshed by config_time_to_live alone, as before. With it, keep a sensible config_time_to_live as a safety net in case a notification is not delivered.

5. Can several connection strings share one queue?
Yes, within one application process. The plugin matches each notification to the connection strings that use the changed configuration.

python-oracledb Resources

python-oracledb is the open source Python driver for Oracle AI Database 26ai.

• Documentation
• AWS centralized configuration providers
• Caching configuration information
• Installation
• AWS configuration provider plugin
• Source code on GitHub
• GitHub discussions
• PyPI

AWS Resources

• Amazon S3 and Amazon EventBridge
• Matching AWS Secrets Manager events with Amazon EventBridge
• Amazon SQS Developer Guide
• Amazon EventBridge rules
• AWS CloudTrail User Guide
• IAM security best practices

Event-Driven Configuration Refresh for Oracle AI Database 26ai with python-oracledb, Amazon S3, and… was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Starting with python-oracledb 26.1.0, your applications can pick up configuration changes in Google Cloud Storage or Secret Manager shortly after they are made, by adding one connection string parameter, with no restarts and no shorter cache lifetimes.

The python-oracledb 26.1.0 release adds configuration change notifications for the Google Cloud configuration provider plugin.

Key Takeaways

• Configuration changes no longer need to wait up to 24 hours. With python-oracledb 26.1.0, Google Cloud Pub/Sub notifications expire affected cached configurations when the configuration in Cloud Storage or Secret Manager changes, so the next use retrieves the updated settings instead of waiting for the default 24-hour cache.
• One connection string parameter turns it on. Add subscriptionname, the full name of your Pub/Sub pull subscription, to a config-gcpstorage:// or config-gcpsecretmanager:// Data Source Name (DSN). No additional application code is needed.
• Your configuration stays secure in Google Cloud. Pub/Sub notifications identify the changed resource without exposing its contents. python-oracledb retrieves the configuration directly from Cloud Storage or Secret Manager using your existing Google Cloud credentials.
• Updates bring fresh settings; deletions keep outdated ones out. An updated configuration file or a new secret version is used on the next connection. A deleted configuration file, or a disabled or destroyed secret version, is removed from the cache once the notification is processed, so outdated settings are not reused.
• Existing connections keep running unchanged. Notifications only affect cached configuration. New connections and pools use the updated settings, while existing connections and pools keep their current configuration.

In our earlier post, you saw how the python-oracledb Google Cloud Platform (GCP) configuration provider plugin lets your application connect to Oracle AI Database 26ai through a single DSN, with the connection details stored in Google Cloud Storage or Google Cloud Secret Manager. Your code stays the same; the configuration lives in Google Cloud.

There was one catch. python-oracledb caches that configuration. So even when the configuration changes, your application keeps the old copy until the cache expires. python-oracledb 26.1.0 closes that gap: the plugin now listens to Google Cloud Pub/Sub and refreshes the cached configuration when it changes.

In this post, you’ll see why that matters, how it works, and how to turn it on with one connection string parameter.

Your Configuration Changed. Why Hasn’t Your Application?

Imagine your team updates a database connection configuration stored in Google Cloud Secret Manager. The new version is available, but your application continues using the old settings. Nothing is broken. It’s simply using a cached copy.
To avoid contacting Google Cloud on every connection, python-oracledb caches the configuration it retrieves. The config_time_to_live setting controls how long it stays cached, with a default of 86,400 seconds, or 24 hours.
Until now, you had two common ways to pick up changes sooner: restart every application that uses it, or shorten the Time-To-Live (TTL). Neither is ideal. Restarts add operational work, while shorter TTLs mean more requests to Google Cloud, even when nothing has changed.

Your application shouldn’t have to wait for a timer. Google Cloud can tell it when something changes.

That’s the idea behind event-driven configuration change notifications.

How a Change Finds Its Way to Your Application

Instead of waiting for the cache to expire, python-oracledb can receive a notification from the Google Cloud Pub/Sub messaging service when your configuration changes.

The setup connects three components: Google Cloud Storage, where the configuration is stored as a JSON object; Google Cloud Secret Manager, where it is stored as a versioned secret; and Pub/Sub, which delivers change notifications to the python-oracledb GCP configuration provider plugin. Pub/Sub uses a topic to receive events and a pull subscription to deliver them. You enable notifications by adding subscriptionname, the full name of that subscription, to the connection string.

Here’s what happens when a configuration changes:

1. A resource changes in Google Cloud. You update or delete a configuration file in Cloud Storage, or add, disable, or destroy a secret version, or delete the secret, in Secret Manager.
2. Google Cloud publishes a notification. The service sends an event to your Pub/Sub topic, naming the affected resource.
3. Pub/Sub delivers the event to your application’s pull subscription. The plugin keeps a streaming pull connection open on the subscription, so the event arrives as soon as Pub/Sub delivers it. There is no polling interval to configure.
4. The plugin updates the matching cache entries. It finds the connection strings that use the changed resource and expires their cached configuration, or removes it after a deletion. Unrelated configurations remain untouched.
5. The next use retrieves the configuration. The next oracledb.connect() or oracledb.create_pool() call reads the configuration from Cloud Storage or Secret Manager.

The figure traces one change from start to finish. The subscriptionname parameter in your DSN points the plugin to its Pub/Sub subscription. Each event names only the resource that changed. The plugin expires the matching cache entry after an update or removes it after a deletion, and python-oracledb reads the configuration again on the next connection.

Google Cloud signals the change. python-oracledb retrieves the configuration when it’s needed.

Before You Start

You need:

• python-oracledb 26.1.0 or later, installed with the GCP configuration provider: python -m pip install oracledb[gcp_config] (installation guide).
• A Pub/Sub topic that receives the change events, and a pull subscription on it for your application.
• The Pub/Sub Subscriber role (roles/pubsub.subscriber) on the subscription for the application’s identity, in addition to its read access to the configuration, and the Pub/Sub Publisher role (roles/pubsub.publisher) on the topic for your project’s Cloud Storage service agent, so the bucket can publish notifications (Pub/Sub notifications for Cloud Storage).

Set Up Notifications for Cloud Storage

1. Allow the Cloud Storage service agent to publish to your topic.
2. Create a Pub/Sub notification on the bucket that sends object events to your topic, using the JSON payload format.
3. Add the full subscription name as the subscriptionname parameter of the connection string.

import oracledb
import oracledb.plugins.gcp_config_provider

dsn = (
    "config-gcpstorage://project=my-project;bucket=my-bucket;object=config.json"
    "?subscriptionname=projects/my-project/subscriptions/config-updates-app1"
)

with oracledb.connect(dsn=dsn) as connection:
    with connection.cursor() as cursor:
        cursor.execute("select sys_context('userenv', 'service_name') from dual")
        print(cursor.fetchone())

Compared with a regular Cloud Storage DSN, only subscriptionname is new. It takes the full name of your subscription, in the form projects/<project>/subscriptions/<subscription>, exactly as Google Cloud shows it. The query prints the database service the application is connected to, so after you upload a new config.json that points elsewhere, the next oracledb.connect() call shows the change.

Set Up Notifications for Secret Manager

Secret Manager publishes event notifications directly to Pub/Sub, so there is no extra service to configure:

1. Allow the Secret Manager service agent to publish to your topic.
2. Attach the topic to your secret.
3. Add the subscription name to the connection string:

dsn = (
    "config-gcpsecretmanager://projects/my-project/secrets/app-config/versions/latest"
    "?subscriptionname=projects/my-project/subscriptions/config-updates-app1"
)

Secret Manager stores configuration in versions, and the plugin follows them. When you add a version (SECRET_VERSION_ADD), connection strings that read versions/latest get the new version on their next connection. When you disable or destroy a version (SECRET_VERSION_DISABLE, SECRET_VERSION_DESTROY), the cached configuration that used it is removed. When you delete the secret (SECRET_DELETE), it is removed for every connection string that reads that secret.

Update or Retire: How python-oracledb Reacts

Not every change means the same thing. Uploading a new configuration file or adding a secret version says “use this instead”. Deleting the file or disabling a version says “stop using this”. python-oracledb treats the two differently, and only ever touches its cache, never your open connections.

An update doesn’t cause an immediate outage. If Google Cloud is briefly unavailable when the next connection needs the new settings, python-oracledb keeps using the expired copy during the grace period (config_time_to_live_grace_period, 30 minutes by default). The grace period starts when the notification is processed. After it ends, the connection attempt raises an error.

A retirement is final. Once a configuration file is deleted, a secret version is disabled or destroyed, or the secret is deleted, the cached copy is gone, so retired settings can’t be reused by mistake.

Two things don’t change:

• Open connections and pools keep running with the settings they were created with. New connections and new pools pick up the change.
• Your config_time_to_live still applies. Notifications make changes arrive sooner; the TTL is the safety net if one is ever missed.

Ready for Production: A Short Checklist

Before you turn on notifications for a production service, check these points:

• Each application process has its own subscription. Pub/Sub hands each message to only one of the clients pulling from a subscription. If two processes share one, each sees only part of the changes. Create one subscription per process on the same topic; within a process, one subscription serves all its connection strings.
• The subscription is a pull subscription. The plugin pulls the events itself, so a push subscription won’t deliver anything to it.
• Only configuration events reach the topic. Filter the Cloud Storage notification by object name prefix so that only your configuration files publish events. Unrelated events are ignored, but each one still costs a delivery.
• Caching is on. With config_time_to_live set to 0, nothing is cached, so there is nothing to refresh, and the plugin doesn’t listen even when subscriptionname is set.
• The application can wait out a short outage. If the connection to Pub/Sub drops, the plugin reconnects on its own with increasing delays, and the TTL keeps refreshing the configuration in the meantime.

Conclusion

Centralized configuration took connection details out of your code. Configuration change notifications take the waiting out of changing them. Upload a new configuration file to Cloud Storage or add a secret version in Secret Manager, and your application uses it on its next connection: no restart, no shorter TTL, no new code.

Start with one subscription and one connection string, keep your TTL as a safety net, and let Google Cloud tell python-oracledb when something changes.

FAQs

1. What do I change in my application?
Only the connection string: add the subscriptionname parameter. The plugin receives the notifications and updates the cache for you.

2. Will a notification interrupt my running connections?
No. It only expires or removes the cached configuration. Open connections and pools keep working; new ones use the updated configuration.

3. My connection string reads a fixed secret version. Is it refreshed when I add a new version?
No. Only connection strings that read versions/latest follow new versions. A fixed version is removed from the cache only if that version is disabled or destroyed.

4. Can Cloud Storage and Secret Manager use the same subscription?
Yes. One topic and one subscription can carry both, and every connection string in the process can share that subscription.

5. What happens if Google Cloud is unavailable at the next connection?
After an update, python-oracledb keeps using the expired configuration during the grace period, 30 minutes by default, counted from when the notification is processed. After a deletion, there is no fallback, and the connection attempt raises an error.

6. Do I still need config_time_to_live?
Yes. Keep a sensible value as a safety net in case a notification is ever missed. With 0, nothing is cached and notifications have no effect.

python-oracledb Resources

python-oracledb is the open source Python driver for Oracle AI Database 26ai.

• Documentation
• GCP centralized configuration providers
• Caching configuration information
• Installation
• GCP configuration provider plugin
• Source code on GitHub
• GitHub discussions
• PyPI

Google Cloud Resources

• Pub/Sub notifications for Cloud Storage
• Secret Manager event notifications
• Pub/Sub overview
• Pub/Sub subscriptions
• Pub/Sub access control
• IAM best practices for service accounts

Change-Aware Configuration Caching for Oracle AI Database 26ai with python-oracledb and Google… was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

By Kailas Jawadekar

A collection of documents can hold valuable knowledge while still being difficult to use. Finding an answer may require opening several files, searching for related terms, and reconciling different versions. We built a Document Knowledge Base to make that content searchable and reusable through natural-language questions, generated drafts, and document comparisons.

The Document Knowledge Base combines Oracle APEX, PL/SQL, Oracle AI Database vector storage, and AI inference. Its core workflow is independent of the subject matter: organize a document collection, retrieve relevant passages, and use them to answer questions or create new content. Support articles, developer guides, operational runbooks, and product documentation are all examples of collections suited to this pattern.

Key takeaways

• Ingest DOCX documents into stored files, metadata, text chunks, and embeddings.
• Use Oracle AI Vector Search and RAG to retrieve evidence for each question.
• Ask for source-grounded answers with filenames and headings to support review.
• Generate editable content from retrieved passages and prompt templates.
• Compare selected documents within a bounded context; check coverage before interpreting omissions.

Review generated results: tables, long passages, missing embeddings, or limited retrieval can leave evidence out. Check answers, drafts, and comparisons against the originals.

Implementation scope

Document Knowledge Base is a simple internal project, not a production deployment. It demonstrates a reusable workflow for finding answers, drafting content, and comparing documents. The collection supplies the subject matter: support guidance, developer documentation, operational procedures, onboarding material, or product knowledge. These broader examples illustrate how the pattern can be adapted; each needs suitable documents, prompts, and coverage checks.

What users can do

The Document Knowledge Base organizes work around three capabilities:

• Ask a question. Semantic search retrieves relevant passages and generates an answer with source references. For a support collection, a question might be “How do I troubleshoot a connection timeout?”
• Create a draft. Retrieved content and template instructions guide an output such as a summary, knowledge article, or response draft. The document collection and prompt determine the subject and format.
• Compare documents. A selected document and baseline provide the evidence for a structured comparison. In a documentation collection, the task might be to summarize changes between two versions.

The current uploader adds DOCX files and metadata. Administration views show document coverage, ingestion status, and missing embeddings.

Use cases for a Document Knowledge Base

The collection and task give the knowledge base its purpose. These examples illustrate applications of the same architecture; each requires suitable source content and prompts.

How the architecture works

Oracle APEX supplies the browser interface, forms, and reports. PL/SQL packages extract text, retrieve passages, assemble prompts, and call the AI service. Oracle AI Database provides native vector storage and AI Vector Search alongside relational data. In this implementation, the schema stores original DOCX BLOBs, filenames, dates, hashes, processing status, extracted text, ordered chunks, and embeddings. It also stores search questions and query vectors, generated answers, reusable prompt templates, and assembled prompts and outputs for generated reports and comparisons. The answer package stores answer text, but does not persist its assembled prompt.

Keeping these records in Oracle AI Database lets SQL join evidence and metadata directly, apply category filters, and retrieve saved generation prompts and outputs without synchronizing a separate vector store. This makes the workflow easier to inspect and maintain; it does not by itself guarantee complete extraction or accurate answers.

The application has two main flows:

Document → Extract text → Split into chunks → Create embeddings → Store
Question → Create embedding → Find relevant chunks → Generate answer

This is retrieval augmented generation, or RAG. The model receives selected source passages with each request; we do not train it on the uploaded documents. Keeping vectors alongside relational data lets SQL combine semantic ranking with metadata filters and explicit document selection.

Our AI wrapper is configured for OCI Generative AI endpoints using OpenAI-compatible embedding and chat request formats. Endpoint URLs and model identifiers are configuration values. API secrets belong in APEX Web Credentials and are referenced by the REST call, keeping them out of application code. The checked-in endpoint defaults and model placeholders are setup values, not proof that those combinations are currently supported. Confirm both embedding and chat access in the target environment using the service’s current documentation.

Prerequisites and setup

• Oracle APEX and database support. Use an APEX workspace and parsing schema on an Oracle Database environment that supports VECTOR, TO_VECTOR, and VECTOR_DISTANCE. Verify the installed APEX and database versions; the source does not establish a tested minimum APEX release. The custom packages use APEX_ZIP, APEX_JSON, and APEX_WEB_SERVICE.
• OCI Generative AI access. Configure reachable HTTPS embedding and chat endpoints, authorized model IDs, regional availability, and the required service permissions. Set EMBEDDINGS_URL, EMBEDDING_MODEL, CHAT_URL, and CHAT_MODEL. Test both calls; an OpenAI-compatible chat endpoint does not automatically imply compatible embedding support. The current vector columns and conversion expect 1,536 dimensions, so check the embedding-dimension diagnostic before ingestion.
• APEX Web Credentials. For the project’s bearer-token setup, create an HTTP Header credential with header Authorization and secret value Bearer <API key>, restricted with Valid for URLs to the intended service prefixes. Set WEB_CREDENTIAL_STATIC_ID to that credential’s Static ID. The later project build instructions use credentials-for-standards-ai, while the original seed uses openai-api-key; match the active workspace credential rather than copying either blindly. Configure outbound network access as required by the database environment.
• Schema, pages, and a small DOCX trial. Install the schema and ingestion, AI, search, answer, template, report, and comparison packages, check compilation, and connect the APEX page processes. Start with a representative DOCX and inspect extracted text, chunks, and embeddings before testing answers. The supplied source is not a complete, verified one-click APEX deployment. The current uploader is DOCX-only; PDF, HTML, Markdown, and images need additional parsers.

See Oracle’s Web Credentials guidance and OCI Generative AI API key and endpoint documentation for the supported authentication and service configuration.

Turning documents into searchable content

When a user uploads a DOCX file, the application retains the original file and calculates a SHA-256 hash to detect an identical upload. It uses APEX_ZIP and XMLTABLE to extract body paragraphs, detects headings, and groups text into chunks of approximately 5,000 characters. Each chunk retains its heading and connection to the source document.

Extraction limitation: the XPath reads direct body paragraphs, not paragraphs inside table cells. The helper takes at most 200 characters per XML text run and caps the assembled paragraph at 3,000 bytes. Long passages can therefore be incomplete before chunking; the original DOCX remains the reference for checking coverage. Chunks have no overlap.

The application creates an embedding for each chunk and stores it as VECTOR(1536, FLOAT32). An embedding is a list of numbers representing the text in a form that supports similarity comparisons. Related passages can be close together even when they use different words. For example, “unable to sign in” may match a passage about “authentication failures.”

The embedding function sends text to the configured model, then converts the returned JSON array into an Oracle vector. This excerpt shows the conversion inside our PL/SQL package:

l_response := embedding_response(p_text);
l_embedding_json := json_query(
  l_response, '$.data[0].embedding' returning clob);

select to_vector(l_embedding_json, 1536, float32)
  into l_vector
  from dual;

embedding_response builds the JSON request with the configured embedding model and input text. Document chunks and questions use the same embedding model so their vectors can be compared.

Finding evidence and generating an answer

For each search, we embed the question once and store its vector. SQL ranks document chunks by cosine distance: a smaller distance means greater semantic similarity. The simplified query below shows the core operation using generic knowledge-base table names. Metadata filters can narrow the candidate documents before ranking.

select c.chunk_id, c.heading, c.chunk_text,
       vector_distance(c.embedding,
                       q.query_embedding, cosine) distance
  from kb_doc_chunks c
  join kb_search_queries q on q.search_id = :search_id
 where c.embedding is not null
 order by distance
 fetch first :top_n rows only;

We assemble the selected passages, filenames, headings, and document dates into a prompt. The instructions ask the model to answer the specific question, cite its sources, and say when the supplied material is insufficient. A shared function handles generation:

l_output := kb_ai_pkg.generate_text(
  p_system_prompt =>
    'Ground every claim in provided source material.',
  p_prompt => l_prompt
);

Inside this function, APEX_JSON constructs the chat request and APEX_WEB_SERVICE.MAKE_REST_REQUEST sends it using the configured Web Credential. The response text is extracted from choices[0].message.content and displayed in APEX.

Reusing the evidence for content and comparisons

Content generation adds a topic, output type, and template instructions to the retrieved context. A template defines the audience, structure, and tone while the source passages supply the evidence. Our implementation saves the assembled prompt and generated text and offers a Markdown download.

Document comparison answers a different question: “What changed between these two documents?” Users select a current document and a baseline, then generate a structured comparison with source references. The screenshot shows the current document and baseline selectors, an optional category filter, and the comparison action.

Unlike semantic search, this workflow takes a bounded set of chunks from each selected document in document order. The prompt labels the two sets as current and baseline and asks for supported changes. The comparison defaults to the first 10 embedded chunks per document, with a maximum of 15, subject to the optional category filter. Because context is bounded, an omitted item is not automatically evidence that it was resolved.

The reusable pieces are document storage, extraction, chunking, embeddings, retrieval, and generation. The domain determines the metadata, filters, and prompts. A support repository might filter by product and version; developer documentation might use API, language, and release. Adapting the application also requires parsers for the source formats and access controls appropriate to the collection. Our current uploader handles DOCX files.

Answer quality depends on the evidence supplied. Our current extractor focuses on body paragraphs, so table content and long passages need attention when assessing coverage. Similarity scores do not establish factual accuracy or freshness, and generated answers still need review against their sources.

The Document Knowledge Base brings these pieces into one workflow: add documents, ask questions, create drafts, and compare sources. Keeping documents, metadata, vectors, reusable prompts, and generated artifacts together in Oracle Database supports that workflow with familiar joins and filters. The next step is to validate extraction and retrieval against representative documents, then review generated outputs against their sources before broader use.

The images show the live Document Knowledge Base application. Code snippets are simplified examples using generic kb_ names; they are not standalone installation scripts.

Further reading: Oracle APEX web service API and Oracle vector distance functions.

Frequently asked questions

What is this app for?

Document Knowledge Base makes a DOCX collection searchable and reusable through questions, generated drafts, and selected-document comparisons. For example, a support team could retrieve troubleshooting guidance, a developer team could draft an API summary, or an onboarding team could help employees find procedures. These uses depend on the documents and prompts supplied.

Which file types are supported?

The current uploader and extractor handle DOCX. They do not implement PDF, HTML, Markdown, scanned images, or complete DOCX table extraction. Adding another format requires a parser and coverage validation.

What does RAG do here?

Retrieval augmented generation embeds a question, ranks stored chunks by semantic similarity, and supplies selected passages to the model as context. Instructions request source-grounded answers and source references. The comparison workflow instead takes bounded, ordered chunks from two explicitly selected documents.

Do uploaded documents train the model?

The application code makes embedding and generation calls; it does not train or fine-tune a model. Selected source text is sent to the configured inference service. That implementation fact is not a blanket statement about provider retention or data use: verify the chosen service, model, and applicable terms before uploading sensitive documents.

What limits coverage and answer accuracy?

Table cells are skipped, long XML runs and paragraphs can be truncated, and chunks have no overlap. Missing embeddings, metadata filters, and bounded retrieval or comparison can leave relevant evidence out. Similarity and source citations do not prove correctness or freshness. Check extracted coverage and review every generated answer, draft, and comparison against the original documents.

Building a Document Knowledge Base with Oracle APEX and Oracle AI Vector Search was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Companion articles and notebooks:

• Codex workflow: How to Build a Controlled MCP Workflow for Codex and Oracle AI Database, Companion Codex notebook
• Cursor workflow: Build a Controlled Cursor MCP Workflow with Oracle AI Database, Companion Cursor notebook
• Antigravity workflow: How to Build an Antigravity Workflow with the Oracle SQLcl MCP Server and Oracle AI Database, Companion Antigravity notebook
• Claude workflow: When to use Claude memory, Oracle AI Agent Memory, and LangChain together, Companion Claude notebook

Key takeaways

• The Oracle SQLcl MCP server gives an AI client an explicit tool path to Oracle AI Database, so the assistant can inspect approved database context instead of relying only on schema details copied into a prompt.
• Codex, Cursor, Antigravity, and Claude offer different developer experiences, but the database boundary can stay consistent: approved SQLcl connections, database privileges, inspectable tool calls, and validation before broader access.
• Oracle AI Agent Memory is a separate application layer. Add it when a workflow needs durable threads, scoped retrieval, reusable context, or continuity across sessions. Do not add memory simply because MCP is present.
• The companion notebooks are build and validation harnesses. They help prove configuration, storage, retrieval, and memory patterns, but they are not a substitute for live client validation or production telemetry.
• Start small, validate the database path, and expand access only when the workflow is explainable.

Your AI assistant can write SQL. But does it know your database?

A developer pastes a table definition into a prompt and asks for a query. The assistant returns something that looks right. Then the query fails because a column was renamed last week, the object lives in another schema, the current database user cannot see it, or the application has a rule that never made it into the prompt.

The issue is not that the assistant cannot generate SQL. The issue is that the assistant is reasoning from the context it was given. If the context is incomplete, stale, or simplified, the answer can be perfectly plausible and still be wrong for the environment in front of you.

That is what the title of this guide means by guessing. The goal is not to remove reasoning from the assistant. The goal is to stop making prompt text carry responsibilities that belong to tools, database permissions, and runtime validation.

Across this Oracle Database MCP series, the same pattern appeared in several developer environments. Codex, Cursor, Antigravity, and Claude can all sit at the front of a workflow. The Oracle SQLcl MCP server provides the tool boundary to Oracle AI Database, using approved SQLcl connection profiles instead of database details copied into a prompt. Oracle AI Agent Memory becomes relevant when an application needs durable context beyond the current interaction. LangChain can help with retrieval orchestration when that extra application layer is useful.

This final article brings those pieces together. Think of it as the map for the series: what each layer is responsible for, what changes between clients, what should stay the same, and how to decide when a simple MCP workflow has grown into a memory and retrieval architecture.

This article is the final guide in the Oracle Database MCP series. The earlier posts cover the hands on workflows for Codex, Cursor, Antigravity, and Claude with Oracle AI Agent Memory. Use this guide as the map, then follow the individual article and notebook links when you are ready to run a specific workflow.

What Changes When AI Can Inspect the Database Instead of Relying on the Prompt?

There is a practical difference between describing a database to an assistant and letting the assistant inspect approved database context through a tool. In the first case, the model sees whatever the developer remembered to paste into the prompt. In the second case, the model can ask a declared tool for information at runtime, within the permissions and connection boundaries you configured.

That distinction matters during normal development. Schemas evolve. Grants differ between environments. Object names are similar. Views hide implementation details. Data quality problems show up only when a query actually runs. Once a workflow can check those facts instead of assuming them, the assistant becomes easier to use for real database work and easier to debug when something goes wrong.

This is where context engineering for AI agents becomes practical. The goal is not to paste more and more database detail into the prompt. The goal is to decide which context should come from runtime database inspection, which context should come from retrieved memory, and which context should stay outside the model call entirely.

MCP does not make the model authoritative, and it does not replace database security. It gives the client a standard way to call tools. The Oracle SQLcl MCP server turns that protocol into a tool path for Oracle AI Database. The database still decides what the configured user can do, and the developer still decides which workflows should be automated, reviewed, or blocked.

What MCP Does, and What It Does Not Do

MCP gives an AI client a standard way to discover and call tools, and the Oracle SQLcl MCP server applies that pattern to Oracle Database workflows.

MCP does not replace database privileges. It does not automatically provide durable memory. It does not decide which operations an application should allow. Oracle AI Database still controls what the configured database identity can access and execute.

Agent Memory solves a different problem. It becomes useful when an application needs useful context to survive beyond the current interaction. Keeping tool access, database security, and durable memory as separate concerns makes the architecture easier to test and easier to operate.

One architecture, multiple developer experiences

The most useful way to read the series is to separate the developer experience from the shared foundation. Codex, Cursor, Antigravity, and Claude differ in how they fit into a developer workflow, but the architecture underneath does not need to be reinvented for every client.

Tool discovery and operation control are related, but they are not the same thing. A client may discover a tool, while SQLcl MCP configuration and Oracle Database privileges still determine which operations are allowed to execute

Core idea: SQLcl MCP controls how assistants act through tools. Oracle AI Agent Memory controls what applications remember over time. They can both use Oracle AI Database, but they solve different parts of the architecture.

The Oracle SQLcl MCP Server Is the Execution Boundary

In these workflows, the Oracle SQLcl MCP server is the bridge between an MCP capable client and Oracle AI Database. SQLcl runs in MCP mode and exposes a declared set of tools. The client does not need direct database credentials in the prompt. It uses the MCP server, and the server works through SQLcl connection profiles that the developer has already created and validated.

That sounds like a small implementation detail, but it changes how you reason about the workflow. The assistant is no longer being trusted because a prompt says to behave safely. The database path has an identity, a set of privileges, a known connection, and observable actions. If a query is denied, that can be the correct outcome. If a tool is not exposed, the assistant cannot simply wish it into existence.

For developers, this turns a vague safety discussion into a set of concrete questions. Which saved connection is in use? Which database user owns that connection? What can that user read or change? Which SQLcl MCP capabilities are available? What activity can be inspected afterward? Those questions are much easier to test than a general instruction such as “only do safe things.”

The earlier articles walk through the concrete setup for each client. The important point for this capstone is that the control model belongs below the client. You should be able to swap the developer interface without rebuilding the database permission model from scratch.

One Foundation, Four Developer Experiences

The series uses several clients because developers do not all work in the same place. Some want an agent in the terminal. Some want the workflow inside an editor. Some are evaluating a newer agentic development environment. Some are exploring a general assistant together with application memory. The client changes the ergonomics, not the underlying responsibility model.

Codex: useful when the agent sits close to the implementation loop

The Codex workflow is a good fit when the agent is already working with files, commands, configuration, notebooks, and implementation details. Database access then becomes one more tool in a broader development loop. That proximity is useful because the agent can relate database findings back to code changes, but it also raises the importance of an explicit database boundary.

The companion Codex notebook acts as a validation harness for prerequisites, the SQLcl connection path, memory storage, retrieval, and Agent Memory integration. Keeping those checks separate makes it easier to identify whether a failure comes from MCP discovery, the database connection, privileges, memory, or retrieval.

Cursor: useful when database context belongs inside the editor loop

Cursor keeps the database workflow close to the code editing experience. That can be especially useful when a developer is changing application code and needs to inspect the schema or validate assumptions without leaving the working loop. The same caution applies: convenience should not turn into broad, implicit access.

The Cursor notebook also makes an important distinction: its operational rows are simulated teaching data, not live Cursor telemetry. The notebook demonstrates the database backed pattern without presenting sample data as evidence of live client activity.

Antigravity: useful for testing the same pattern in another agentic environment

The Antigravity article is valuable because it shows that the architecture is portable. The client interface changes, but the workflow still benefits from the same separation: developer facing agent, SQLcl MCP tool boundary, Oracle AI Database, Oracle AI Agent Memory when needed, and optional retrieval orchestration.

The Antigravity notebook follows the same principle. It can validate the memory substrate, retrieval, and Agent Memory path, while live MCP behavior still needs to be verified through the actual client and SQLcl MCP workflow.

Claude: useful for seeing where assistant context ends and application memory begins

The Claude article adds a different question to the series. Once the assistant can use database tools, what should happen when useful context needs to survive the current interaction? The answer is not to make MCP carry memory responsibilities. Tool access and durable application memory are separate concerns, even when they use the same Oracle platform underneath.

That separation is one of the most important ideas in the whole series. An assistant can have useful working context and still need an application memory layer. Likewise, an application can maintain durable memory without giving an assistant unrestricted database access. Keeping those responsibilities separate gives developers clearer tests, clearer ownership, and fewer hidden assumptions.

AI Agent Memory Belongs After the Workflow Has Something Worth Remembering

It is tempting to add memory as soon as an agent demo starts working. In practice, that is often too early. Memory adds value when the workflow has a real continuity problem: a user returns later and expects the application to recall a preference, a developer wants prior tool outcomes available in a new session, or an agent needs to retrieve relevant context from earlier work without replaying an entire conversation.

In this series, AI agent memory refers to Oracle AI Agent Memory, which provides durable memory APIs backed by Oracle AI Database. Oracle AI Agent Memory sits in the application layer on top of Oracle AI Database. It gives Python applications an API for threads, durable memory records, scoped retrieval, and context assembly. Oracle AI Database remains the storage and enforcement layer underneath. That means memory can participate in the same kinds of engineering decisions developers already understand: identity, retention, access control, observability, data quality, and lifecycle.

For Python applications, the oracleagentmemory package provides the application API used to create users, agents, threads, memory records, and retrieval flows.

A useful rule is to ask what failure you are solving. If the problem is “the client cannot inspect the current schema,” add or fix the MCP tool path. If the problem is “the application loses useful context between sessions,” evaluate Agent Memory. If the problem is “we need a reusable retrieval pipeline around that memory,” then LangChain or another orchestration layer may help. Each addition should answer a concrete need.

Thinking in two execution loops

The architecture becomes easier to reason about when you separate the live interaction loop from the durable context loop. They can share Oracle AI Database, but they run for different reasons.

The live interaction loop is immediate. A developer asks a question or requests a task. The client decides that a database tool is needed. SQLcl MCP invokes the approved database path. Oracle AI Database returns a result. The client uses that result to continue the task.

The durable context loop is different. Application code stores selected conversation history, tool outcomes, memory records, metadata, or embeddings. Later, the application retrieves only the context that is useful for the current user or task. That context can then be assembled before the next model call. The point is not to replay everything. The point is to make useful context queryable and scoped.

Scoped retrieval for agents is what keeps this useful. A memory system should retrieve records for the right user, agent, thread, or workflow rather than treating all stored context as equally available. That scope is what helps memory stay relevant and prevents old or unrelated context from being added to the next model call.

The notebooks are validation harnesses, not the product

Every hands-on article in the series includes a companion notebook because a notebook is a practical place to make the moving parts visible. It can check local prerequisites, show sanitized configuration, create teaching tables, insert sample records, exercise lexical and vector retrieval, initialize Agent Memory, and finish with a validation snapshot.

That is useful, but it is important to keep the notebook in the right role. The final user experience happens in Codex, Cursor, Antigravity, or Claude. The notebook helps prove the surrounding database and memory path before or alongside that client. It is not automatically the source of live telemetry, and simulated teaching data should not be presented as proof that a live client action occurred.

Keeping the Database Boundary Consistent

The client can change. The database rules should not.

Codex, Cursor, Antigravity, and Claude all provide different ways for a developer to work with an AI assistant, but changing the client should not mean redesigning database access every time. The SQLcl connection, database identity, permissions, and validation path should remain predictable underneath.

A good starting point is a purpose specific database identity with only the privileges required for the workflow. During development, that usually means beginning with a small, read focused surface and expanding access only when there is a clear reason to do so.

The saved SQLcl connection also becomes part of the application architecture. Developers should know which connection alias is being used, which database user it represents, and which environment that user can access. When something behaves unexpectedly, those details are often more useful than changing the prompt.

The same principle applies to MCP tools. Expose the capabilities the workflow actually needs. A smaller tool surface is easier to understand, test, and troubleshoot than a large collection of tools that nobody has reviewed closely.

Database activity should also remain inspectable. When a client invokes a tool, the team should have a way to understand what was requested, which database identity executed it, and what happened as a result. Write operations and other higher impact actions deserve stronger controls than ordinary inspection or read queries.

Agent Memory introduces another boundary. Memory can help an application retain useful context, but it should not become a second source of truth for information that already belongs in an authoritative system. As the amount of memory grows, retrieval quality also becomes part of the application contract. Returning confident but irrelevant context is still a failure, even if the underlying retrieval system technically worked.

The goal is simple: let developers choose the client experience that works for them while keeping the database access model understandable, controlled, and consistent underneath.

A developer friendly rollout path

The easiest way to make an agent workflow hard to debug is to enable too much at once. A better path is to grow capability only after the previous layer has a clear acceptance check.

1. Prove the runtime. Start SQLcl in MCP mode locally and confirm the expected client can discover the SQLcl tools.
2. Prove the connection. Use one approved saved SQLcl connection and run a bounded read query against the expected schema.
3. Prove the permission boundary. Confirm that allowed queries succeed and that disallowed operations fail because of the database role or configured restrictions.
4. Prove observability. Check the available database side activity trail and document what the team will inspect when a workflow fails.
5. Only then add durable memory. Store a small set of useful records, retrieve them in a second session, and verify identity and scope.
6. Add semantic retrieval when exact filters are no longer enough. Evaluate retrieval quality with representative developer questions, not only a single happy path.
7. Expand tool or write access last. Treat each new capability as a change to the operational surface, not as a prompt tweak.

Which workflow should you start with?

There is no universal “best” client in this series. The better question is which developer loop you already use and what you need to validate next. Use the table below as the quick route into the detailed articles and notebooks.

The recommended path through the series

If you are discovering the series from this article, do not try to reproduce every client. Pick the environment that matches your work, prove the shared SQLcl MCP path once, and then add complexity only when the use case demands it.

The order matters because each step narrows the next problem. If SQLcl MCP cannot start, memory is irrelevant. If the connection is wrong, retrieval is irrelevant. If the database identity is too broad, a better prompt will not fix the permission model. When the lower layers are proven first, the remaining failures become easier to diagnose.

What I would carry from demo to production

The articles in this series are developer examples, but the architectural lessons are broader. Production readiness comes from making the workflow explainable when everything is not going well. A team should be able to answer simple questions quickly: what did the client ask for, which tool ran, which database identity executed it, what result came back, what memory was retrieved, and why a sensitive action was allowed or blocked.

That means keeping environment boundaries clear, reviewing tool surfaces, and treating write access as a deliberate capability. Failure paths matter too. A denied query can show that least privilege is working. An empty retrieval result can be safer than inventing context. A client that cannot see an unapproved tool may be behaving exactly as intended.

Memory needs its own lifecycle decisions. What should be remembered, for how long, and under which identity? Can stale memory be corrected or removed? What should remain in an authoritative system instead of becoming memory?

Keep the architecture boring where possible. A controlled connection, a small tool surface, clear logs, scoped memory, and strong validation checks are usually more useful than adding frameworks before the team understands the failure modes.

The big idea: Give AI the Right Path to the Database

AI coding assistants are good at reasoning over the context they receive. The quality of a database workflow therefore depends heavily on how that context is obtained. Copying schema fragments into a prompt is useful for small examples, but it does not scale as a substitute for current, permission aware, inspectable access to the environment.

The Oracle SQLcl MCP server gives developers a practical way to make database access explicit. Oracle AI Database keeps execution, security, durable records, and retrieval close to the data layer. Oracle AI Agent Memory adds continuity when the application needs it. Codex, Cursor, Antigravity, and Claude can then sit on top as different developer experiences without forcing a different database architecture every time.

That is the real theme of this series. Do not ask the assistant to memorize the database in a prompt. Give it the right tool boundary, let Oracle Database enforce the rules, verify what happened, and add durable memory only when the workflow has something worth carrying forward.

Frequently asked questions

Does SQLcl MCP tool discovery mean every operation is allowed?
No. Tool discovery and operation control are different concerns. A client may discover available MCP tools, but SQLcl MCP configuration, SQLcl command restrictions, and Oracle Database privileges still determine what can actually run.

Is MCP the same thing as durable agent memory?
No. MCP gives an AI client a way to call tools. Oracle AI Agent Memory is a separate application layer for durable context, scoped retrieval, threads, and memory records backed by Oracle AI Database.

Do the notebooks show live client telemetry?
No. The notebooks are validation harnesses. They help prove prerequisites, storage, retrieval, memory setup, and database patterns. Live client behavior still needs to be validated in Codex, Cursor, Antigravity, or Claude with the configured SQLcl MCP workflow.

Which versions should I use?
Use the versions and prerequisites listed in each hands-on article and companion notebook. Before running the workflow, confirm the current supported versions for SQLcl, Java, the chosen client, and the Oracle AI Agent Memory package.

Can I use the same Oracle SQLcl MCP server pattern across different AI clients?
Yes. That is the point of the shared foundation in this series. Codex, Cursor, Antigravity, and Claude can provide different developer experiences, while the database boundary stays consistent through approved SQLcl connections, configured tools, database privileges, and validation checks. The client can change without requiring a completely new database access model.

When should I add Oracle AI Agent Memory to an MCP workflow?
Add Oracle AI Agent Memory when the workflow needs continuity beyond the current interaction. Good examples include remembering user preferences, carrying useful project context into a later session, retrieving prior tool outcomes, or assembling scoped context for a recurring workflow. Do not add memory only because MCP is present. First prove the Oracle SQLcl MCP server path, then add memory when there is something worth carrying forward.

References

• How to Build a Controlled MCP Workflow for Codex and Oracle AI Database
• End-to-end Companion Codex notebook
• Build a Controlled Cursor MCP Workflow with Oracle AI Database
• End-to-end Companion Cursor notebook
• How to Build an Antigravity Workflow with the Oracle SQLcl MCP Server and Oracle AI Database
• End-to-end Companion Antigravity notebook
• When to use Claude memory, Oracle AI Agent Memory, and LangChain together
• End-to-end Companion Claude notebook
• Oracle AI Agent Memory documentation
• Oracle Database 26ai documentation
• oracleagentmemory on PyPI
• SQLcl MCP Server

Stop Making AI Guess Your Database: Oracle SQLcl MCP Server, Agent Memory, and Oracle AI Database was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Key Takeaways

• ODP.NET 26ai (23.26.3) and the Oracle AI Database Vector Store Connector are now available for .NET developers.
• The connector lets applications store, search, update, and delete vectors in Oracle AI Database and supports Microsoft Agent Framework.
• Expanded Oracle Deep Data Security features allow applications to manage access for human users and AI agents through end-user names and lookup keys.
• Oracle .NET AI skills provide reusable guidance that helps coding agents create ODP.NET applications using Oracle best practices.

I’m excited to announce ODP.NET 26ai (23.26.3) and the production release of the Oracle AI Database Vector Store Connector, expanding support for AI agent development.

These releases add new AI agent, skills, and security capabilities for Oracle .NET developers. With the Oracle AI Database Vector Store Connector, .NET applications can search, create, read, update, and delete vectors directly in Oracle AI Database.

The connector also integrates with Microsoft AI technologies, including Microsoft Agent Framework, to support AI agents, large language model (LLM) applications, Model Context Protocol (MCP) solutions, and workflows.

ODP.NET 23.26.3 extends Oracle Deep Data Security support for agentic AI by adding end-user names and lookup keys to security contexts. These additions give developers more flexible ways to create and manage security contexts for both users and AI agents.

Finally, we’ve released Oracle .NET AI developer skills to help AI coding agents build robust ODP.NET applications faster while following Oracle best practices.

Oracle AI Database and ODP.NET offer a broad set of capabilities for C# data applications, including performance, high availability, security, data management, scalability, and AI. The challenge is that developers need time to learn these capabilities and apply the right practices.

Oracle AI skills help agents apply that guidance directly when generating application code, improving developer productivity while helping teams avoid common implementation pitfalls.

Let’s take a closer look at these new capabilities and the benefits they provide.

Oracle AI Database Vector Store Connector

The Oracle AI Database Vector Store Connector for .NET is now generally available in the Oracle.VectorData NuGet package. It provides out-of-the-box support for standard .NET vector operations, so existing C# AI applications can use Oracle AI Database to store and retrieve vectors with minimal code changes.

The connector also exposes Oracle AI Database 26ai capabilities beyond basic vector storage, including multiple vector distance functions and vector index types that most databases don’t offer. This gives .NET developers access to a broader set of vector search options directly from their applications.

The new Oracle connector also supports Microsoft Agent Framework, an SDK and runtime for building, orchestrating, and deploying multi-agent AI systems. It combines simple agent abstractions with enterprise-ready capabilities.

Microsoft Agent Framework applications can use Oracle AI Database as a vector store for retrieval-augmented generation. Because the framework uses the same vector store libraries as .NET, existing connectors such as Oracle’s can integrate with little or no additional customization.

To learn more about the Oracle AI Database Vector Store Connector, explore the documentation or try the sample applications.

• Documentation: Using the Oracle AI Database Vector Store Connector
• Documentation: Oracle AI Database Vector Store Connector Classes
• Samples: AI Agent Framework Chat App and AI Vector Hotel Search App

The connector supports both .NET and .NET Framework and uses ODP.NET for database access.

While the connector supports Oracle Database 19c and later, I recommend Oracle AI Database 26ai for its broader vector search and AI agent capabilities, with additional features delivered in quarterly releases. One of its most important capabilities is Deep Data Security, designed to help secure applications for the agentic AI era.

That brings us to the second major Oracle .NET enhancement in this release: expanded Deep Data Security integration.

ODP.NET Deep Data Security End-user Names and Lookup Keys

Agentic AI can perform complex tasks, but it often requires broad, dynamic access to data, increasing security and compliance risk. Enforcing authorization in the database reduces duplicated application logic and keeps access rules consistent across applications, agents, and tools.

Oracle Deep Data Security provides unified, database-native policy enforcement for both conventional and AI applications. ODP.NET 23.26.2 first added support for passing Deep Data Security end-user security contexts from .NET applications. I covered this integration in more detail in a previous blog post.

Deep Data Security plays a critical role in AI by helping prevent autonomous agents from exposing sensitive information or making unauthorized data changes. Enforcing security policies in the database provides a consistent control point instead of relying on every application to govern AI agents correctly and stay up to date on new practices.

In the vibe-coding era, AI can generate applications quickly, cheaply, and with extensive customization. That can produce many variations of the same application, some of which may be rarely updated. Centralized database security helps keep access policies consistent across all of them.

With so many applications, thorough code and security reviews may become less consistent. AI coding agents also may not understand an organization’s specific end-user authorization rules unless those rules are explicitly provided.

Oracle Deep Data Security Usage Scenario

Consider a human resources database. Employees should see only their own salary and, where appropriate, the salaries of their direct reports. Sensitive identifiers, such as Social Security numbers, should be restricted to the individual they belong to. Employees may be allowed to update their home address, but not their salary.

Building all of these business, security, and legal rules into every AI application is difficult to maintain. Centralizing authorization in the database helps prevent unintended data exposure and unauthorized changes across applications and agents.

That’s where Oracle Deep Data Security comes in. By enforcing security at the database level, organizations can simplify policy setup, maintenance, and auditing. Developers can also place less trust in individual applications, which actually makes it easier to build and deploy securely.

ODP.NET Deep Data Security End-user Names and Lookup Keys

In the latest release, ODP.NET Deep Data Security adds support for end-user names and lookup keys in end-user security contexts. This gives applications more flexible ways to manage user and AI agent access. An external application identity can now be mapped to a database session without requiring a corresponding account in an external identity and access management system, such as Microsoft Entra ID.

A Deep Data Security end-user name can represent a human or AI agent authenticated by the application, rather than a traditional database user account. The end-user name can be used in a security context instead of a user access token.

A lookup key gives Oracle Deep Data Security a unique identifier for an application-managed end user. It maps the authenticated identity to the appropriate database policies, data roles, and data grants, enabling fine-grained access enforcement.

The lookup key is automatically generated when an end-user security context is created with an end-user name. The .NET security context then contains the lookup key, database access token, and end-user name.

Here’s a C# example that creates the security context using an end-user name. You can see the context created with the CreateWithTokenAndName method and end-user parameter.

/* Setup an end user for the security context in lieu of a user access token.
* Create the user:
  CREATE END USER deepsec_end_user;
* Create data grant to give limited HR schema access to the end user:
  CREATE DATA GRANT enduser_grant AS SELECT (ALL COLUMNS EXCEPT salary) ON hr.employees TO deepsec_end_user;
*/

// Read database token from file. 
string midtierToken = GetMidtierToken(); 
// Create security context with deepsec_end_user.
OracleEndUserSecurityContext securityCtx = OracleEndUserSecurityContext.CreateWithTokenAndName(midtierToken, "deepsec_end_user"); 

// Create and open connection. Deep Data Security requires a TCPS connection. 
// low_privilege_user only has connection privileges. 
OracleConnection conn = new OracleConnection("user id=low_privilege_user;password=<PASSWORD>;data source=oracle");
conn.Open(); 
// Set security context on connection 
conn.SetEndUserSecurityContext(securityCtx); 

// Execute SQL 
using (var command = new OracleCommand("SELECT FIRST_NAME, SALARY from hr.employees", conn)) 
{
  using (var reader = command.ExecuteReader()) 
  {
    while (reader.Read()) 
    {
      // Deepsec_end_user has access to employee first names but not salaries based on data grant.  
      // Salary data will return as null values.
      Console.WriteLine("Name: " + (reader.IsDBNull(0) ? "<NULL>" : reader.GetString(0)));
      Console.WriteLine("Salary: " + (reader.IsDBNull(1) ? "<NULL>" : reader.GetString(1)));
    }
  }
}

Managed ODP.NET and ODP.NET Core 23.26.3 both support Oracle Deep Data Security end-user names and lookup keys.

Learn more in the ODP.NET Deep Data Security documentation.

Oracle .NET AI Skills for More Productivity

Perhaps the biggest gain in developer efficiency comes from AI skills. These reusable Markdown files contain instructions, knowledge, and workflows that extend AI agents for specific tasks or domains. Our focus here is ODP.NET and Oracle AI Database development and optimization.

My team has published an Oracle .NET AI skills file for use with AI tools and coding assistants. You can use it as is or adapt it to your project. It works with new or existing managed ODP.NET, ODP.NET Core, and Oracle EF Core applications.

We plan to continue expanding the Oracle .NET AI skills over time.

These Oracle .NET AI skills are part of the Oracle AI Skills repository, which covers a broader range of Oracle products and services, including Oracle AI Database. The repository also includes an Oracle AI Database skills file for improving database code and design, including PL/SQL, SQL, and security.

You can use individual skill files to focus on specific areas, such as performance or high availability, or combine them to improve your application more holistically.

Get Started with New AI Features

You can start building AI applications with ODP.NET 23.26.3 and the Oracle AI Database Vector Store Connector today. Download them from NuGet Gallery:

• Oracle AI Database Vector Store Connector
• ODP.NET Core
• Managed ODP.NET

You can also download the Oracle .NET AI skills from GitHub:

• Oracle .NET AI skills file

Give them a try to accelerate Oracle .NET AI development and build better applications with less effort.

#OracleDotNet

FAQs

• What is the Oracle AI Database Vector Store Connector?
  It is a .NET connector that lets applications use Oracle AI Database to store and retrieve vector data with minimal code changes. It’s available in production.
• Which AI technologies does the connector support?
  It works with .NET vector libraries and Microsoft Agent Framework for AI agents, LLM applications, MCP solutions, and related workflows.
• How does Deep Data Security help AI applications?
  It enforces authorization in the database, helping prevent agents and applications from accessing sensitive data or making unauthorized changes.
• What are Oracle .NET AI skills?
  They are reusable instruction files that help AI coding assistants build and optimize ODP.NET, ODP.NET Core, and Oracle EF Core applications.

New ODP.NET Releases for AI Agent Development and AI Skills was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.