From csdn
========================================================
In Oracle, four lobs types are available: blob, clob, bfile, and nclob.
The following is a brief introduction to the lob data type.
L BLOB: Binary lob, Which is binary data. It can be up to 4 GB and stored in the database.
L clob: character lob, character data, up to 4 GB, stored in the database.
L bfile: binary file; read-only binary data stored outside the database. The maximum length is limited by the operating system.
L nclob: supports a clob column of the byte character set (nultibyte characterset.
Oracle Database developers often encounter lob data retrieval and operation. I will introduce some methods and skills for LOB data processing in Oracle, and hope to help readers in future development.
Oracle can use multiple methods to retrieve or operate lob data. The common solution is to use the dbms_lob package.
Other methods include application programming interfaces applications. Program And Oracle call interface (OCI.
1. In the Oracle development environment, we can use the dbms_lob package for processing! Dbms_lob package is powerful and simple to use. It can be used to read internal lob objects or process bfile objects. However, there is a difference between the two. Read and Write operations can be performed when processing internal lob objects (blob, clob), but read operations can only be performed when processing external lob object bfile, write operations can be processed using PL/SQL. In addition, you can use SQL to process lob, but note that SQL can only process the entire lob and cannot operate on lob data slices.
The dbms_lob package has built-in functions such as read (), append, write (), erase (), copy (), getlength (), and substr, you can easily operate lob objects. We will not discuss it in depth here. Readers can refer to relevant books.
For PL/SQL, the following describes how to use dynamic PL/SQL statements to process clob objects and pass them as table names!
Example 1.
Dynamic PL/SQL. For clob field operations, you can pass the table name table_name, the unique table Identifier Field name field_id, And the clob field name field_name Record Number v_id to start processing the character location v_pos, passed string variable v_clob
Update the PL/SQL PROCESS OF clob: updateclob
Create or replace procedure updateclob (
Table_name in varchar2,
Field_id in varchar2,
Field_name in varchar2,
V_id in number,
V_pos in number,
V_clob in varchar2)
Is
Lobloc clob;
C_clob varchar2 (32767 );
AMT binary_integer;
Pos binary_integer;
Query_str varchar2 (1000 );
Begin
POs: = v_pos * 32766 + 1;
AMT: = length (v_clob );
C_clob: = v_clob;
Query_str: = 'select' | field_name | 'from' | table_name | 'where' | field_id | '=: ID for Update ';
-- Initialize buffer with data to be inserted or updated
Execute immediate query_str into lobloc using v_id;
-- From POS position, write 32766 varchar2 into lobloc
Dbms_lob.write (lobloc, AMT, POs, c_clob );
Commit;
Exception
When others then
Rollback;
End;
L/usage instructions:
Before inserting or modifying, insert or modify other fields. Set the clob field to empty empty_clob (),
Then, call the above process to insert more than 2048 to 32766 characters.
If you want to insert more than 32767 characters, compile a loop to solve the problem.
Query the PL/SQL function of clob: getclob
Create or replace function getclob (
Table_name in varchar2,
Field_id in varchar2,
Field_name in varchar2,
V_id in number,
V_pos in number) return varchar2
Is
Lobloc clob;
Buffer varchar2 (32767 );
Amount number: = 2000;
Offset number: = 1;
Query_str varchar2 (1000 );
Begin
Query_str: = 'select' | field_name | 'from' | table_name | 'where' | field_id | '=: id ';
-- Initialize buffer with data to be found
Execute immediate query_str into lobloc using v_id;
Offset: = offset + (v_pos-1) * 2000;
-- Read 2000 varchar2 from the buffer
Dbms_lob.read (lobloc, amount, offset, buffer );
Return buffer;
Exception
When no_data_found then
Return buffer;
End;
L usage instructions:
Use select getclob (table_name, field_id, field_name, v_id, v_pos) as partstr from dual;
The clob field can contain 2000 characters to partstr,
Compile a loop to combine partstr into a target string of the length of dbms_lob.getlength (field_name.
2. the lob processing methods vary with other development environments, such as Vc, VB, Pb, and Java, here we will give a few examples to illustrate how lob is not processed in the Oracle development environment.
(1) Processing in Pb
Exampler 2.
String ls_path, ls_filename, ls_jhdh
Long ll_num, ll_count, RTN
Blob ole_blob
Ll_num = dw_lb.getrow ()
If ll_num> 0 then ls_jhdh = dw_lb.object.ct_njhdh [ll_num]
Select count (*) into: ll_count from sj_jh_jhfjb where ct_jhdlxbh = '1' and ct_jhdh =: ls_jhdh and ct_jdlxbh =: is_jdlx;
If ll_count> 0 then
RTN = MessageBox ("prompt", "Whether to modify this attachment", question !, Yesno !, 1)
If RTN = 1 then
Selectblob ct_jhfjnr into le_blob from sj_jh_jhfjb where ct_jhdlxbh = '1' and ct_jhdh =: ls_jhdh and ct_jdlxbh =: is_jdlx;
Ole_1.objectdata = ole_blob
If ole_1.activate (offsite !) & Lt;> 0 then
MessageBox ("Ole activate", "cannot activate ")
Return-1
End if
End if
Else
MessageBox ("prompt", "no attachment ")
End if
End if
(2) Processing in VB
To process large objects in VB, you can use oo4o (Oracle Objects for OLE) to process large objects. This section describes a method to process large object blob without 0040.
The following program can save a file (text file, DOC file, image file, etc.) to the database and read it from the database.
Two commandbuttons are required.
Cmd1 name cmdsave caption save
Cmd2 name cmdread caption read
A cmddialog Control
At the same time, you need to create a table t_demo (field ID type number,; field text type blob ;)
Exmple 3.
Option explicit
Dim rn as ADODB. Connection
Public Function createdatasource (datasource as string, userid as string, password as string) as Boolean
On Error goto dbconerr:
Set Rn = new ADODB. Connection
With Rn
. Connectionstring = "provider = oraoledb. oracle.1 ;"&_
"Password =" [$ password & "]" & _
"User ID =" [$ userid & "]" & _
"Data Source =" [$ datasource & "]" & _
& Quot; locale identifier = 2052 & quot"
. Open
End
Createdatasource = true
Exit Function
Dbconerr:
Createdatasource = false
End Function
Private sub cmdread_click ()
Dim RS as new ADODB. recordset
Rs. activeconnection = rn
Rs. locktype = adlockoptimistic
Rs. cursorlocation = aduseclient
Rs. Source = "select * From t_demo"
Rs. Open
Comdlgdir. dialogtitle = "save file"
Comdlgdir. Filter = "*.*"
Comdlgdir. showsave
Call blobtofile (Rs. Fields ("text"), comdlgdir. filename)
Set rs = nothing
Exit sub
Set rs = nothing
End sub
Private sub cmdsave_click ()
Dim RS as new ADODB. recordset
Rs. activeconnection = rn
Rs. locktype = adlockoptimistic
Rs. cursorlocation = aduseclient
Rs. Source = "select * From t_demo"
Rs. Open
Rs. addnew
Comdlgdir. dialogtitle = "select file"
Comdlgdir. showopen
Rs. Fields ("ID"). value = 1
If comdlgdir. filename & lt;> "" then
Call filetoblob (Rs. Fields ("text"), comdlgdir. filename)
Rs. Update
End if
Set rs = nothing
Exit sub
Set rs = nothing
End sub
Private sub form_load ()
If not createdatasource ("Sid", "systemp", "manager") then
Msgbox "connection failure! "
End if
End sub
Stored as ADODB. Field, filename as string, optional chunksize as long = 8192)
Dim fnum as integer, bytesleft as long, bytes as long
Dim TMP () as byte
If (attributes. attributes and adfldlong) = 0 then
Err. Raise 1001, "field doesn't support the getchunk method ."
End if
If dir $ (filename) = "" Then err. Raise 53, "file not found"
Fnum = freefile
Open filename for binary as fnum
Bytesleft = lof (fnum)
Do While bytesleft
Bytes = bytesleft
If bytes> chunksize then bytes = chunksize
Redim TMP (1 to bytes) as byte
Get fnum, TMP
Consumer. AppendChunk TMP
Bytesleft = bytesleft-bytes
Loop
Close # fnum
End sub
Sub blobtofile (stored as ADODB. Field, filename as string, optional chunksize as long = 8192)
Dim fnum as integer, bytesleft as long, bytes as long
Dim TMP () as byte
If (attributes. attributes and adfldlong) = 0 then
Err. Raise 1001, "field doesn't support the getchunk method ."
End if
If dir $ (filename) & lt;> "" Then kill filename
Fnum = freefile
Open filename for binary as fnum
Bytesleft = bytes. actualsize
Do While bytesleft
Bytes = bytesleft
If bytes> chunksize then bytes = chunksize
TMP = bytes. getchunk (bytes)
Put # fnum, TMP
Bytesleft = bytesleft-bytes
Loop
Close # fnum
End sub
(3) Processing lob with JDBC
Exmple 4.
First, getting blob and clob locators from a result set
// Select lob locator into standard result set.
Resultset Rs into stmt.exe cutequery ("select blob_col, clob_col from lob_table ");
While (Rs. Next ())
{// Get lob locators into Java Wrapper Classes.
Oracle. jdbc2.blob blob = (Oracle. jdbc2.blob) Rs. GetObject (1 );
Oracle. jdbc2.clob clob = (Oracle. jdbc2.clob) Rs. GetObject (2 );
[... Process...]
}
Then read BLOB data from blob locator.
Inputstream byte_stream = my_blob.getbinarystream ();
Byte [] byte_array = new byte [10];
Int bytes_read = byte_stream.read (byte_array );
And writing BLOB Data
Java. Io. outputstream outstream;
// Read data into a byte array
Byte [] DATA = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
// Write the array of binary data to a blob
Outstream = (BLOB) my_blob). getbinaryoutputstream ();
Outstream. Write (data );
And passing a blob locator to a prepared statement.
Oraclepreparedstatement Ops = (oraclepreparedstatement) Conn. preparestatement
"Insert into blob_table values (?) ");
Ops. setblob (1, my_blob );
Ops.exe cute ();
Note:
Empty_blob () must be used for initialization during insert.
Stmt.exe cute ("insert into my_blob_table values ('row1', empty_blob ()");
(4) Processing in Pro * C
Pro * C can process lob fields in three ways.
1. The dbms_lob package inside PL/SQL blocks.
2. OCI (Oracle call interface) function CILS.
3. Embedded SQL statements.
Embedded SQL statements. The method is simple and flexible. OTN provides an example:
In this example we will be reading data from a blob with an unknown arbitrary length into a buffer and then writing the data from the buffer into an external file.
Our buffer is small, so depending on the size of the Blob we are reading, we may
Be able to read the Blob value into the buffer in a single read Statement or we
May be required to utilize a standard polling method instead.
First we start off with OCI. h and some simple local variable declarations
Example 5.
# Include & lt; OCI. h>
Ocibloblocator * blob;
File * FP;
Unsigned int AMT, offset = 1;
Now we need a buffer to store the Blob value and then write to the file from:
# Define maxbuflen 5000
Unsigned char buffer [maxbuflen];
Exec SQL var buffer is raw (maxbuflen );
Allocate the Blob host variable and select a blob which we will read:
Exec SQL allocate: blob;
Exec SQL select a_blob into: blob from lob_table where ...;
We can then open the external file to which we will write the Blob value:
Fp = fopen (const char *) "image.gif", (const char *) "W ");
If the buffer can hold the entire lob value in a single read we need to catch
Not found condition to signal lob read termination:
Exec SQL whenever not found goto end_of_lob;
Now do our first read. We set the amount to the maximum value of 4 gigabytes. it
Is larger than our buffer so if the lob doesn't fit we will read using a polling
Mode:
AMT = 4294967295;
Exec SQL lob read: AMT from: blob at ffset into: buffer;
If we get here then it means that the buffer was not large enough to hold the entire
Lob value, so we must write what we have using binary I/O and continue reading:
(Void) fwrite (void *) buffer, (size_t) maxbuflen, (size_t) 1, FP );
We use a standard polling method to continue reading with the lob read inside
Of an infinite loop. We can set up the not found condition to terminate the loop:
Exec SQL whenever not found do break;
While (true)
{
During polling, the offset is not used so we can omit it in subsequent lob reads.
We need the amount, however, because it will tell us how much was read in
Last read Invocation
Exec SQL lob read: AMT from: blob into: buffer;
(Void) fwrite (void *) buffer, (size_t) maxbuflen, (size_t) 1, FP );
}
Here, we have reached the end of the lob value. The amount holds the amount
The last piece that was read. during polling, the amount for each Interim piece
Was set to maxbuflen, or the maximum size of our buffer:
End_of_lob:
(Void) fwrite (void *) buffer, (size_t) AMT, (size_t) 1, FP );
(5) Processing in Delphi
For the lob field, I personally think it is more flexible than the long type, and the lob field can save various types of data and save images and a large amount of text, the two types of clob and blob are described. Blob Stores image information and clob stores a large amount of text.
Exmple 6.
Create Table test_table
(C_no number (1) not null,
C_blob blob,
C_clob clob,
Constraint pk_test_table primary key (c_no ));
Unit unit1;
Interface
Uses
Windows, messages, sysutils, variants, classes, graphics, controls, forms,
Dialogs, stdctrls, dbctrls, grids, dbgrids, DB, dbtables, extdlgs;
Type
Tform1 = Class (tform)
Database1: tdatabase; // used to connect to the database
Table1: tTable; // obtain table information
Performance1: tdatasource;
Dbgrid1: TDBGrid;
Dbmemo1: tdbmemo; // display the content of the c_clob Field
Dbimage1: tdbimage; // display the content of the c_blob Field
Button1: tbutton; // insert button
Button2: tbutton; // Save button
Table1c_no: tfloatfield; // tfiled
Table1c_blob: tblobfield;
Table1c_clob: tmemofield;
Openpicturedialog1: topenpicturedig1; // obtain an image from a file
Opendialog1: topendialog; // obtain text from a file
Procedure button1click (Sender: tobject );
Procedure button2click (Sender: tobject );
Private
{Private Declarations}
Public
{Public declarations}
End;
VaR
Form1: tform1;
Implementation
{$ R *. DFM}
Procedure tform1.button1click (Sender: tobject );
Begin // insert operation
With Table1 do
Begin
Insert; // set the table status to insert
If openpicturedialog1.execute then // obtain image information
Table1c_blob.loadfromfile (openpicturedialog1.filename );
If opendialog1.execute then // obtain text information
Table1c_clob.loadfromfile (opendialog1.filename );
End;
End;
Procedure tform1.button2click (Sender: tobject );
Begin // submit the inserted content
Try
Table1.post;
Except
Application. MessageBox ('error occurred ', 'warn', 0 );
End;
End;
End.
Note:
Openpiceturedilog can only open files such as DMP, ICO, and WMF. You need to save the image file format in advance to these categories;
When text fields are not obtained from files, you can manually enter
This example is just a small exploration of the lob field. It is not used properly and needs to be corrected. Please give me more advice.
Note: This article Article Most of the examples are taken from the Forum. If you infringe your copyright, please send me a letter and I will handle them accordingly.
Note:
Openpiceturedilog can only open files such as DMP, ICO, and WMF. You need to save the image file format in advance to these categories;
When text fields are not obtained from files, you can manually enter
