Oracle inserts a 4000-byte clob field processing method _oracle

Source: Internet
Author: User
Tags oracleconnection
In the application of data insertion through a group of SQL statements, we are likely to encounter situations where we need to insert large data, for example, when we need to insert a field with more than 4000 bytes in Oracle, if we implement the insert through a simple set of SQL statements, there is obviously a problem, and in SQL The server does not have this restriction, the individual tried the 26w-character SQL statement in the SQL server2005 execution, can still insert data, but the insertion of more than 4,000 characters in Oracle will report an exception.

The following solution to this problem, do a summary:
We can be successful by creating a separate OracleCommand for the specified inserts, where only the data inserted into the CLOB type is introduced, and a blob similar to this, which is not covered here, is described in the following two ways, both of which have been validated:
The first method: Using the method of component System.Data.OracleClient, simpler:
Copy Code code as follows:

String conn = "Data source= client Specifies the connection string; User Id=user; Password=mima ";
OracleConnection Con = new System.Data.OracleClient.OracleConnection (conn);
Con.open ();
String cmdtext = "INSERT into gwexpointlist (ID, name, content) VALUES (1, ' name ',: CLOB)";
OracleCommand cmd = new OracleCommand (Cmdtext, Con);
OracleParameter op = new OracleParameter ("Clob", Oracletype.clob);
Op. Value = "extraordinary string of more than 4000 characters";
Cmd. Parameters.Add (OP);
Cmd. ExecuteNonQuery ();
Con.close ();

The second method, implemented using the component Oracle.dataaccess method, may be slightly older, but still valid:
Copy Code code as follows:

IDbCommand M_objcmd = new OracleCommand ();
M_objcmd.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 = "extraordinary string exceeding 4000 characters";
M_arrparamter.insert (0, Objparam);

int nret = M_objcmd.executenonquery ();

Of course, SQL Server can also add strings in such a way, but adding binary files can only be added in this way 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.