Describe LOB here


=====================
PL/SQL LOB 
=====================

讒... 觜′ 伎 蟶 螳給... 襷襷豺 蟲...
  る狩伎 螻蠍蠍磯レ 覲願給..
蠏語 LOB 蟯伎 誤 覲願...襦貅危, DBMS_LOBれ 覯 覲願給..

=========================================
 一危襯 豌襴蠍  一危 
=========================================

BLOB  : 蟲譟壱 讌  伎 一危襯 , 豕 4蠍郁                      --> 碁
CLOB  : 蟲譟壱 讌  覓語 一危襯 , 豕 4蠍郁                      --> 企
NCLOB : ル 覓語 一危  れ 蟲螳 語 讌蠍磯 , 豕 4蠍郁 --> 企
BFILE : 蟲譟壱 讌  一危襯 一危覯伎 碁 , 豕 4蠍郁       --> 企

========================
 る語 
========================

- 企ろ磯  
- group by, order by, select distinct, join覓語企 讌   
- 覿磯螳
- varray    

==========================
LOB LONG 一危 觜蟲
==========================

LOB                                     LONG
---------------------------------       -----------------------------
   LOB リ            襷 
 一危 螳讌         一危 螳讌 
貉殊 LOB 襦貅危磯               豌願 貉殊  
(LOB 蠍郁 3964覦危語企 貉殊 )
LOB 貉殊 蠏狩 襦貅危郁 覦   豌願 覦
4GB蟾讌                             2GB蟾讌 
 ろ 語り                  豌覿  豺蟾讌 ′語
讌螻 覿磯 蟆曙 覈 覲旧螳      覲旧 覿螳

==================================
LONG 一危 LOB襦 覲蠍
==================================
TO_LOB襯 覃 ...(覯 磯 襴伎 ...  讌覿 覃伎殊 谿瑚..)

<覓碁>
TO_LOB (LONG 螳)

蟒 碁螳 朱Μ 螳...

==================
碁  蠍
==================

碁 殊 る 襷 る狩伎 一危覯伎る伎 ル 一危 蠏狩 蟆 
伎豌伎 殊 蠏狩る 蟆..
企 蠍磯レ  蟆 BFILE  螳豌伎...
碁殊 語 る 襾殊 襴 螳豌企ゼ 燕伎狩...          <----------------------- 覦 襴 螳豌 燕..!!!!!!!!
れ 覓碁..

CREATE [OR REPLACE] DIRECTORY 襴企 AS 蟆暑

- 襴 螳豌伎 企 豕 30蟾讌
- 殊企 2000蟾讌

襾殊 れ旧  一 F:\Books朱 襴襯 燕螻 蠏碁狩 9螳襯 讌企ｌ給..
蠏碁壱 襴 る碁ゼ 燕企慨蟆給..

SQL> connect yasi/yasi
郁屋給.
SQL> create or replace directory books_dir as 'F:\Books';

襴螳 焔給.

=================
BFILE  
=================

- 碁 讌讌 .
- 殊 所鍵 朱 企Π.
- too many files open る襯 譟一伎 .. 願屋 朱誤 殊 SESSION_MAX_OPEN_FILES = 20 螳 譟一..
  伎豌伎 蟲 螻ろ螻, 殊 伎 覦 譯殊伎 . 蠏碁讌 朱 讌  
  螻 企Π朱 蠍磯伎, れ襦  殊 危襦 ′語ろ螻  磯 襯 覦蟆 .
  る螳 覦 覈 殊  覦 ..
  
=====================================  
BFILE螻 蟷 DBMS_LOB れ 蠍
=====================================

朱 DBMS_LOB れ襯 蠍 伎 れ螻 螳 2螳讌 ろ襴渚碁ゼ ろ..817 覯 讌..

SQL> @ORACLE_HOME/rdbms/admin/dbmslob.sql
SQL> @ORACLE_HOME/rdbms/admin/prvtlob.plb

る狩伎 碁 LOB襷  襯 磯 螻牛..
れ 蠏 曙..

