Basic Oracle binary and big object types

Reference 1. Introduction to Data Types in OracleConceptsOracleDataType: ## i41462.oraclejdbc#guid1] get and set of Object Data Types in java in workwithoracleobjecttype:

References 1. oracle Concepts introduction to Data Types in Oracle DataType: http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i4146 2. oracle JDBC Developer Guid 1: get and set of object Data Types in java in work with oracle object type:

References

1. Oracle Concepts introduction to Data Types in Oracle DataType: http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i4146

2. Oracle JDBC Developer Guid

Introduction to get and set of object Data Types in java in work with oracle object type: http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraoot.htm#autoId0

2) java stream in JDBC introduces how to obtain a stream from a binary type in java and then generate a file in the OS file system. Http://docs.oracle.com/cd/B28359_01/java.111/b31224/jstreams.htm#i1014109

3. Oracle jdbc api each data type API Introduction: http://download.oracle.com/otn_hosted_doc/jdeveloper/905/jdbc-javadoc/index.html? Oracle/SQL/NCLOB.html

I. type differentiation

Long stores variable-length character data (2 GB, backward compatible, not recommended)

NCLOB stores single-byte character data (4 GB)

CLOB stores multi-byte character data (4 GB)

BFILE stores the pointer to the binary file in the OS file system. files are not stored in the database.So after you insert the BFile field into the file, if you delete the file on your computer or move the storage directory, you cannot find the stored file from this field.(264-1 bytes)

BLOB stores binary data (4 GB)

RAW stores fixed-length binary data (2000 bytes), which must be defined, for example, create table t (s raw (2000 );

Long raw stores variable-length binary data (2 GB, not recommended for Oracle ).

Ii. Use of types in Databases

NCLOB

?

1 2 3 4 5 6

create table t(id int,cont NCLOB); Insert into t(id,cont) values(1,’hello’); select * from t; ID CONT ------------------------- 1 hello

CLOBSame as NCLOB.

BLOBNo method can be directly inserted.

BFILE

?

1 2 3 4 5 6 7 8 9 10

Conn lyy/lyy Create table bfiletable(id int,obj BFILE); Create or replace directory dir AS ‘d:\’; Conn / as sysdba Grant read directpry dir to lyy; Conn lyy/lyy Insert into bfiletable(id,obj) values(1,filename(‘DIR’,’1.jpg’)); Select * from filetable; ID OBJ ------------------------------------------------- 1 filename(‘DIR’,’1.JPG’)

RAW

?

1 2 3 4 5 6

Create table rawtable(id int,obj raw(2000)); Insert into rawtable(id,obj) values(1, uti_raw.cast_to_raw(‘hello’)); Select * from rawtable; ID OBJ ------------------------------------------------- 1 68656C6C6F

LONG RAW

?

1 2 3 4 5 6

Create table lrawtable(id int,obj long raw); Insert into lrawtable(id,obj) values(1, uti_raw.cast_to_raw(‘hello’)); Select * from lrawtable; ID OBJ ------------------------------------------------- 1 6