Oracle LOB data Type application instance

There are three types of lob:

Blob:binary Large Object Binary Large Objects
Clob:character Large Object single character large objects
Double-byte Character Large Object Double Byte character large objects
Large data types in Oracle:
Long: variable-length string data, the longest 2g,long has an attribute of VARCHAR2 column, can store long text, a table with up to one long column
Long RAW: variable Long binary data, up to 2G

CLOB: Character large object CLOB used to store single-byte character data
NCLOB: Used to store multibyte character data
BLOB: for storing binary data

BFILE: Binary data stored in a file that can only be accessed by read-only data. However, the file is not included in the database.
bfile Field The actual file is stored in the file system, and the file location pointer is stored in the field. Bfile is read-only and does not participate in transactional control and data recovery for Oracle.
　　
Clob,nclob,blob are internal lob (Large Object) types, maximum 4G, no long can only have one column limit
It's also nice to save pictures, text files, and Word files with Blob,long raw, but LONG is the type that Oracle is going to discard, and there are only one column limitations, so it is recommended to use BLOBs.

Oracle stored procedure Operations LOB field Instance one:
One of the problems in doing the project is that there are multiple sets of systems (0,A,B,C) in which the central system (0) extracts data from other subsystems (A,B,C). The principle of the design is to create the stored procedure in the central system, then connect to the subsystem through the Dblink, and extract the data (insert or update to the central system) according to the marked field in the Subsystem table.

Stored procedure Syntax (pseudo code):

The code is as follows

Copy Code

CREATE OR REPLACE PROCEDURE "Pro_n_roles" (v_filter in varchar2: = ') Is V_role VARCHAR2 (30); V_treeauth VARCHAR2 (4); V_ori number (22); V_updateflag VARCHAR2 (2); Subsystem Label Field V_sql varchar2 (4000); Type VV Is REF CURSOR; Cur_data VV; Begin V_sql: = ' Select Role,treeauth,updateflag From Roles@db_n//create a good Dblink where Uploadflag = ' Y ' ' | | V_filter; subsystem Label field, Open Cur_data For V_sql; Loop Fetch cur_data into V_role,v_treeauth, V_updateflag; Exit When Cur_data%notfound; If V_updateflag = ' N ' Then Begin INSERT Into J_roles ( Role, Treeauth, Sori,//SUBSYSTEM Www.111cn.net PRIMARY key value S_flag//SUBSYSTEM markings ) VALUES ( V_role, V_treeauth, V_ori,//a system table primary key value ' A system ' ) ; Commit UPDATE Roles@db_n SET uploadflag = ' N ', Updateflag = ' Y ' WHERE The ORI = V_ori; Commit End End If; If V_updateflag = ' Y ' Then Begin UPDATE J_roles SET role = V_role, Treeauth = V_treeauth, The ORI = V_ori, Sori = V_ori, S_flag = ' a system ' WHERE Sori = V_ori and S_flag = ' a system '; Commit UPDATE Roles@db_n SET uploadflag = ' N ', Updateflag = ' Y ' WHERE The ORI = V_ori; Commit End End If; 001 Add the Update CLOB field code here End Loop; Close Cur_data; End Pro_n_roles;

There is no definition of the LongVarChar type in the Oracle stored procedure, the varchar type variable is defined with a maximum length of 32,767 characters, so the Clob type field cannot be stored directly with the stored procedure variable.
There are a number of methods on the web, either by creating temporary tables or by intercepting and merging the methods. According to my side of the actual situation, such a few fields, using a direct Update method.

So in the above comment "//001 here Add the update Clob field code" to add the following code:

The code is as follows	Copy Code
UPDATE J_roles SET othersituation = ( SELECT Othersituation From Roles@db_n WHERE The ORI = V_ori ) ， Suggest = ( SELECT Suggest From Roles@db_n WHERE The ORI = V_ori ) WHERE Sori = V_ori and S_flag = ' a system ';

Oracle9i Database Store and read Clob file (Pl/sql script) instance two (turn)

The code is as follows

Copy Code

First set up the table Lob_example1 CREATE TABLE Lob_example1 ( ID Number (6) primary key, Name VARCHAR2 (10), Resume Clob ); Inserting data INSERT into lob_example1 values (1, ' Pig ', Empty_clob ()); INSERT into Lob_example1 values (2, ' Dog ', Empty_clob ()); Commit Create directory, save in C packing directory CREATE OR REPLACE DIRECTORY DOCS as ' C: '; //Create stored procedures to write the contents of the file to the database Clob Create OR REPLACE PROCEDURE update_doc (    t_id  number,  & nbsp FileName varchar2   ) as    lobloc clob    fileloc bfile;    ; amount int;    Src_offset int:=1;    Dest_offset int:=1;    CSID int:=0;    lc  int:=0;    warning int; Begin    fileloc:=bfilename (' DOCS ', filename);    Dbms_lob.fileopen (fileloc,0);    amount:=dbms_lob.getlength (Fileloc);    Select Resume into Lobloc from Lob_example1      where id=t_id for update; & nbsp;  Dbms_lob.loadclobfromfile (lobloc,fileloc,amount,dest_offset,src_offset,csid,lc,warning);    Dbms_lob.fileclose (fileloc);    commit; End; Call the stored procedure, read the file into the database CLOB, have these two files under C disk Call Update_doc (1, ' aa.csv '); Call Update_doc (2, ' bb.csv '); View file size in rows with IDs of 2 and 1 Select Length (resume) from Lob_example1 where id=2; Select Length (resume) from Lob_example1 where id=1; File has been put into the database Read the file from the database Clob CREATE OR REPLACE PROCEDURE Get_doc ( T_ID number, FileName VARCHAR2 ) As Lobloc Clob; amount int; Offset int:=1; Buffer Varchar2 (2000); Handle Utl_file.file_type; Begin Select Resume into Lobloc from Lob_example1 where id=t_id; Amount:=dbms_lob.getlength (Lobloc); Dbms_lob.read (Lobloc,amount,offset,buffer); Handle:=utl_file.fopen (' DOCS ', filename, ' W ', 2000); Utl_file.put_line (Handle,buffer); Utl_file.fclose (handle); End / Call the process and read the file. Call Get_doc (1, ' zz.csv ');

Haha, the file is saved in the C-plate ah, go and see it

Yes, the file will cause the buffer is too small, buffer maximum allowable 36k, big I do not know how to do, is being resolved!

Author: Wolf Number one