UDataset.Oracle 0.14.2

UDataset.Oracle

UDataset.Oracle is an Oracle implementation for UDataset cross-database data access framework. It provides Oracle-specific implementations of core UDataset interfaces, enabling seamless data operations with Oracle databases.

Features

• Enterprise-Grade Database: Full support for Oracle's advanced features and scalability
• Rich Data Types: Comprehensive support for Oracle data types including JSON, XML, CLOB, BLOB, and RAW
• Connection Pooling: Efficient connection pooling for high-performance applications
• SQL Transformation: Automatic transformation for Oracle-specific SQL syntax (e.g., SYSDATE, NVL, ROWNUM)
• Full UDataset Features: Complete implementation of all UDataset features including CRUD, transactions, and schema management
• User Management: Oracle-specific user and permission management
• Schema Management: Complete DDL operations for tables, indexes, constraints, and sequences
• Advanced Features: Support for materialized views, partitioning, and PL/SQL integration

Installation

dotnet add package UDataset.Core
dotnet add package UDataset.Oracle

Quick Start

1. Register Provider

using UDataset.Core;
using UDataset.Oracle;

// Register Oracle provider at application startup
OracleBootstrapper.Register();

2. Create Connection

// Basic connection string
string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=myuser;Password=mypass;";

IConnection connection = ProviderFactory.CreateConnection("Oracle", connectionString);

3. Basic Operations

// Create table with JSON and XML support
var schemaManager = ProviderFactory.CreateSchemaManager("Oracle", connectionString);
var documentsTable = new Table("Documents")
    .WithAutoIncrementPK("DocId")
    .WithoutVersionControl();
documentsTable.Attributes.Add(new Column("Title", DataType.String) { Properties = { [ColumnProperty.Length] = 500 } });
documentsTable.Attributes.Add(new Column("Metadata", DataType.Json)); // JSON type
documentsTable.Attributes.Add(new Column("Content", DataType.Xml));    // XML type
documentsTable.Attributes.Add(new Column("CreatedDate", DataType.DateTime));
documentsTable.Attributes.Add(new Column("IsActive", DataType.Boolean));
schemaManager.Create(documentsTable);

// Insert data
var document = new Row("Documents")
{
    ["Title"] = "Document Title",
    ["Metadata"] = "{ \"author\": \"John\", \"tags\": [\"tag1\", \"tag2\"] }",
    ["Content"] = "<root><content>Document content</content></root>",
    ["CreatedDate"] = DateTime.UtcNow,
    ["IsActive"] = true
};
await connection.Create(document);

// Query data
var query = new QueryExpression("Documents");
query.Filter = "IsActive = @isActive AND CreatedDate > @minDate";
query.Parameters["isActive"] = true;
query.Parameters["minDate"] = DateTime.Now.AddDays(-30);
var results = await connection.Query(query);

Oracle-Specific Features

Boolean Type Handling

Oracle does not have a native BOOLEAN data type. UDataset automatically handles this conversion:

// Boolean values are stored as NUMBER(1) internally (0 or 1)
// UDataset automatically converts between C# bool and Oracle NUMBER(1)
var row = new Row("Users")
{
    ["IsActive"] = true  // Automatically converted to 1 in Oracle
};

JSON Data Type

Oracle 12c+ supports JSON data types with efficient querying:

// Create table with JSON column
var table = new Table("Configurations");
table.Attributes.Add(new Column("Config", DataType.Json));
schemaManager.Create(table);

// Query JSON data
var query = new QueryExpression("Configurations");
// Oracle supports JSON_VALUE, JSON_QUERY functions

XML Data Type

Full XML support with XMLTYPE:

// Create table with XML column
var table = new Table("Documents");
table.Attributes.Add(new Column("Content", DataType.Xml));
schemaManager.Create(table);

Auto-Increment with Sequences

Oracle uses SEQUENCE objects for auto-increment primary keys:

// UDataset automatically creates and manages sequences for auto-increment PKs
var table = new Table("Users")
    .WithAutoIncrementPK("UserId"); // Creates a sequence automatically
schemaManager.Create(table);

Use Cases

Oracle is ideal for:

• Enterprise Applications: Large-scale enterprise applications requiring high reliability
• High-Performance OLTP: Transactional workloads with high concurrency
• Data Warehousing: Oracle's partitioning and materialized views for analytical processing
• Financial Systems: Applications requiring ACID compliance and advanced security
• Legacy Systems: Integration with existing Oracle-based infrastructure
• Cloud Applications: Oracle Cloud, Autonomous Database, and Exadata

Compatibility

• Oracle Database 12c and later
• Oracle Cloud
• Autonomous Transaction Processing
• Autonomous Data Warehouse
• .NET 8.0
• Entity Framework Core compatibility (when used alongside)

Performance Characteristics

• Connection Pooling: Efficient connection pool management for high-throughput scenarios
• Batch Operations: Optimized batch inserts with parameter limits (max 1,000 parameters)
• Materialized Views: Support for pre-computed query results
• Partitioning: Efficient data partitioning for large tables
• Index Types: Support for B-tree, Bitmap, Function-based indexes

Limitations

• Parameter limit of 1,000 per query (Oracle constraint)
• Boolean values stored as NUMBER(1) require type conversion
• Database rename operations have specific constraints
• Some Oracle features require additional licensing

Requirements

• .NET 8.0
• Oracle.ManagedDataAccess.Core 23.26.100

Dependencies

• UDataset.Core
• Oracle.ManagedDataAccess.Core 23.26.100
• Dapper 2.1.66
• System.Data.Common
• System.Text.Json

Oracle SQL Syntax Transformations

UDataset automatically transforms common SQL functions to Oracle equivalents:

For general usage examples and advanced features, please refer to UDataset.Core documentation.

Version History

0.14.0 (2026-08-06)

• UPDATE bind variable names with safe prefix (fixes ORA-01745)
• Boolean parameter values converted to 1/0 Int (fixes ORA-00932)
• Aggregate results cast with explicit precision
• WhenNotExists auto-increment PK bypass
• Full regression passed: 8 functional groups, 0 failures

0.11.0 (2026-04-16)

• Added DM (Dameng) and KingbaseES database support to UDataset framework
• Note: KingbaseES support is experimental and not recommended for production use