Description of Long RAW BLOB CLOB type in Oracle

Description

Raw: Unprocessed type, can store binary data or byte characters
Long: variable long string data, longest 2g,long has VARCHAR2 column attributes, can store long text in one table up to a long column "not recommended"
Long RAW: variable Long binary data, up to 2G "not recommended"
CLOB: Character large object CLOB used to store single-byte character data; Large text, such as XML data.
NCLOB: Used to store multibyte character data
BLOB: Used to store binary large object data, such as digital photos;
BFILE: Binary data stored in a file, the data in this file can only be read-only. However, the file is not included in the database.
bfile Field The actual file is stored in the file system, and the field stores the file locator pointer. bfile is read-only to Oracle and does not participate in transactional control and data recovery.
　　
Clob,nclob,blob are internal lob (Large Object) type, maximum 4G, no long can have only one column limit

Note: Long and long raw are deprecated in Oracle's new version (using BLOB substitution) and are reserved for backwards compatibility.

This article emphatically introduces: Raw/clob/blob

1. Raw type
1.1 Introduction
You use the RAW datatype to store binary data or byte strings. For example, a RAW
Variable might store a sequence of graphics characters or a digitized picture. Raw data
Is like VARCHAR2 data, except. PL/SQL does not interpret raw data. Likewise,
Oracle Net does no character set conversions when to transmit raw data from one
System to another.
The RAW datatype takes a required parameter that lets your specify a maximum size up
to 32767 bytes. The syntax follows:
RAW (Maximum_size)
You cannot use a symbolic constant or variable to specify the maximum size; You must
Use a integer literal in the range 1. 32767.
You cannot insert raw values longer than, bytes into a raw column. You can insert
Any raw value into a LONG RAW database column because the maximum width of a
LONG RAW column is 2147483648 bytes or both gigabytes. However, you cannot retrieve
A value longer than 32767 bytes from a LONG raw column to a raw variable. Note
That the LONG RAW datatype are supported only for backward compatibility; See "LONG
and LONG RAW Datatypes "on page 3-5 for more information.

Raw English means: raw; unprocessed;
You can use raw types to store binary data or byte characters. For example, a raw variable can store a series of graphic characters or a digital photo.
Raw data is like VARCHAR2 data, except for one thing: PL/SQL does not interpret it. Similarly, when you transfer raw data, Oracle NET does not convert it into character set.

The raw data type requires a parameter that specifies a maximum value of 32767;

The declaration format is as follows: RAW (maximum_size)
You cannot use a symbolic constant or variable in place of this parameter and must use 1. Any integer in the 32767.

You cannot insert more than 2000 bytes of characters into the raw column;
You can insert any raw data into the long raw column with a maximum support of 2G. However, in turn, it is not possible to remove more than 32767 bytes of raw data at once.

It is important to note that long raw is the type of early-riser version; It is now not recommended; see below for details:

1.2 Related tools
– Package
Utl_raw

– function
Utl_raw.cast_to_raw
Utl_raw.cast_to_number
Utl_raw.cast_to_varchar2
Hextoraw

Raw saves 16 binary numbers. When Hextoraw is used, the data in the string is treated as a 16 binary number.
Instead, use Utl_raw. Cast_to_raw, the ASCII code of each character in the string is placed directly into the field of the raw type.

1.3 Examples

drop table Test_raw;create table Test_raw (Msg Raw ( -)); [Email protected]> Insert intoTest_raw VALUES ('<xml><name>Dylan</name><score></ score></xml>');Insert intoTest_raw VALUES ('<xml><name>Dylan</name><score></ score></xml>')* Section1Line error: ora-01465: Invalid hexadecimal number--this place note is hex [email protected]> Insert intoTest_raw VALUES (Utl_raw.cast_to_raw ('<xml><name>Dylan</name><score></ score></xml>'));has been created1Yes. [Email protected]> commit;--ViewSelectMsg fromTest_raw; MSG------------------------------------------------------------------------------3C786d6c3e3c6e616d653e44796c616e3c2f6e616d653e3c73636f72653e3130303c2f73636f72653E3c2f786d6c3e0Abc[email protected]>SelectUTL_RAW.CAST_TO_VARCHAR2 (msg) fromTest_raw; Utl_raw. CAST_TO_VARCHAR2 (MSG)------------------------------------------------------------------------------<xml> <name>Dylan</name><score> -</score></xml>

