Introduction to the long raw blob clob type in Oracle, blobclob

Introduction to the long raw blob clob type in Oracle, blobclob

Note:

RAW: unprocessed type, which can store binary data or byte characters
LONG: variable-length string data. The maximum length is 2 GB. LONG has the VARCHAR2 column feature. It can store at most one LONG column in a table in LONG text. [not recommended]
Long raw: variable-length binary data, up to 2 GB [not recommended]
CLOB: The character big 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: binary data stored in the file. The data in this file can only be read-only. However, this file is not included in the database.
The bfile field is actually stored in the file system. 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 and no restrictions on LONG columns.

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, and 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 datatype 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 the maximum width of
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 means RAW English: 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.

A maximum value of 32767 must be specified for the RAW data type;

Statement format: RAW (maximum_size)
You cannot use a symbolic constant or variable to replace this parameter. Instead, you must use any integer in 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. 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 related tools
-Package
Utl_raw

-Function
Utl_raw.cast_to_raw
Utl_raw.cast_to_number
Utl_raw.cast_to_varchar2
Hextoraw

RAW stores the 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>')
                            *
An error occurred in line 1:
ORA-01465: invalid hexadecimal digit

-Note that this place is hexadecimal
SCOTT @ orcl> insert into test_raw values (utl_raw.cast_to_raw ('<xml> <name> Dylan </ name> <score> 100 </ score> </ xml>'));

It has created a row.

SCOTT @ orcl> commit;

--View
select msg from test_raw;
MSG
-------------------------------------------------- ----------------------------
3C786D6C3E3C6E616D653E44796C616E3C2F6E616D653E3C73636F72653E3130303C2F73636F72
653E3C2F786D6C3E

0ABC

SCOTT @ orcl> select utl_raw.cast_to_varchar2 (msg) from test_raw;

UTL_RAW.CAST_TO_VARCHAR2 (MSG)
-------------------------------------------------- ----------------------------
<xml> <name> Dylan </ name> <score> 100 </ score> </ xml>
2. LONG and LONG RAW types

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

Use the LONG RAW type to store binary data or byte strings. 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 the LONG column, the maximum length is 2G. However, the LONG type variable in PL / SQL can only support up to 32760.
This rule also applies to the LONG RAW type.

The LONG column in the table can store text, character arrays, and even short documents. UPDATE, INSERT, and SELECT operations can be performed on this type of column.
However, expressions, SQL function calls, or specific SQL conditional statements such as WHERE, GROUP BY, and CONNECT BY cannot be used.

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, then issues
an error message because you cannot pass LONG values to a SQL function

In the SQL statement, PL / SQL binds the LONG type as the VARCHAR2 type. However, if the length of the bound VARCHAR2 exceeds 4000, ORACLE will be automatically converted to LONG,
Then an error is thrown because you cannot pass the LONG value to the SQL function.

--E.g:
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');

It has created a row.

SCOTT @ orcl> commit;

The submission is complete.

SCOTT @ orcl> select * from long_test where msg = '123';
select * from long_test where msg = '123'
                              *
An error occurred in line 1:
ORA-00997: illegal use of LONG data type


SCOTT @ orcl> /

        ID MSG
---------- ---------------------------------------- ----------------------------------------
         1 hello world

SCOTT @ orcl> select id, trim (msg) from long_test where id = 1;
select id, trim (msg) from long_test where id = 1
                *
An error occurred in line 1:
ORA-00932: data type inconsistency: should be NUMBER, but get LONG
3. CLOB
You can use CLOB type large block of character data. Each CLOB variable stores a locator, pointing to a large block of character data.

CLOBs participate 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, can be recovered, and can be repeated.
Data changed by the DBMS_LOB package can be submitted and rolled back. The CLOB locator can cross transactions, but not cross sessions.

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 participate 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 span sessions.

Used to store large binary objects, BLOB participates in the overall transaction, can be recovered, and can be repeated.
Data changed by the DBMS_LOB package can be submitted and rolled back. BLOB locators can cross transactions, but not sessions.

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, '11111000011111');

It has created a row.

SCOTT @ orcl> commit;

The submission is complete.

SCOTT @ orcl> select * from blob_test;

SCOTT @ orcl> set linesize 2000
SCOTT @ orcl> /

        ID CONTENT
---------- -----------------------------------
         1 11111000011111


SCOTT @ orcl> insert into blob_test values (1, '11111000011111>');
insert into blob_test values (1, '11111000011111>')
                                             *
An error occurred in line 1:
ORA-01465: invalid hexadecimal digit


 SCOTT @ orcl> update blob_test set content = to_blob ('110010000110011') where id = 1;

1 row updated.

SCOTT @ orcl> rollback
  2  ;

The rollback is complete.

SCOTT @ orcl> select * from blob_test;

        ID CONTENT
---------- ---------------------------------------- -----------------------------
         1 11111000011111

 delete from blob_test where id = 1;
 commit;
Copyright statement: This article is an original article by bloggers and may not be reproduced without the permission of the bloggers.