or襦   螳ル一危          る
------------     ------------------  --------------------------------------------------------------------------------
BFILENAME         BFILE            PL/SQL 觚襦企 企 殊 豺  誤(襦貅危)襯 
COMPARE           ALL LOB           LOB襯 豌  朱襯 觜蟲
FILECLOSE         BFILE            BFILE 襦貅危一 郁屋 殊 .
FILECOLOSEALL     BFILE            BFILE 覈 .
FILEEXISTS        BFILE            襦貅危郁 殊 伎 り 螳襴る 豺 殊 譟伎讌襯 蟆.
FILEGETNAME       BFILE            BFILE 襴 る語 蟆暑襯 覦.
FILEISOPEN        BFILE            殊 企 企 讌襯 蟆.
FILEOPEN          BFILE            れ襦 殊 磯
GETLENGTH         ALL LOB          LOB れ 蠍語企ゼ 覦.
INSTR             ALL LOB          LOB 讌 覓語 ろ碁螻 殊 伎 谿城.
READ              ALL LOB          讌 襷 LOB襯 覯朱 曙 れ碁.
BSUBSTR           ALL LOB          朱誤一 伎 讌 豌  朱 LOB襯 覦.


螳螳  覈 蟆 ....
 BFILE   る  襯 50%蟆 伎  蟆...
譴 BFILE   れ螻 螳給..

- 朱 企 襦貅危郁 ル蠍 伎 BFILENAME襯 伎伎 .
-  殊 企 一一 , 蠏 殊 譟伎讌 FILEEXISTS襯 伎 誤伎 ..
- BFILE 蠍  FILEISOPEN襯 伎 蠏 殊 企 企 讌 誤伎 .
- 碁 殊 語ろる 襾殊 FILEOPEN襦襯 伎 蠏 殊 伎伎 .. 
- READ襦襯 伎 殊 朱  豌企ゼ 讌 覃覈襴襦 曙企れ  給..
- 殊  曙朱 覦 FILECLOSE襦襯 伎伎 殊 給..
- 覈 BFILE  豌襴襯 碁 FILECLOSEALL 襦襯 牛伎 語 ..

讌 襯 牛伎 企..
れ  BFILE 願, BFILE 螻, FILEEXISTS, FILEISOPEN  る襯 蟆螻,
GETFILENAME 伎 襴 螳豌伎 手化襦襯 蟆 ..

DECLARE

/* This Anonymous PL/SQL block will demonstrate how to
   open a BFILE, close the BFILE, and do some error checking
   with FILEEXISTS, FILEISOPEN, and retrieve the Directory
   Object and Path with GETFILENAME  */

     v_BOOKFILE BFILE; -- BFILE to access
     v_DIRNAME VARCHAR2(30); -- Holds Directory Object for FILEGETNAME
     v_LOCATION VARCHAR2(2000); -- Holds filename for FILEGETNAME
     v_FILEISOPEN INTEGER; -- Holds status to check if the file is open
     v_FILEEXISTS INTEGER; -- Holds status if the file actually exists

BEGIN
     v_BOOKFILE := BFILENAME('BOOKS_DIR','BOOKS1.JPG'); -- Create Locator
     v_FILEISOPEN := DBMS_LOB.FILEISOPEN(v_BOOKFILE);-- Check if file open

     v_FILEEXISTS := DBMS_LOB.FILEEXISTS(v_BOOKFILE);

     IF v_FILEEXISTS = 1 THEN
           DBMS_OUTPUT.PUT_LINE('The file exists');
     ELSE
           DBMS_OUTPUT.PUT_LINE('The file cannot be found');
     END IF;

     IF v_FILEISOPEN = 1 THEN  --Determine actions if file is opened 
           DBMS_OUTPUT.PUT_LINE('The file is open');
     ELSE
           DBMS_OUTPUT.PUT_LINE('Opening the file');
           DBMS_LOB.FILEOPEN(v_BOOKFILE);
     END IF;
     DBMS_LOB.FILEGETNAME(v_BOOKFILE,v_DIRNAME,v_LOCATION);
     DBMS_OUTPUT.PUT_LINE('The Directory Object is: ' || v_DIRNAME ||
          ' The File Name is: ' || v_LOCATION);

     DBMS_LOB.FILECLOSE(v_BOOKFILE); -- Close the BFILE

