Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle LONG RAW BLOB CLOB type of presentation     - Ubuntu 12.04 64bit Install Source Insight 3.5 and create desktop icons (Linux)

- DBCA Error: ORA-19809: limit exceeded for recovery files process (Database)

- CentOS 6.5 install VNC-Server (Linux)

- How to configure a development environment elegant Lua (Linux)

- Ubuntu set Swap Space Tutorial (Linux)

- Oracle partition table data migration, process management automation (Database)

- How to migrate MySQL to MariaDB under linux (Database)

- CentOS7 virtual machine creation failed Solution (Linux)

- crontab task scheduling Health Check (Linux)

- Oracle database file path changes (Database)

- Mount NFS network file system (Linux)

- OpenGL Programming Guide (8th edition of the original book) - compute shader (Programming)

- MySQL 5.5 on master-slave copy filter (Database)

- ORA-01000 Solution (Database)

- HTML5 Application Cache (Programming)

- Using 30 seconds to write a detailed analysis of garbage removal system (Linux)

- linux network security experience (Linux)

- Embedded Linux to solve the problem in the time zone (Linux)

- Why is better than Git SVN (Linux)

- C language - Traverse pci device (Programming)

 
         
  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:      
 
- Sublime Text Add instructions to insert the current time zone (Linux)
- Linux NFS service fixed ports and firewall configuration (Linux)
- Linux operation and maintenance engineers face questions Intermediate (Linux)
- Git Tutorial Comments (Linux)
- Linux (SUSE) mount NTFS mobile hard practice (Linux)
- Depth understanding of the use of Spring Redis (Programming)
- Ubuntu disable graphics card (Linux)
- Python programming style (Programming)
- Understanding Linux firewall Iptables (Linux)
- Analysis examples: Intrusion Response Linux platform Case (Linux)
- Cryptography development environment to build under Ubuntu (Linux)
- Linux System Getting Started Tutorial: how to find information on Linux-embedded module (Linux)
- Linux system monitoring, top command of the diagnostic tool Detailed (Linux)
- Using 30 seconds to write a detailed analysis of garbage removal system (Linux)
- Cobbler batch install Ubuntu / CentOS system (Linux)
- C ++ Fundamentals study notes (Programming)
- To install Internet security firewall Raiders (Linux)
- Android Studio Installation and Configuration Guide tutorial (Linux)
- Binary Packages Golang (Linux)
- CentOS7 management of systemd (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.