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 through the 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);

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.

Table ODP.NET OpenTelemetry activity tags {#GUID-38BA1E2C-3904-40B3-A6A9-57AB73DF2159}

Oracle Data Type	JsonValueKind Name
VARCHAR2	String
NVARCHAR2	String
CHAR	String
NCHAR	String
NUMBER	Number
FLOAT	Number
LONG	String
DATE	String
BINARY_FLOAT	Number/String
BINARY_DOUBLE	Number/String
TIMESTAMP	String
TIMESTAMP WITH TIME ZONE	String
TIMESTAMP WITH LOCAL TIME ZONE	String
INTERVAL YEAR TO MONTH	String
INTERVAL DAY TO SECOND	String
RAW	String
LONG RAW	String
ROWID	String
UROWID	String
CLOB	String
NCLOB	String
BLOB	String
BFILE	String
JSON	Object/Array/others
BOOLEAN	True/False
VECTOR	Array