ORA-22922: nonexistent LOB value

[Thomasdc]

Package used: Devart.Data.Oracle (version 9.6.696, latest)

I get

Code: Select all

ORA-22922: nonexistent LOB value

when I try to read out a blob via a stored procedure. If I add a transaction scope around the read step however it does work.

Oracle.ManagedDataAccess.Core doesn't have this problem. It works without a transaction scope on the same code.

Is this a bug in Devart.Data.Oracle or a limitation compared to Oracle.ManagedDataAccess.Core?

Code: Select all

async Task Main()
{
	var connectionString = ComposeConnectionString("rebus_messaging");
	var tableName = "transports";
	await CreateSchema(tableName, connectionString);
	await InsertEntry(tableName, connectionString);
	await ReadData(tableName, connectionString);
}

async Task CreateSchema(string tableName, string connectionString) {
	var commands = new[] {
		$@"CREATE TABLE {tableName}
			(
				id NUMBER(20) GENERATED ALWAYS AS IDENTITY NOT NULL,
				recipient VARCHAR2(255) NOT NULL,
				headers blob NOT NULL
			)",
		$@"create or replace PROCEDURE rebus_dequeue_{tableName}(recipientQueue IN varchar, output OUT SYS_REFCURSOR ) AS
			  messageId number;
			  readCursor SYS_REFCURSOR; 
			begin
			    open readCursor for 
			    SELECT id
			    FROM {tableName}
			    WHERE recipient = recipientQueue        
			    ORDER BY id ASC
			    for update skip locked;
			    
			    fetch readCursor into messageId;
			    close readCursor;      
			  open output for select * from {tableName} where id = messageId;
			  delete from {tableName} where  id = messageId;
			END;"
	};

	using (var connection = new OracleConnection(connectionString)) {
		connection.Open();
		foreach (var commandText in commands)
		{
			using (var command = connection.CreateCommand())
			{
				command.CommandText = commandText;
				await command.ExecuteNonQueryAsync();
			}
		}
	}
}

async Task InsertEntry(string tableName, string connectionString)
{
	using (var myConnection = new OracleConnection(connectionString))
	{
		myConnection.Open();
		using (var writeCommand = myConnection.CreateCommand())
		{
			writeCommand.CommandText = $@"
			INSERT INTO {tableName}
                    (
                        recipient,
                        headers
                    )
                    VALUES
                    (
                        :recipient,
                        :headers
                    )";

			var headers = Encoding.UTF8.GetBytes("This is a test");
			writeCommand.Parameters.Add(new OracleParameter("recipient", OracleDbType.VarChar, "foobar", ParameterDirection.Input));
			writeCommand.Parameters.Add(new OracleParameter("headers", OracleDbType.Blob, headers, ParameterDirection.Input));
			await writeCommand.ExecuteNonQueryAsync();
		}
	}
}

async Task ReadData(string tableName, string connectionString)
{
//	using (var scope = new TransactionScope(
//			TransactionScopeOption.Required,
//			new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted },
//			TransactionScopeAsyncFlowOption.Enabled))
//	{
		using (var myConnection = new OracleConnection(connectionString))
		{
			myConnection.Open();
			using (var cmd = myConnection.CreateCommand($"rebus_dequeue_{tableName}"))
			{
				cmd.CommandType = CommandType.StoredProcedure;
				cmd.Parameters.Add(new OracleParameter("recipientQueue", OracleDbType.VarChar, "foobar", ParameterDirection.Input));
				cmd.Parameters.Add(new OracleParameter("output", OracleDbType.Cursor, ParameterDirection.Output));
				cmd.InitialLobFetchSize = -1;

				cmd.ExecuteNonQuery();
				using (var reader = (cmd.Parameters["output"].Value as OracleCursor).GetDataReader())
				{
					if (await reader.ReadAsync())
					{
						var headers = reader["headers"]; // ORA-22922: nonexistent LOB value
					}
				}
			}
		}
//		scope.Complete();
//	}
}

