How to insert a clob field of over 4000 bytes in Oracle

Source: Internet
Author: User
Tags oracleconnection

In an application that inserts data using SQL statements in a group, we may encounter the need to insert large data, for example, when we need to insert fields with more than 4000 bytes in Oracle, if we use a simple grouping SQL statement to insert the fields, we will obviously encounter problems, this restriction is not imposed on SQL Server. I tried to execute a-character SQL statement in SQL Server and still can insert data, however, if you insert more than 4000 characters in Oracle, an exception is reported.

The following is a summary of the solution to this problem:
you can create a separate oraclecommand to insert the specified data, this section only describes how to insert data of the clob type. Blob is similar to this. We will not introduce it here. The following two methods have been verified:
Method 1: Use the component system. data. the implementation of oracleclient is simpler: copy Code the code is as follows: string conn = "Data Source = connection string specified by the client; user id = user; Password = Mima";
oracleconnection con = new system. data. oracleclient. oracleconnection (conn);
con. open ();
string plain text = "insert into gwexpointlist (ID, name, content) values (1, 'name',: clob )";
oraclecommand cmd = new oraclecommand (plain text, con);
oracleparameter op = new oracleparameter ("clob", oracletype. clob);
op. value = "super-normal string with more than 4000 characters";
cmd. parameters. add (OP);
cmd. executenonquery ();
con. close ();

Method 2: Use the Oracle. dataaccess component. The usage may be a little older, but it is still valid:Copy codeThe Code is as follows: idbcommand m_objcmd = new oraclecommand ();
M_obj316.commandtext = "insert into gwexpointlist (ID, name, content) values (1, 'name',: clob )";
Idataparametercollection m_arrparamter = m_objcmd.parameters;
Oracleclob clob = new oracleclob (oracleconnection) m_objconn );
Oracleparameter objparam = new oracleparameter ('clob', oracledbtype. clob, clob, parameterdirection. Input );

Objparam. value = "super-normal string with more than 4000 characters ";
M_arrparamter.insert (0, objparam );

Int nret = m_obj).executenonquery ();

Of course, SQL Server can also use this method to add strings. However, if you add a binary file, you can only use this method to add it because you need to read the binary stream content of the file.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.