END;
/

れ襦 企語襯 覲企る.....企殊伎誤 襦蠏碁 狩...觜覯..觜煙...
れ   殊 觜蟲螻 殊 蠍語企ゼ 蟲 .

DECLARE

/* The purpose of this anonymous PL/SQl block is to compare
   the contents of three files completely.  The size of the
   files is determined by the GETLENGTH function */

     v_FILE1 BFILE;
     v_FILE2 BFILE;
     v_FILE3 BFILE;
     v_GETLENGTH1 INTEGER; -- Hold length of the file
     v_GETLENGTH2 INTEGER; -- Hold length of the file
     v_GETLENGTH3 INTEGER; -- Hold length of the file
     v_COMPARELENGTH INTEGER; -- Holds smallest of two values
     v_COMPARERESULT INTEGER; -- Hold result of comparing files

BEGIN

-- Create three locators for each of the files to compare

     v_FILE1 := BFILENAME('BOOKS_DIR','BOOKS1.JPG');
     v_FILE2 := BFILENAME('BOOKS_DIR','BOOKS2.JPG');
     v_FILE3 := BFILENAME('BOOKS_DIR','BOOKS5.JPG');

-- Open the files for access

     DBMS_LOB.FILEOPEN(v_FILE1);
     DBMS_LOB.FILEOPEN(v_FILE2);
     DBMS_LOB.FILEOPEN(v_FILE3);

     v_GETLENGTH1 := DBMS_LOB.GETLENGTH(v_FILE1);
     v_GETLENGTH2 := DBMS_LOB.GETLENGTH(v_FILE2);
     v_GETLENGTH3 := DBMS_LOB.GETLENGTH(v_FILE3);

-- Compare 1st and 2nd File
     IF v_GETLENGTH1 < v_GETLENGTH2 THEN
          v_COMPARELENGTH := v_GETLENGTH1;
     ELSE
          v_COMPARELENGTH := v_GETLENGTH2;
     END IF;

     v_COMPARERESULT := DBMS_LOB.COMPARE(v_FILE1,v_FILE2,
          v_COMPARELENGTH,1,1);

     IF v_COMPARERESULT = 0 THEN
          DBMS_OUTPUT.PUT_LINE('Both Files Are Identical');
     ELSE
          DBMS_OUTPUT.PUT_LINE('Both Files Are Different');
     END IF;

-- Compare 1st and 3rd file
     IF v_GETLENGTH1 < v_GETLENGTH3 THEN
          v_COMPARELENGTH := v_GETLENGTH1;
     ELSE
          v_COMPARELENGTH := v_GETLENGTH3;
     END IF;

     v_COMPARERESULT := DBMS_LOB.COMPARE(v_FILE1,v_FILE3,
          v_COMPARELENGTH,1,1);

     IF v_COMPARERESULT = 0 THEN
          DBMS_OUTPUT.PUT_LINE('Both Files Are Identical');
     ELSE
          DBMS_OUTPUT.PUT_LINE('Both Files Are Different');
     END IF;

     DBMS_LOB.FILECLOSEALL;

END;
/

========
襦貅危            
========
襦貅危磯 れ 一危螳 ル 豺襯 螳襴る 蟆朱  螳豌 貉殊 ル..
る狩 襦貅危磯ゼ   碁 譴 企 殊 覦讌襯 危危 蟆 譴..
LOB襯   れ 朱 覲旧  襦 襦貅危郁 焔 れ, 覲 朱覿 覈 一危螳 覲旧螻 ル..
願 襷   り  蠏 豌 伎 襦 朱 覲旧讌 る, LOB 覈 一危襯 
企襴蟆 蠍 覓語 譴...企 LOB襯   LOB 襦貅危一 伎  ..
襷 碁 BFILE 螻 る 襦貅危磯  訖 殊 蠏碁襦 給..
企 LOB襯 企 豢螳  LOB 貉殊 一危襯 麹蟇磯 EMPTY_BLOB EMPTY_CLOB襯 
襦貅危磯ゼ 燕伎 ..BFILE 企 豢螳 BFILENAME  貉殊 襦貅危磯 麹覃 ..
襷讌襷 覓語 企 LOB襷  蟆. るジ 螳 LOB 蠏狩讌 覈詩襦 豌襴 LOB襯 
蠏碁 蟆 譬 螳..

