Introduction to the long raw blob clob type in Oracle

Introduction to the long raw blob clob type in Oracle
RAW: unprocessed type, which can store binary data or LONG, a variable-length string data. The longest value is 2 GB. LONG has the feature of VARCHAR2 columns, you can store up to one LONG column in a LONG text table [not recommended] long raw: Variable Length binary data, up to 2 GB [not recommended] CLOB: the character large object Clob is used to store single-byte character data; large text, such as XML data. NCLOB: used to store multi-byte character data BLOB: used to store binary big object data, such as digital photos; BFILE: the binary data stored in the file, the data in this file can only be read-only access. However, this file is not included in the database. The bfile field actually stores the file in the file system, and the field stores the file positioning pointer. bfile is read-only for Oracle and does not participate in transaction control and data recovery. CLOB, NCLOB, and BLOB are all internal LOB (Large Object) types, with a maximum length of 4 GB. There can only be one column without LONG restrictions. Note: LONG and long raw are not recommended in the new version of Oracle (using BLOB instead), but are reserved for backward compatibility. This article focuses on: 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, bytes t that PL/SQL does not interpret raw data. likewise, Oracle Net does no character set conversions when you transmit raw data from one system to another. the RAW datat Ype takes a required parameter that lets you 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 an integer literal in the range 1 .. 32767. you cannot insert RAW values longer than 2000 bytes into a RAW column. you can insert any RAW value into a long raw database column because th E maximum width of a long raw column is 2147483648 bytes or two gigabytes. however, you cannot retrieve a value longer than 32767 bytes from a long raw column into a RAW variable. note that the long raw datatype is supported only for backward compatibility; see "LONG and long raw Datatypes" on page 3-5 for more information. RAW: RAW; unprocessed; you can use RAW to store binary data or byte characters. For example, a RAW variable can store a series of graphical characters or a digital photo. RAW data is like VARCHAR2 data, except that PL/SQL does not explain it. Similarly, Oracle Net does not convert the character set when RAW data is transmitted. The RAW data type requires you to specify a parameter with the maximum value to 32767. The declaration format is as follows: RAW (maximum_size) You cannot use a symbolic constant or variable to replace this parameter. You must use 1 .. any integer in the value 32767. You cannot insert more than 2000 bytes into the RAW column. you can insert any raw data into the long raw column. The maximum size is 2 GB. However, in turn, it is impossible to retrieve raw data of more than 32767 bytes at a time. Note that long raw is the type of the earlier version. It is not recommended now. For details, see the following: 1.2 The utl_raw-function utl_raw.cast_to_raw utl_raw.cast_to_number utl_raw.cast_to_varchar2 hextorawRAW is saved as a hexadecimal number. When HEXTORAW is used, the data in the string is treated as a hexadecimal number. When UTL_RAW.CAST_TO_RAW is used, the ASCII code of each character in the string is directly stored in RAW fields. 1.3 example: drop table test_raw; create table test_raw (msg raw (2000 )); SCOTT @ orcl> insert into test_raw values ('<xml> <name> Dylan </name> <score> 100 </score> </xml> '); insert into test_raw values ('<xml> <name> Dylan </name> <score> 100 </score> </xml>') * row 1st error: ORA-01465: invalid hexadecimal number -- note that the hexadecimal SCOTT @ orcl> insert into test_raw values (utl_raw.cast_to_raw ('<xml> <name> Dylan </name> <score> 100 </score> </xml> ')); one row has been created. SCOTT @ orcl> commit; -- View select msg from test_raw; MSG------------------------------------------------------------------------------3C786D6C3E3C6E616D653E44796C616E3C2F6E616D653E3C73636F72653E3130303C2F73636F72653E3C2F786D6C3E0ABC SCOTT @ orcl> select utl_raw.cast_to_varchar2 (msg) from test_raw; UTL_RAW.CAST_TO_VARCHAR2 (MSG) then )------------------------------------------------------------------------------ <Xml> <name> Dylan </name> <score> 100 </score> </xml> 2. LONG and long raw types can be used to store variable-length strings. The Long type is like VARCHAR2, except that the maximum capacity of LONG is 32760; the long raw type is used to store binary data or byte strings. Long raw data is like LONG data, which is not interpreted by PL/SQL except LONG RAW data. The maximum capacity of long raw is also 32760. you can insert any LONG data into the LONG column, and the maximum length is 2 GB. However, variables of the LONG type in PL/SQL can only be up to 32760. This rule also applies to the long raw type. LONG Columns in a table can store text, character arrays, and even short documents. You can perform UPDATE, INSERT, and SELECT operations on columns of this type. However, you cannot use expressions, such as SQL function calls, or specific SQL condition statements such as WHERE, GROUP BY, and CONNECT. In SQL 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, in the then issues an error message because you cannot pass LONG values to a SQL functionSQL statement, PL/SQL binds the LONG type as the VARCHAR2 type. However, if the length of the bound VARCHAR2 exceeds 4000, ORACLE automatically converts it to LONG and throws an error because you cannot pass the LONG value to the SQL function. -- For example, SCOTT @ orcl> create table long_test (id number, msg long); the table has been created. SCOTT @ orcl> insert into long_test values (1, 'Hello World'); 1 row has been created. SCOTT @ orcl> commit; the submission is complete. SCOTT @ orcl> select * from long_test where msg = '000000'; select * from long_test where msg = '000000' * row 123 error: ORA-00997: the LONG data type SCOTT @ orcl>/id msg ---------- invalid 1 hello worldSCOTT @ orcl> select id, trim (msg) from long_test where id = 1; select id, trim (msg) from long_test where id = 1 * 1st row error: ORA-00932: Data Type inconsistent: NUMBER, However, LONG3 and CLOB can use the character data of the CLOB type. Each CLOB variable stores a locator pointing to a large character. CLOBs participant fully in transactions, are recoverable, and can be replicated. changes made by package DBMS_LOB can be committed or rolled back. CLOB locators can span transactions (for reads only), but they cannot span sessions. CLOB participates in the overall transaction and can be recovered and repeated. Data changed by the DBMS_LOB package can be submitted and rolled back. The CLOB locator can be cross-transaction, but not cross-session. 4. BLOB You use 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 particle fully in transactions, are recoverable, and can be replicated. changes made by package DBMS_LOB can be committed or rolled back. BLOB locators can span transactions (for reads only), but they cannot spa N sessions. It is used to store large binary objects. BLOB participates in the overall transaction and can be recovered and repeated. Data changed by the DBMS_LOB package can be submitted and rolled back. BLOB locators can be cross-transaction, but not cross-session. Drop table blob_test; SCOTT @ orcl> create table blob_test (id number primary key, content blob not null); the table has been created. SCOTT @ orcl> insert into blob_test values (1, '20140901'); 1 row has been created. SCOTT @ orcl> commit; the submission is complete. SCOTT @ orcl> select * from blob_test; SCOTT @ orcl> set linesize 2000SCOTT @ orcl>/id content ---------- values 1 11111_111scott @ orcl> insert into blob_test values (1, '2014> '); insert into blob_test values (1, '2014>') * 11111000011111 rows error: ORA-01465: invalid hexadecimal number SCOTT @ orcl> update blob_test set content = to_blob ('123') where id = 1; 1 row updated. SCOTT @ orcl> rollback2; rollback completed. SCOTT @ orcl> select * from blob_test; id content ---------- --------------------------------------------------------------------- 1 11111000011111 delete from blob_test where id = 1; commit;