39. LOB DATA TYPE

LOB Data Type:

Before of 8i if you want to store the images or huge information we opt(choose) for long data types but, we have so many restrictions on long data types to overcome this disadvantage we have LOB data type.

Cliassifications of LOB datatype:-

LOB datatypes can be devided as below:

·         Internal

¨       Persistance

¨       Non-persistence(temporary)

§  BLOB

§  CLOB

§  NCLOB

·         External

·         Bfiles

CLOB:  Allowshuge information up to 4GB from 11g onwords even it supports 8 to 128TB.

BLOB:  Stores binary data, allows images, audio,video files and so on…

NCLOB:  It stores national data base chracterset data. Supports multi languages.

Bfiles:  Useful to store files in the form of binary data.

Commit and rollback is not passable to provide on this data.

·         We’e to make use of transactions by using locators.

·         We handle LOB datatype through a package is’DBMS_LOB’

Locator starts-null,0,empty.

Handling lob data by using DBMS_lob package:_

If we are using Lob datatype’s we’ve to make use of packages compulsoraly.

Declare

VCLOB clob;

Max_size integer;

Offset integer:=1;

Wdata varchar(100):=’welcomw to LOB world’;

Rdata varchar2(100);

Begin

Delete from clobdata;

Insert into clobtab values(10,empts_clob);

Select ccol into vclob from clobtab;

Max_size:=length(wdata);

Dbms_lob.open(vclob,dbms_lob.lob_readwrite);

Dbms_lob.write(vclob,max_size,offset,wdata);

Dbms_lob.read(vclob,max_size,offset,rdata);

Dbms_lob.close(vclob);

DOPL(rdata);

End;

Create table clobtab(sno number(5),ccol clob);

Insert into clobtab values(10,’abc’);

Create table imagetab(sno number(5), image bfile);

Declare

Bimage bfile:=bfilename(‘DY’,’gimage.bmp’);

Image_loc bfile;

Max_data integer:=60;

Offset_integer:=1;

Raw_data raw(100);

Begin

Delete from imagetab;

Insert into imagetab values(10,bimage);

Select image into image_loc from imagetab;

Dbms_lob.open(image_loc);

Dbms_lob.read(image_loc, max_data, offset,raw_data);

Dbms_lob.close(image_loc);

DOPL(rawtohex(raw_data));

DOPL(utl_raw.cast_to_varchar2(raw_data));

End;

·         Create directory dy as ‘c:\’;

·         Grant read on directory by to apps;

·         Create a file with name ‘gv.txt’ and provide data and save in path mentioned.

As in directory ‘dy’;

Create table direction(sno number(10),dcol clob);

Declare

Vdir bfile:=bfilenamme(‘DY’,’gv.txt’);

Colfile clob;

Max_data integer:=10;

Des_offset integer:=1;

S_ofset integer:=1;

Lang_ctx integer:=dbms_lob.default_lang_ctx;

War_mes integer;

Begin

Delete from direction;

Insert into direction values(10, empty_clob);

Selection dcol into colfile from direction;

Dbms_lob.open(colfile,dbms_lob,lob_readwrite);

Dbms_lob.open(vdir);Dbms_lob.load clob from file(colfile, vdir, dbms_lob.maxsize, des_offset, s_offset, nls_charset_id(‘USTASCII’), lang_ctx, war_mes);

Dbms_lob.close(colfile);

Dbms_lob.close(vdir);

End;

Select * from direction;