2, long and long raw types

You can use a long type to store variable-length strings. The long type is like VARCHAR2, except that the maximum capacity of long is 32760;

Stores binary data or byte strings using a long raw type. Long raw data is like long data, except that long raw data is not interpreted by PL/SQL.
The maximum capacity of LONG raw is also 32760.

You can insert any long data into a long column with a maximum length of 2G. However, a long type variable in PL/SQL can only be supported to 32760.
This rule also applies to the long raw type.

A long column in a table can store text, character arrays, and even short files. Update, INSERT, and select operations can be made for this type of column.
But no more expressions, SQL function calls, or specific SQL conditional statements such as where, GROUP by, and connect by.

In statements, PL/SQL binds long values as VARCHAR2, not as LONG. However,
If the length of the bound VARCHAR2 exceeds the maximum width of a VARCHAR2
Column (4000 bytes), Oracle converts the bind type to LONG automatically, then issues
An error message because your cannot pass LONG values to a SQL function

In an SQL statement, PL # Binds a long type as a VARCHAR2 type. However, if the bound VARCHAR2 length exceeds the 4000,oracle, it is automatically converted to long,
Then throws an error because you cannot pass a long value to the SQL function.

-for example: [Email protected]> CREATE TABLE long_test (ID number, MSGLong); The table was created. [Email protected]> Insert intoLong_test VALUES (1,' Hello World ');has been created1Yes. [Email protected]> commit; commit completed. [Email protected]>Select* fromLong_testwheremsg=' 123 ';Select* fromLong_testwheremsg=' 123 '* Section1Line error: ora-00997: illegal useLONGdata type [email protected]>/ID MSG-------------------------------------------------------------------------------- ----------1Hello World[email protected]>SelectID, trim (msg) fromLong_testwhereID =1;SelectID, trim (msg) fromLong_testwhereID =1* Section1Line error: ora-00932: Data type is inconsistent: number should be, but getLONG

3, CLOB
You can use CLOB type large chunks of character data. Each of the CLOB variables stores a locator that points to a large chunk of character data.

CLOBs participate fully in transactions, is recoverable, and can be replicated. Changes
Made by package Dbms_lob can is committed or rolled back. CLOB Locators can span
transactions (for reads only), but they cannot span sessions.

CLOB participate in the overall transaction, can be restored, and can be duplicated.
Data that is changed by the Dbms_lob package can be submitted and rolled back. CLOB locators can span transactions, but not across sessions.

4. BLOB
The BLOB datatype to store large binary objects in the database, In-line or
Out-of-line. Every BLOB variable stores a locator, which points to a large binary object.
BLOBs participate fully in transactions, is recoverable, and can be replicated. Changes
Made by package Dbms_lob can is committed or rolled back. BLOB Locators can span
transactions (for reads only), but they cannot span sessions.

For storing a sophomore binary object, the BLOB participates in the overall transaction, is recoverable, and can be duplicated.
Data that is changed by the Dbms_lob package can be submitted and rolled back. Blob locators can span transactions, but not across sessions.

drop table blob_test; [Email protected]>CreateTable Blob_test (ID number primary key, content blob notNULL); The table was created. [Email protected]> Insert intoBlob_test VALUES (1,' 11111000011111 '); created1Yes. [Email protected]> commit; commit completed. [Email protected]>Select* fromBlob_test; [Email protected]>SetLinesize -[Email protected]>/ID CONTENT---------------------------------------------1 11111000011111[Email protected]> Insert intoBlob_test VALUES (1,' 11111000011111> '); insert intoBlob_test VALUES (1,' 11111000011111> ') * Section1Line error: ora-01465: invalid hexadecimal number [email protected]> update blob_testSetContent=to_blob (' 110010000110011 ')whereId=1; updated1Yes. [Email protected]> rollback2, fallback is complete. [Email protected]>Select* fromBlob_test; ID CONTENT-------------------------------------------------------------------------------1 11111000011111Delete fromBlob_testwhereId=1; Commit

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Description of Long RAW BLOB CLOB type in Oracle