Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle LONG RAW BLOB CLOB type of presentation     - Linux System Getting Started Learning: Repair (Linux)

- HTTP and HTTPS request response process difference (Linux)

- Tab set to four spaces in Vim (Linux)

- Linux automatic installation and implementation (Linux)

- How to release the cache memory on Linux (Linux)

- RabbitMQ tutorial examples: RabbitMQ installation under Windows (Linux)

- C ++ containers (Programming)

- Share and show your code on GitHub (Linux)

- Install Ubuntu 14.04 and Windows 8 / 8.1 dual-boot in UEFI mode (Linux)

- MySQL concat function is SQL injection (Linux)

- Java List add duplicate the same object (Programming)

- RHEL 6.6 install GNOME desktop environment (Linux)

- Teach you how to protect the security of Linux desktop (Linux)

- Installation of Python2.7.10 under CentOS 6.4 (Linux)

- Nginx request processing (Server)

- Use DB2 federated access Oracle (Database)

- Oracle 11g partition maintenance (two) - Coalescing Partitions (Database)

- How to configure MariaDB replication in CentOS Linux (Database)

- JavaScript in this usage (Programming)

- 17 How to install the Ubuntu 14.04 and Linux Mint Kodi14 (XBMC) (Linux)

 
         
  Oracle LONG RAW BLOB CLOB type of presentation
     
  Add Date : 2017-08-31      
         
       
         
  Explanation:

RAW: No processing type, you can store binary data or byte character
LONG: variable-length data string, the longest 2G, LONG VARCHAR2 column having the characteristics of a long text can be stored up to a table LONG column [deprecated]
LONG RAW: variable-length binary data, the longest 2G [deprecated]
CLOB: Character Large Object Clob used to store single-byte character data; large text, such as XML data.
NCLOB: used to store multi-byte character data
BLOB: for storing binary large object data; such as digital photos;
BFILE: binary data stored in the file, the data in this file can only be read-only access. However, the file is not included in the database.
bfile field the actual file is stored in the file system, the file is stored in the field location pointer .bfile for Oracle is read-only, do not participate in transactional control and data recovery.

CLOB, NCLOB, BLOB are internal LOB (Large Object) type, up to 4G, not only restricted LONG column

Note: LONG and LONG RAW are not recommended for use in the new version of Oracle (using the BLOB alternative), but retained 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, except 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 can not use a symbolic constant or variable to specify the maximum size; you must
use an integer literal in the range 1 .. 32767.
You can not 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 a
LONG RAW column is 2147483648 bytes or two gigabytes. However, you can not 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 English meaning: students; unprocessed;
You can use the RAW data type stores binary or byte character. For example, a RAW variable can store a set of graphic character or a digital photo.
RAW data is like VARCHAR2 data, except for one thing: PL / SQL does not interpret them. Similarly, when you transfer data in RAW, Oracle Net will not be the character set conversion.

RAW data type requires you to specify a maximum value of the parameter to 32767;

Statement following format: RAW (maximum_size)
You can not use a symbolic constant or variable instead of this parameter must be used in any integer 1..32767.

You can not insert more than 2000 bytes of characters to RAW column;
You can insert any raw data to the long raw column, maximum support 2G. However, in turn, can not be removed at once more than 32,767 bytes of raw data.

It should be noted here, long raw is the early version of the type; now deprecated; see details below:

1.2 Related Tools
- Package
utl_raw

- Functions
utl_raw.cast_to_raw
utl_raw.cast_to_number
utl_raw.cast_to_varchar2
hextoraw

RAW saved hexadecimal numbers. When using HEXTORAW, will the data string as a hexadecimal number.
While using UTL_RAW.CAST_TO_RAW, directly to each character in the string of ASCII codes stored in the RAW type field.

1.3 Examples

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>')
                            *
Line 1 Error:
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

It can be used to store LONG type variable-length strings. Long as VARCHAR2 type, except LONG maximum capacity of 32760;

Use LONG RAW data type stores binary or byte strings. LONG RAW data is like LONG data, except LONG RAW data can not be PL / SQL explanation.
The maximum capacity for 32760 LONG RAW.

