Oracle Lob Types provide BFile, Blob, Clob, NClob to allow us to store largest sizes of 4G unable structureless data blocks (for example, text, images, sounds, and videos, etc.). And their right to access data in an efficient, arbitrary, and segmentation operation.
The LOB type is different in many ways and long and long Raw, for example, LOB other than NCLOB can be considered as an object type, but Long is not. The maximum size of LOB is 4G, but the maximum size of Long is only 2GB. At the same time, LOB supports random access data, but supports sequential access. The Lob type stores the LOB locator, which points to a large object stored in an external file, and the PL / SQL is operated by the locator. From Oracle9i, we can convert Clobs to CHAR and VARCHAR2, and vice versa. You can use the package dbms_lob to perform segmentation operations on the LOB type. LONG, Long Raw, RAW, Clob, Blob, BLOB in Oracle: Clob and BLOB are better than CLOB, BLOB This data type is used to store unstructured binary data. RAW This data type is used to store some of the contents of the binary data LONG type stored by Oracle cannot be modified in the use of the long data type.
Instructions for use:
1, the LONG data type is stored in a variable long string, and the maximum length limit is 2GB.
2. For texts that exceed a certain length, it is basically possible to store, and the definition of many objects in the data dictionary is stored in long.
3. The long type is mainly used for long string data that does not require a string search. If you want to perform character search, you need to use VARCHAR2 type.
4, many tools, including SQL * Plus, is very difficult to handle long data types.
5. In the use of long data types, it is limited to the size of the disk.
Ability to operate the SQL statement:
1, SELECT statement
2, set statement in the UPDATE statement
3, the VALUES statement in the INSERT statement
limit:
1. You can only include a list of a long type in a table.
2, can not index the long type column.
3. You cannot cluster the form containing the LONG type column.
4. You cannot insert the value of the long type column in the SQL * PLUS into another table, such as Insert Into ... Select.
5. You cannot create a long-type column in SQL * Plus, such as CREATE TABLE As SELECT.
6. Do not constrain the LONG type column constraint (except NULL, NOT NULL, DEFAULT), such as the primary key, cannot be a long data type.
7, the long type column cannot be used in the following subseese: where, group by, order by, and SELECT statement with DISTINCT.
8, the long type column cannot be used to distribute queries.
9, the variable of the PL / SQL process block cannot be defined as a long type.
10, the long type column cannot be changed by the SQL function, such as: Substr, Instr.
SQL * PLUS Action LONG Type Column:
1, set long n
2, COL column name Format AN
n represents N-bit (N is an integer greater than zero), and the AN indicates that the data display width of this column does not exceed N bits.
Use the Oracle Long type field to insert a big text
When writing applications, you need a lot of insertion of big text, but Oracle's Clob is more cumbersome, so I didn't choose to use Clob, but used the long type in Oracle in previous versions [but the long type has some restrictions in one There can only be a long field in the table].
Inserting the database directly into the database directly, but Oracle has some statements that cannot exceed 4,000 characters and report the errors of ORA-01704. Finding the Oracle's document finds a solution, that is to insert String into the database using the setcharacterStream () method for the LONG field.
Code:
SQL = "INSERT INTO MSG_INFO VALUES (?,?,?,? [long type field], 'c', sysdate, sysdate " msgterm ",?)"
Pstat1 = conn.preparestatement (SQL);
Pstat1.Setlong (1, MSG_ID);
Pstat1.setint (2, MSG_GP_ID);
Pstat1.setstring (3, msg_title);
PSTAT1.SETCHARACTERSTREAM (4, New StringReader (MSG_INFO.TOSTRING ()), MSG_INFO.LENGTH ());
CONN.COMMIT ();
Pstat1.Setlong (5, this.upid);
Oralce recommends that the system will use the LOB type as much as possible.