string ComposeConnectionString(string userId)
{
	var devartLicenseKey = @"...";
	
	var builder = new OracleConnectionStringBuilder
	{
		Server = "localhost",
		UserId = userId,
		Password = userId,
		ServiceName = "ORCLCDB.LOCALDOMAIN",
		Port = 32769,
		Direct = true,
		Pooling = true,
		LicenseKey = devartLicenseKey
	};

	return builder.ConnectionString;
}

[Shalex]

With Oracle.ManagedDataAccess.Core, we are getting exactly the same error:

Code: Select all

using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;
using System;
using System.Data;
using System.Text;

namespace ConsoleApp600
{
    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = ComposeConnectionString("alexsh");
            var tableName = "transports";
            CreateSchema(tableName, connectionString);
            InsertEntry(tableName, connectionString);
            ReadData(tableName, connectionString);
        }

        static void CreateSchema(string tableName, string connectionString)
        {
            var commands = new[] {
        $@"CREATE TABLE {tableName}
			(
				id NUMBER(20) GENERATED ALWAYS AS IDENTITY NOT NULL,
				recipient VARCHAR2(255) NOT NULL,
				headers blob NOT NULL
			)",
        $@"create or replace PROCEDURE rebus_dequeue_{tableName}(recipientQueue IN varchar, output OUT SYS_REFCURSOR ) AS
			  messageId number;
			  readCursor SYS_REFCURSOR; 
			begin
			    open readCursor for 
			    SELECT id
			    FROM {tableName}
			    WHERE recipient = recipientQueue        
			    ORDER BY id ASC
			    for update skip locked;
			    
			    fetch readCursor into messageId;
			    close readCursor;      
			  open output for select * from {tableName} where id = messageId;
			  delete from {tableName} where  id = messageId;
			END;"
    };

            using (var connection = new OracleConnection(connectionString))
            {
                connection.Open();
                foreach (var commandText in commands)
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = commandText;
                        command.ExecuteNonQuery();
                    }
                }
            }
        }

        static void InsertEntry(string tableName, string connectionString)
        {
            using (var myConnection = new OracleConnection(connectionString))
            {
                myConnection.Open();
                using (var writeCommand = myConnection.CreateCommand())
                {
                    writeCommand.CommandText = $@"
			INSERT INTO {tableName}
                    (
                        recipient,
                        headers
                    )
                    VALUES
                    (
                        :recipient,
                        :headers
                    )";

                    var headers = Encoding.UTF8.GetBytes("This is a test");
                    writeCommand.Parameters.Add(new OracleParameter("recipient", OracleDbType.Varchar2, "foobar", ParameterDirection.Input));
                    writeCommand.Parameters.Add(new OracleParameter("headers", OracleDbType.Blob, headers, ParameterDirection.Input));
                    writeCommand.ExecuteNonQuery();
                }
            }
        }

        static void ReadData(string tableName, string connectionString)
        {
            //	using (var scope = new TransactionScope(
            //			TransactionScopeOption.Required,
            //			new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted },
            //			TransactionScopeAsyncFlowOption.Enabled))
            //	{
            using (var myConnection = new OracleConnection(connectionString))
            {
                myConnection.Open();
                using (var cmd = myConnection.CreateCommand())
                {
                    cmd.CommandText = $"rebus_dequeue_{tableName}";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new OracleParameter("recipientQueue", OracleDbType.Varchar2, "foobar", ParameterDirection.Input));
                    cmd.Parameters.Add(new OracleParameter("output", OracleDbType.RefCursor, ParameterDirection.Output));
                    //cmd.InitialLobFetchSize = -1;

                    cmd.ExecuteNonQuery();
                    using (var reader = (cmd.Parameters["output"].Value as OracleRefCursor).GetDataReader())
                    {
                        if (reader.Read())
                        {
                            var headers = reader["headers"]; // ORA-22922: nonexistent LOB value
                        }
                    }
                }
            }
            //		scope.Complete();
            //	}
        }

        static string ComposeConnectionString(string userId)
        {
            var builder = new OracleConnectionStringBuilder
            {
                DataSource = "(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE NAME=ORCLCDB.LOCALDOMAIN)))",
                UserID = userId,
                Password = userId,
                Pooling = true,
            };

            return builder.ConnectionString;
        }
    }
}

Please check the code in your environment with Oracle.ManagedDataAccess.Core.