=======================================
企 LOB 蟷 DBMS_LOBれ 蠍
=======================================

or襦   螳ル一危          る
------------     ------------------  --------------------------------------------------------------------------------
COMPARE           ALL LOB           LOB襯 豌  朱襯 觜蟲
GETLENGTH         ALL LOB          LOB れ 蠍語企ゼ 覦.
INSTR             ALL LOB          LOB 讌 覓語 ろ碁螻 殊 伎 谿城.
READ              ALL LOB          讌 襷 LOB襯 覯朱 曙 れ碁.
BSUBSTR           ALL LOB          朱誤一 伎 讌 豌  朱 LOB襯 覦.
APPEND            企 LOB          LOB襯 るジ LOB 豢螳
COPY              企 LOB           LOB襯 るジ 朱 覲旧.
EMPTY_BLOB        BLOB             BLOB 貉殊 襦貅危磯ゼ 
EMPTY_CLOB        CLOB             CLOB 貉殊 襦貅危磯ゼ 
EREASE            企 LOB         企 LOB 豌  朱襯 
TRIM              企 LOB         讌 蠍語企 LOB襯 襯碁.
WRITE             企 LOB         LOB 一危襯 蠍磯.

====

====
SQL> create table lobs(
  2  lob_index integer,
  3  clob_locator clob);

企 焔給.

SQL> INSERT INTO LOBS VALUES(1,'Teach Yourself Oracle8i in 21 Days');

1 螳  襷れ伎給.

SQL> INSERT INTO LOBS VALUES(2,'Oracle Data Warehousing Unleashed');

1 螳  襷れ伎給.

SQL> INSERT INTO LOBS VALUES(3,'Teach Yourself Database Development With Oracle in 21 Days');

1 螳  襷れ伎給.

SQL> INSERT INTO LOBS VALUES(4,'Oracle Unleashed 2E');

1 螳  襷れ伎給.

SQL> INSERT INTO LOBS VALUES(5,EMPTY_CLOB());

1 螳  襷れ伎給.

SQL> INSERT INTO LOBS VALUES(6,EMPTY_CLOB());

1 螳  襷れ伎給.

SQL> select * from lobs;

 LOB_INDEX CLOB_LOCATOR
---------- -------------------------------------------------------------
         1 Teach Yourself Oracle8i in 21 Days
         2 Oracle Data Warehousing Unleashed
         3 Teach Yourself Database Development With Oracle in 21 Days
         4 Oracle Unleashed 2E
         5
         6

6 螳  給.

========================================================
COPY 襦襯 伎 一危螳    覲旧 
========================================================

DECLARE
    Source_Lob   CLOB;
    Dest_Lob   CLOB;
    Copy_Amount INTEGER;
BEGIN
    SELECT CLOB_LOCATOR into Dest_LOB
         FROM LOBS
         WHERE LOB_INDEX = 5 FOR UPDATE; -- FOR UPDATE locks the ROW
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 1;
    Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob);
    DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount);
    COMMIT;
-- Start second copy process
    SELECT CLOB_LOCATOR into Dest_LOB
         FROM LOBS
         WHERE LOB_INDEX = 6 FOR UPDATE;
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 2;
    Copy_Amount := DBMS_LOB.GETLENGTH(Source_Lob);
    DBMS_LOB.COPY(Dest_LOB, Source_LOB,Copy_Amount);
    COMMIT;
END;
/

SQL> SELECT * FROM LOBS;

 LOB_INDEX CLOB_LOCATOR
