Oracle.ManagedDataAccess.SQLScriptExecution 23.26.400

Oracle.ManagedDataAccess.SQLScriptExecution 23.26.400

Release Notes for Oracle Data Provider for .NET Extension for SQL Script Execution NuGet Package

October 2026

This NuGet package supports SQL script execution with ODP.NET Core.

This document provides information that supplements the Oracle Data Provider for .NET (ODP.NET) documentation.

Oracle .NET Links

• Oracle .NET Home Page
• GitHub - Sample Code
• ODP.NET Discussion Forum
• YouTube
• X (Twitter)
• Email Newsletter Sign Up

New Features

• Introduces SQL script execution support via OracleCommand.ExecuteStatements
• Execute multiple SQL and PL/SQL statements from a single stream
• Returns execution results (result sets, output parameters, errors) in JSON format
• Supports bind parameters via JSON
• Configurable execution behavior (AbortOnError / ContinueOnError)
• Configurable LOB and LONG data fetch limits for JSON results
• Provides detailed error information with statement-level metadata

Usage

Use the ExecuteStatements extension method on OracleCommand to execute SQL or PL/SQL statements from a stream. The API parses the input, executes each statement, and returns the results as a JSON string.

You can optionally specify

• how execution behaves when errors occur - whether to abort or continue the execution of the remaining statements using the OracleExecutionOption enum
• supply bind parameters information through a JSON stream via the SetParametersForExecuteStatements extension method on OracleCommand OR via OracleParameterCollection
• the maximum LOB and LONG data returned in the JSON result using the MaxLOBFetchSize and MaxLONGFetchSize properties

ODP.NET SQL Script Execution Documentation

Summary

• ODP.NET Core - Execute SQL and PL/SQL scripts

ODP.NET can now programmatically execute SQL and PL/SQL commands from a stream, providing a new scripting option. Developers can bind any needed script parameters in a JSON format with results output to a JSON string.

This new SQL script execution capability makes it easy to apply and automate database change tasks using .NET runtime without requiring another tool, such as SQL Plus.

SQL Script Execution Feature

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 occur 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.

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.

SQL script execution requires the Oracle.ManagedDataAccess.SQLScriptExecution NuGet package.

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.

Usage and Results

Here is a 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.

SQL Script Execution code sample

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,
    completionOption: HttpCompletionOption.ResponseHeadersRead);
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.

ODP.NET SQL Script sample

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.

ODP.NET JSON parameter sample

[
  {
    "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.

SQL Script Execution Application Programming Interfaces (APIs)

OracleCommand.ExecuteStatements Method

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

Declaration

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

Parameters

• 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

// C#     
public static void SetParametersForExecuteStatements(this OracleCommand cmd, StreamReader jsonParameters);

Parameters

• 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.

Requirements

Execution Property

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

Declaration

// C#     
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.

MaxLOBFetchSize Property

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

Declaration

// C#     
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 chars for CLOB/NCLOB or bytes for BLOB/BFILE are included. The default is 65536.

MaxLONGFetchSize Property

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

Declaration

// C#     
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 chars 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.

Table OracleExecutionOption Enumeration Values

Requirements

Copyright (c) 2026, Oracle and/or its affiliates.