You can insert any LONG LONG column data to a maximum length of 2G. However, LONG type variable in PL / SQL can only support up to 32760.
This rule also applies to LONG RAW type.

Table LONG columns can store text, arrays of characters, or even short documents. UPDATE can do for this type of column, INSERT, and SELECT operations.
But no longer expressions, SQL function calls, or certain SQL conditional statement such as WHERE, GROUP BY, and CONNECT BY.

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 can not pass LONG values to a SQL function

SQL statements, PL / SQL as a LONG type VARCHAR2 type binding. However, if the length of the bound VARCHAR2 exceeds 4000, ORACLE will automatically switch to LONG,
Then throw an error because you can not pass LONG values to a SQL function.

--E.g:
SCOTT @ orcl> create table long_test (id number, msg long);

Table has been created.

SCOTT @ orcl> insert into long_test values (1, 'hello world');

It has created a row.

SCOTT @ orcl> commit;

Submit completed.

SCOTT @ orcl> select * from long_test where msg = '123';
select * from long_test where msg = '123'
                              *
Line 1 Error:
ORA-00997: illegal use of LONG datatype


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
                *
Line 1 Error:
ORA-00932: inconsistent datatypes: should NUMBER, but he received LONG3, CLOB
You can use the CLOB type chunk of character data. Every CLOB variable stores a locator, pointing to a chunk 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 can not span sessions.

CLOB participate in the overall transaction, recoverable, and can be repeated.
DBMS_LOB package by the changed data can commit and rollback. CLOB locators can span transactions, but not across 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 can not span sessions.

For storing binary large objects, BLOB involved in the whole transaction, recoverable, and can be repeated.
DBMS_LOB package by the changed data can commit and rollback. BLOB locators can span transactions, but not across sessions.

drop table blob_test;

SCOTT @ orcl> create table blob_test (id number primary key, content blob not null);

Table has been created.

SCOTT @ orcl> insert into blob_test values (1, '11111000011111');

It has created a row.

SCOTT @ orcl> commit;

Submit completed.

SCOTT @ orcl> select * from blob_test;

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

        ID CONTENT
---------- -----------------------------------
        111,111,000,011,111


SCOTT @ orcl> insert into blob_test values (1, '11111000011111>');
insert into blob_test values (1, '11111000011111>')
                                            *
Line 1 Error:
ORA-01465: invalid hexadecimal digit


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

Updated 1 line.

SCOTT @ orcl> rollback
  2 ;

Rollback has been completed.

SCOTT @ orcl> select * from blob_test;

        ID CONTENT
---------- ---------------------------------------- -----------------------------
        111,111,000,011,111

 delete from blob_test where id = 1;
 commit;
     
         
       
         
  More:      
 
- Node.js Getting the basics: Helloworld! (Linux)
- Java implementation file encryption and decryption (Programming)
- CentOS5 installation Nodejs (Linux)
- Oracle Execute to Parse perform analytical Ratio Analysis (Database)
- CentOS yum source deployment (Linux)
- RHEL 6.6 install GNOME desktop environment (Linux)
- Steps to build MPICH2 development environment on CentOS 6.4 (Linux)
- Linux GCC 5.1.0 compiler installation (Linux)
- Hadoop 2.6.0 stand-alone / pseudo-distributed installation (Server)
- Linux Getting Started tutorial: XWindow what (Linux)
- Cancel Root Command History under Linux (Linux)
- Embedded Linux to solve the problem in the time zone (Linux)
- Redis Linux system installation guide (Database)
- Ubuntu users to install voice switch instructs the applet (Linux)
- Nginx Proxy timeout Troubleshooting (Server)
- The user how to install Notepadqq 0.41.0 under ubuntu and debian (Linux)
- Linux (SUSE) mount NTFS mobile hard practice (Linux)
- Build a super simple "hardware" firewall router (Linux)
- Android gets the global process information and the memory used by the process (Programming)
- Android Unzip the ZIP / GZIP data (based on the InflaterInputStream implementation) (Programming)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.