---------- -----------------------------------------------------------------------
         1 Teach Yourself Oracle8i in 21 Days
         2 Oracle Data Warehousing Unleashed
         3 Teach Yourself Database Development With Oracle in 21 Days
         4 Oracle Unleashed 2E
         5 Teach Yourself Oracle8i in 21 Days
         6 Oracle Data Warehousing Unleashed

6 螳  給.

 結覿 豈  ...
襯 覲願 磯狩企慨覃.....螳  襦襯 企至  蟆瑚   蟆..

Listing 14.7 Appending and Writing to LOBs
DECLARE
/* This appends the contents of Row 1 to the contents of Row 5.
   In addition, it writes text at the end of the values in
   Row 6.  */

    Source_Lob   CLOB;
    Dest_Lob   CLOB;
    Write_Amount INTEGER := 10;
    Writing_Position INTEGER ;
    Buffer VARCHAR2(10) := 'Added Text';
BEGIN
-- Append from Row 1 to Row 5
    SELECT CLOB_LOCATOR into Dest_LOB
         FROM LOBS
         WHERE LOB_INDEX = 5 FOR UPDATE; -- Locks Row for Update
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 1;
    DBMS_LOB.APPEND(Dest_LOB, Source_LOB);
    COMMIT;
-- Write to a LOB
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 6 FOR UPDATE;  -- Locks Row for Update

    Writing_Position := DBMS_LOB.GETLENGTH(Source_Lob) + 1;
    DBMS_LOB.WRITE(Source_LOB,Write_Amount,Writing_Position,Buffer);
    COMMIT;
END;

Listing 14.8 Extracting and Matching Data Inside CLOBs
DECLARE
/* This PL/SQL block finds patterns in a CLOB.  It also
   extracts part of the data from a CLOB with SUBSTR */

    Source_Lob   CLOB;
    v_Pattern VARCHAR2(6) := 'Oracle';
    v_Starting_Location INTEGER := 1;
    v_Nth_Occurrence INTEGER := 1;
    v_Position INTEGER ;
    v_Extract_Amount INTEGER;
    v_Buffer VARCHAR2(100) ;
BEGIN
-- Search for 1st Occurrence of Oracle in Row 5
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 5;
    v_Position := DBMS_LOB.INSTR(Source_LOB,v_Pattern,
         v_Starting_Location,v_Nth_Occurrence);
    DBMS_OUTPUT.PUT_LINE('The first occurrence starts at position: '
         || v_Position);

-- Search for 2nd Occurrence of Oracle in Row 5

    v_Nth_Occurrence := 2;

    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 5;
    v_Position := DBMS_LOB.INSTR(Source_LOB,v_Pattern,
         v_Starting_Location,v_Nth_Occurrence);
    DBMS_OUTPUT.PUT_LINE('The second occurrence starts at position: '
         || v_Position);

-- Extract part of the data from a CLOB
   SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 6;
   v_Buffer := DBMS_LOB.SUBSTR(Source_LOB,11,v_Starting_Location);
   DBMS_OUTPUT.PUT_LINE('The substring extracted is: '  || v_Buffer);

END;

Listing 14.9 Reducing Data in CLOBs
DECLARE
/* This erases the data in Row 6, and trims the data in
   row 5 to one occurrence of the book title. */

    Source_Lob   CLOB;
    Erase_Amount INTEGER;
    Trim_Amount INTEGER;

BEGIN
-- Erase the data completely in Row 6

    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 6 FOR UPDATE; -- Locks Row for Update
    Erase_Amount :=DBMS_LOB.GETLENGTH(Source_LOB);
    DBMS_LOB.ERASE(Source_LOB,Erase_Amount,1);

--Reduce Data in Row 5 to one instance of Book Title
    SELECT CLOB_LOCATOR into Source_LOB
         FROM LOBS
         WHERE LOB_INDEX = 5 FOR UPDATE;

    TRIM_AMOUNT := DBMS_LOB.GETLENGTH(Source_LOB) / 2;
    DBMS_LOB.TRIM(Source_LOB, TRIM_AMOUNT);
    COMMIT;

END;