How to manipulate BLOBs, CLOB, BFILE

BFILE binary files, stored in an operating system file outside the database, read-only. Treat this file as binary.

BLOB binary Large object. A large object stored in a database, usually a file such as an image sound.

CLOB large-character object. General storage of large quantities of text information. Stores single byte, fixed-width data.

NCLOB Byte character large object. Store Single-byte chunks, multibyte fixed-width, multi-byte variable-width data

BFILE

The BFILE data type enables access to binary file lobs, are stored in file systems outside Oracle Database. A BFILE column or attribute stores a BFILE locator, which serves as a pointer to a binary file on the server file system. The locator maintains the directory name and the filename.

With the bfile type, Oracle databases have access to binaries stored on the operating system. We can assume that a pointer to the operating system file is stored in the bfile. With bfile You must first create the directory and have the appropriate permissions.

You can change the filename and path of a BFILE without affecting the base table by using the Bfilename function.

Set the value of the bfile type by bfilename the built-in function, which has two parameters, directory and filename.

Binary file lobs don't participate in transactions and are not recoverable. Rather, the underlying operating system provides file integrity and durability. BFILE data can is up to 264-1 bytes, although your operating system could impose on this

Maximum. The database administrator must ensure that's external file exists and that Oracle processes have system read permissions on the file. The BFILE data type enables read-only support of large files. You cannot modify or replicate such a file. Oracle provides APIs to access file data. The primary interfaces that you use to access file data are the Dbms_lob package and Oracle call Interface (OCI).

The operation of the operating system files pointed to by bfile, not managed by the database transaction, the operating system is responsible for consistency and persistence, bfile can handle the file size of 2^64-1 bytes, so the bfile file size restrictions often come from the operating system level. For files pointed to by bfile, the database has read-only permissions and cannot be edited and replicated. We mainly manage and manipulate bfile type data through Dbms_lob packages.

<span style= "font-size:12px" >[oracle@oadata dir1]$ pwd/home/oracle/oradir/dir1 [oracle@oadata dir1]$ ls 1.tx T 2.txt [Oracle@oadata dir1]$ cat 1.txt 1 2 3 4 5 6 [Oracle@oadata dir1]$ cat 2.txt a b c d [or  
      
Acle@oadata dir1]$ sqlplus/as sysdba sql*plus:release 11.2.0.3.0 Production on Saturday October 12 11:58:08 2013  Copyright (c) 1982, Oracle.  
      
      
All rights reserved. Connecting to: Oracle Database 11g Enterprise Edition release 11.2.0.3.0-64bit Production with the partitioning, OLAP, Data M  
Ining and real application testing options sql> Create Direcotry dir1 as '/home/oracle/oradir/dir1 '; Create Direcotry Dir1 as '/home/oracle/oradir/dir1 ' * Line 1th error occurred: ORA-00901: Invalid create command Sql&gt ;  
      
Create directory Dir1 as '/home/oracle/oradir/dir1 ';  
      
The directory has been created.  
      
Sql> Grant Read,write on directory Dir1 to Easy;  
      
The authorization was successful.  
Sql> Conn Easy/jodezhu is connected. sql> sHow user User is "easy" sql> CREATE TABLE Tab_bfile (ID number,bf bfile);  
      
Table has been created.  
      
sql> INSERT into tab_bfile values (1,bfilename (' DIR1 ', ' 1.txt '));  
      
1 lines have been created.  
      
sql> INSERT into tab_bfile values (2,bfilename (' DIR1 ', ' 2.txt '));  
      
1 lines have been created.  
      
Sql> commit;  
      
Submit completed.  
      
    Sql> select * from Tab_bfile; ID----------BF------------------------------------------------------------------------------------------------ ----1 bfilename (' DIR1 ', ' 1.txt ') 2 bfilename (' DIR1 ', ' 2.txt ') sql> set server  
  Output on DECLARE R Tab_bfile%rowtype;  
  CURSOR C is SELECT id,bf from Tab_bfile;  
  AMOUNT INTEGER: = 100;  
  OFFSET INTEGER: = 1;  
Outraw VARCHAR2 (100);  
  BEGIN OPEN C;  
    LOOP FETCH C into R;  
    EXIT when C%notfound;  
    Dbms_output.put_line (r.id); Dbms_lob.open (R.bf,dbms_lob.  
    LOB_READONLY);  Dbms_lob.read (R.bf,amount,offset,outraw);
    Dbms_output.put_line (Outraw);  
  Dbms_lob.close (R.BF);  
 End LOOP;  
 End;  
      
22/1 310a320a330a340a350a360a 2 610a620a630a640a pl/sql process completed successfully. Sql> </span>

The bfile type must be opened before it can be used.

Blob\clob\nclob

Blob\clob\nclob is called an internal lob (bfile called an external lob), and its size is limited to the product of the block size of the table space in 2^32-1, which ranges from 8T to 128T, so in most cases we don't have to worry about storage caps. BLOB types store binary stream data, while CLOB and NCLOB store large character data, stored in CLOB according to the database's character set, and stored in NCLOB according to the database's national character set. Internal LOB, which is stored inside the database, participates in transaction processing of the database and can be rolled back when errors are sent. LOB data can be processed by Plsql Dbms_lob packages or OCI interfaces.

The processing of LOB data can not be run between open/close function pairs, where the corresponding indexes are updated in a timely manner along with the processing of LOB, which may affect efficiency. When we place it in a open/close pair, the index is updated when close. Open/close must appear in pairs.

Attention:

It is a error to commit the transaction before closing all opened lobs this were by the opened. When the error is returned, the openness of the ' open lobs is discarded, but the transaction-is successfully committed. Hence, the changes made to the LOB and Non-lob data in the transaction are, committed the domain and but D indexes are not updated. If This is happens, you are should rebuild the functional and domain indexes on the LOB column.