ExecuteStatements
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
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",]
}
}