如何使用C＃在Oracle中插入CLOB字段 [英] How to insert CLOB field in Oracle using C#

问题描述

亲爱的，

我有一个无法解决的问题。我想将数据（XML）插入到clob字段中，当我尝试将数据写入clob时，它返回错误。

这是我的代码：

Dear all,
I've a problem that i can't solved. I wanna insert data (XML) into a clob field and when i tried to write data into clob, its return an error.
This is my code :

class clsOracle
  {
    private System.Data.OracleClient.OracleConnection connOracle;
    private System.Data.OracleClient.OracleDataReader rstOracle;
    private System.Data.OracleClient.OracleCommand sqlCommandOracle;
    private System.Data.OracleClient.OracleTransaction txn;
    private System.Data.OracleClient.OracleLob clob;

    public clsOracle()
    {
      string OracleServerAkses = "Data Source=(DESCRIPTION="
           + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.111.3.54)(PORT=1521)))"
           + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=kseistpd)));"
           + "User Id=AKSES;Password=password;";
      connOracle = new System.Data.OracleClient.OracleConnection(OracleServerAkses);
      connOracle.Open();
    }

    public void InsertRecord(string SQLStatement)
    {
      if (SQLStatement.Length > 0)
      {
        if (connOracle.State.ToString().Equals("Open"))
        {
          sqlCommandOracle =
            new System.Data.OracleClient.OracleCommand(SQLStatement, connOracle);
          sqlCommandOracle.ExecuteScalar();
        }
      }
    }

    public void InsertCLOB(string SQLStatement, string str)
    {
      if (SQLStatement.Length > 0)
      {
        if (connOracle.State.ToString().Equals("Open"))
        {
          byte[] newvalue = System.Text.Encoding.Unicode.GetBytes(str);
          sqlCommandOracle =
            new System.Data.OracleClient.OracleCommand(SQLStatement, connOracle);
          rstOracle = sqlCommandOracle.ExecuteReader();
          rstOracle.Read();
          txn = connOracle.BeginTransaction();
          clob = rstOracle.GetOracleLob(0);
          clob.Write(newvalue, 0, newvalue.Length);
          txn.Commit();
        }
      }
    }
    public void CloseDatabase()
    {
      connOracle.Close();
      connOracle.Dispose();
    }
  }

这是主要类别：

And this is the main class :

static void Main(string[] args)
    {
      clsOracle db = new clsOracle();
      string guid = "guid12";
      string sql = "INSERT INTO T_CLOB(GUID, MEMBERID, INPUT_DATE) VALUES('" +
        guid + "' , 'BCA02', sysdate)";
      db.InsertRecord(sql);
      sql = "SELECT MESSAGES FROM T_CLOB WHERE GUID='" + guid + "' FOR UPDATE";
      db.InsertCLOB(sql, data);
      db.CloseDatabase();}

Fields of table in my database (Oracle) :
1. GUID (varchar2)
2. MESSAGES (clob)
3. INPUT_DATE (varhcar2)

请帮帮我.. ：〜：〜

Please help me.. :~:~

推荐答案

修改Main中的Sql语句（）

Modify Sql Statement in Main()

Sql= "INSERT INTO T_CLOB(GUID,MESSAGES,INPUT_DATE) VALUES('" + guid + "' , :message, sysdate)";

更改InsertClob方法

Change InsertClob Method

OracleCommand command = new OracleCommand(SQLStatement, connOracle)
 command.Parameters.Add(":message", OracleDbType.Clob, messageClobValue, ParameterDirection.Input);

command.ExecuteNonQuery();

尝试一次。并且看看这个

http://dotnetchris.wordpress.com/2008/04/10/writting-lobs-clobs-to-oracle-using-c-aspnet-20 [ ^ ]



http：/ /msdn.microsoft.com/en-us/library/cydxhzhz(VS.80).asp [ ^ ]

大家好，



我试图通过C＃，SQL / Oracle将xml插入到Clob中，我尝试了这个代码和许多其他版本，是否有任何人已经测试过它并且可以正常发布在这里的示例？



我非常感谢你的帮助，如果有人可以帮我这个。



提前致谢

Hi All,

I am trying to insert xml to a Clob via C#,SQL/Oracle , I tried this code and many other version , is there any example that someone has tested it itself and works properly to post here?

I really appreciate your help if someone could help me regarding this.

Thanks in advance

将数据插入clob字段有2个步骤，



1.插入记录

2用FOR UPDATE子句打开同一行并执行上面的代码行



问题是插入记录时不要忽略CLOB字段，而是使用EMPTY_CLOB（）在Insert语句中作为CLOB字段的值。

There are 2 steps for inserting data to clob field,

1. Insert the record
2. Open the same row with FOR UPDATE clause and execute the above line of code

The problem is while inserting record don't ignore CLOB field, instead use EMPTY_CLOB() in Insert statement as value for CLOB field.

这篇关于如何使用C＃在Oracle中插入CLOB字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！