输入大量内容到oracle数据库 [英] Enter large content to oracle database

问题描述

我有一个连接Oracle数据库的.Net应用程序，想要在DB的列中输入4000多个字符.

I have a .Net application connecting oracle database and want to enter more than 4000 character to a column in DB.

我尝试过CLOB仍然得到

I tried with CLOB still getting

输入字符串太长.

input string too long.

我正在使用SQL查询输入数据的任何帮助.

I am using SQL query to enter data any help.

推荐答案

最简单的方法是使用绑定变量.这是基本示例:

Simplest way is using bind variable. Here is basic example:

CREATE TABLE clob_table (val CLOB);

void Main()
{
    using (var connection = new OracleConnection("DATA SOURCE=hq_pdb_tcp;PASSWORD=oracle;USER ID=HUSQVIK"))
    {
        using (var transaction = new TransactionScope())
        {
            connection.Open();

            using (var command = connection.CreateCommand())
            {
                command.CommandText = "INSERT INTO clob_table (val) VALUES (EMPTY_CLOB()) RETURNING val INTO :clob";
                var parameter = command.Parameters.Add("clob", OracleDbType.Clob, ParameterDirection.Output);

                command.ExecuteNonQuery();

                var clob = (OracleClob)parameter.Value;

                ChunkFile(@"LargeTextFile.txt", 8060, (b, l) => clob.Append(b, 0, l));
            }

            transaction.Complete();
        }
    }
}

private void ChunkFile(string fileName, int chunkSize, Action<char[], int> chunkAction)
{
    using (var stream = File.OpenText(fileName))
    {
        do
        {
            var buffer = new char[chunkSize];
            var length = stream.Read(buffer, 0, chunkSize);
            if (length == 0)
                return;

            chunkAction(buffer, length);
        }
        while (true);
    }
}

这篇关于输入大量内容到oracle数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！