database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 9273 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 9273
MANIPULATING LARGE OBJECTS USING BMS_LOB PACKAGE
작성자
정재익(advance)
작성일
2002-01-05 17:51
조회수
6,028

MANIPULATING LARGE OBJECTS USING BMS_LOB PACKAGE

Overview
--------

This bulletin describes the DBMS_LOB package which provides functions 
and procedures allowing the manipulation of specific parts, as well as 
complete internal LOBs and read-only operations on BFILEs.


Introduction
------------

Databases have long been used to store large objects.  Oracle 8 significantly
enhances this feature by introducing the Large Object(LOB) datatypes.  LOBs 
can be broadly categorized as Internal LOBs and External LOBs.  

Internal LOBS can store binary data (BLOBs), single-byte character data
(CLOBs), or multi-byte character data (NCLOBs).  Internal LOBs are stored 
in the database tablespaces in a way that optimizes space and provides 
efficient access.  Internal LOBs participate in the transaction model of 
the server. 

External LOBs (henceforth referred to as BFILES), are stored in operating 
system files outside the database tablespaces.  These LOBs do not participate 
in transactions.

Data stored in a LOB is called the LOB's value.   To the Oracle8 Server, a 
LOB's value is unstructured and cannot be queried against.  LOBs can be stored 
along with other row data or separate from row data.  Regardless of how the 
data is stored, every LOB has a locator associated with it which can be viewed 
as a handle or pointer to the actual location.  Selecting a LOB returns the LOB
locator instead of the LOB value.  Two new special functions in Oracle8 SQL 
DML, EMPTY_BLOB() and EMPTY_CLOB(), allow initialization of NULL or non-NULL 
LOB columns to empty.

PL/SQL provides a mechanism to manipulate these LOBs via the DBMS_LOB package.
The DBMS_LOB package provides functions and procedures which allow manipulation
of specific parts as well as complete internal LOBs and read-only operations on
BFILEs.  All DBMS_LOB routines work based on LOB locators.  This bulletin
assumes that readers have some experience working with LOBs, i.e. creating
tables with LOB columns, initializing LOBs either EMPTY_CLOB() or EMPTY_BLOB(),
and using the constructors to initialize the LOBs if the tables are defined on 
object types.


Cautionary Note
---------------

The examples presented in this note make use of DBMS_OUTPUT and/or UTL_FILE
packages.  Both packages have line and buffer limitations which result
in an exception being raised if the size of the data output exceeds those
limitations.    


DDL for LOBs
------------

The LOB definition can involve CREATE TYPE and CREATE TABLE statements.  For
example, the following statement specifies a CLOB, BLOB, and BFILE within a
datatype lob_type:

   CREATE TYPE lob_type AS object (
      lob_id       NUMBER(3),
      video_clip   BLOB,
      document     CLOB ,
      some_file    BFILE );

This DDL creates an object table of lob_type where each row is an instance of
lob_type data:

   CREATE TABLE lob_store OF lob_type;

The following statement stores LOBs in a relational table, as opposed to an
object table as in the preceding statement:

   CREATE TABLE lob_store
   (  lob_id       NUMBER(3),
      video_clip   BLOB DEFAULT empty_blob(),
      document     CLOB DEFAULT NULL,
      some_file    BFILE DEFAULT NULL);


DML for LOBs
------------

The PL/SQL block below populates the table LOB_STORE with 10 LOB IDs which
defaults to initializing the video_clip to empty, and the document and some_file
to null.

   DECLARE
     loop_count INTEGER;
   BEGIN
     loop_count := 1;
     WHILE loop_count <= 10 LOOP
       INSERT INTO lob_store (lob_id) VALUES (loop_count);
       loop_count := loop_count + 1;
     END LOOP;
     UPDATE lob_store SET video_clip=utl_raw.cast_to_raw('0123456789'),
       document = 'abcdefgh' where lob_id=2;

     UPDATE lob_store SET video_clip=utl_raw.cast_to_raw('7777777'), 
       document = 'ijklmn' where lob_id=3;

     UPDATE lob_store SET video_clip=empty_blob(),
       document = empty_clob() where lob_id=4;
   END;
   /

The examples listed in this bulletin are based on the relational table 
LOB_STORE described in the DDL section of the bulletin and on the data 
inserted in the table using the PL/SQL block above.


Subprograms in the DBMS_LOB Package
-----------------------------------

The more commonly used procedures and functions contained in the DBMS_LOB
package can be broadly categorized as follows:

a)  The routines that can modify BLOB, CLOB, and NCLOB values are:

     APPEND()   - append the contents of the source LOB to the destination
                  LOB
     COPY()     - copy all or part of the source LOB to the destination LOB
     ERASE()    - erase all or part of a LOB
     LOADFROMFILE() - load BFILE data into an internal LOB
     TRIM()     - trim the LOB value to the specified shorter length
     WRITE()    - write data to the LOB from a specified offset

b)  The routines that read or examine LOB values are:

     COMPARE()   - comapre two entire or part of two lobs
     GETLENGTH() - get the length of the LOB value
     INSTR()     - return the matching position of the nth occurrence of the
                        pattern in the LOB
     READ()      - read data from the LOB starting at the specified offset
     SUBSTR()    - return part of the LOB value starting at the specified 
                        offset

c)  The read-only routines specific to BFILEs are:

     FILECLOSE()   - close the file
     FILECLOSEALL()- close all previously opened files
     FILEEXISTS()  - check if the file exists on the server
     FILEGETNAME() - get the directory alias and file name
     FILEISOPEN()  - check if the file was opened using the input BFILE
                        locators
     FILEOPEN()    - open a file

The remainder of this bulletin explains each of the functions/procedures in 
the DBMS_LOB package and contains an example demonstrating how these
subprograms can be used to achieve the desired result.


Manipulating Internal LOBS (BLOB, CLOB, NCLOB) with DBMS_LOB Package
--------------------------------------------------------------------

I)  Finding the length of the variable/column which is declared of LOB 
    datatype.

   Syntax

   FUNCTION GETLENGTH (
     lob_loc    IN  BLOB)
   RETURN INTEGER;

   FUNCTION GETLENGTH (
     lob_loc    IN  CLOB   CHARACTER SET ANY_CS)
   RETURN INTEGER;

   FUNCTION GETLENGTH (
     lob_loc    IN  BFILE)
   RETURN INTEGER;

   Example:

   SQL> select dbms_lob.getlength(document) from lob_store where lob_id = 1;


DBMS_LOB.GETLENGTH(DOCUMENT)
----------------------------

The length is not defined because the document was initialized to null by 
default.  The document column needs to be initialized using the built in 
special function EMPTY_CLOB() to empty.

   SQL> update lob_store set document = empty_clob() where lob_id = 1;

   1 row updated.

   SQL> select dbms_lob.getlength(document) from lob_store where lob_id = 1;

   DBMS_LOB.GETLENGTH(DOCUMENT)
   ----------------------------
                              0

II) Populating an internal LOB.  The DBMS_LOB.WRITE procedure can be used to
    insert values into a LOB column of a table.  WRITE() overwrites any data 
    that already exists in the LOB at the offset, for the length specified.

   Syntax

   PROCEDURE WRITE (
     lob_loc  IN OUT  BLOB,
     amount   IN      BINARY_INTEGER,
     offset   IN      INTEGER,
     buffer   IN      RAW);

   PROCEDURE WRITE (
     lob_loc  IN OUT  CLOB   CHARACTER SET ANY_CS,
     amount   IN      BINARY_INTEGER,
     offset   IN      INTEGER,
     buffer   IN      VARCHAR2 CHARACTER SET lob_loc%CHARSET);


   Example:

   This PL/SQL code demonstrates how a LOB column can be populated.

   DECLARE
     lobloc CLOB;
     buffer VARCHAR2(32000);
     amount NUMBER := 20;
     offset NUMBER := 1;
   BEGIN
     --Initialize buffer with data to be inserted
     buffer := 'abcdefghijklmnopqrstuvwxyz';
     amount := length(buffer);
     dbms_output.put_line(buffer);
     dbms_output.put_line(to_char(amount));
     SELECT document INTO lobloc  -- get LOB handle
       FROM lob_store
       WHERE lob_id = 1 FOR UPDATE;
       dbms_lob.write(lobloc,amount,1,buffer);
     COMMIT;
   END;
   /

   SQL> SELECT dbms_lob.getlength(document) FROM lob_store WHERE lob_id = 1;


    DBMS_LOB.GETLENGTH(DOCUMENT)
    ----------------------------
                              26

III) Reading from a LOB column can be achieved by using the DBMS_LOB.READ
     procedure.  This procedure allows piecewise retrieval as well as 
     retrieval of the entire LOB into a buffer.

   Syntax

   PROCEDURE READ (
     lob_loc   IN    BLOB,
     amount    IN OUT  BINARY_INTEGER,
     offset    IN    INTEGER,
     buffer    OUT   RAW);

   PROCEDURE READ (
     lob_loc   IN    CLOB     CHARACTER SET ANY_CS,
     amount    IN OUT  BINARY_INTEGER,
     offset    IN    INTEGER,
     buffer    OUT   VARCHAR2 CHARACTER SET lob_loc%CHARSET);

   PROCEDURE READ (
     lob_loc   IN    BFILE,
     amount    IN OUT  BINARY_INTEGER,
     offset    IN    INTEGER,
     buffer    OUT   RAW);

   Example:  This example shows how a piece wise fetch can be accomplished
             using DBMS_LOB.READ procedure.

   DECLARE
     lobloc CLOB;
     buffer VARCHAR2(32000);
     amount NUMBER := 10;
     amount_in_buffer NUMBER;
     offset NUMBER := 4;
   BEGIN
     --Initialize buffer with data to be inserted
     SELECT document INTO lobloc  -- get LOB handle
       FROM lob_store
       WHERE lob_id = 2;
     dbms_lob.read(lobloc,amount,offset,buffer);
     --using length built-in function to find the length of the buffer
     amount_in_buffer := length(buffer);
     dbms_output.put_line(buffer);
     dbms_output.put_line(to_char(amount_in_buffer));
     COMMIT;
   END;
   /

   defgh
   5

   PL/SQL procedure successfully completed.

IV)  The overloaded APPEND() procedure appends the complete source LOB
     to the destination LOB.

Syntax

PROCEDURE APPEND (dest_lob IN OUT  BLOB,
                  src_lob  IN      BLOB);

PROCEDURE APPEND (dest_lob IN OUT  CLOB  CHARACTER SET ANY_CS,
                  src_lob  IN      CLOB  CHARACTER SET
                  dest_lob%CHARSET);
Example:
CREATE OR REPLACE PROCEDURE tst_append IS
  dblob BLOB;
  sblob BLOB;
  dclob CLOB;
  sclob CLOB;
  spos  NUMBER;
  amt   BINARY_INTEGER;
  bufb  RAW(20);
  bufc  VARCHAR2(20);
BEGIN
  dbms_output.put_line('--------------- APPEND Begin ---------------');

  /* append the BLOBs in the lob_store table */
  SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2 FOR UPDATE;
  SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 3;
  dbms_lob.append(dblob, sblob);

  /* append the CLOBs in the lob_store table */
  SELECT document INTO dclob FROM lob_store WHERE lob_id = 2 FOR UPDATE;
  SELECT document INTO sclob FROM lob_store WHERE lob_id = 3;
  dbms_lob.append(dclob, sclob);

  /* end Xn */
  COMMIT;

  /* testing APPEND operation */
  amt := 20; spos := 1;
  SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 2;
  dbms_lob.read(sblob, amt, spos, bufb);
  dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));

  SELECT document INTO sclob FROM lob_store WHERE lob_id = 2;
  dbms_lob.read(sclob, amt, spos, bufc);
  dbms_output.put_line('Result CLOB: ' || bufc);
  dbms_output.put_line('--------------- APPEND End   ---------------');
  dbms_output.put_line(' ');
END tst_append;
/

SQL> execute tst_append;
--------------- APPEND Begin ---------------
Result BLOB: 01234567897777777
Result CLOB: abcdefghijklmn
--------------- APPEND End   ---------------

PL/SQL procedure successfully completed.

V) The ERASE() procedure allows one to erase a part or the entire LOB 
   depending on the amount and offset parameters passed to the procedure.  
   The actual number of bytes or characters erased can differ from the 
   number specified in the amount parameter if the end of the LOB parameter 
   has been reached.  Erased characters are replaced with zero byte filters 
   for BLOBs and spaces for CLOBs.

CREATE OR REPLACE PROCEDURE tst_erase IS
  dblob BLOB;
  dclob CLOB;
  amt   NUMBER;
  pos   NUMBER;
  bufb  RAW(10);
  bufc  VARCHAR2(10);
BEGIN
  dbms_output.put_line('--------------- ERASE Begin ---------------');

  -- Case 1: erase 5 bytes from the middle of the LOB
  --  amt := 5; pos := 3;
  -- Case 2: trim 2 bytes from the end of the LOB
    amt := 2; pos := 6;
  -- Case 3: erase 5 bytes from the beginning of the LOB
  --  amt := 5; pos := 1;
  -- Case 4: erase from an empty LOB

  /* test ERASE for BLOBs */
  SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2 FOR UPDATE;
  dbms_lob.erase(dblob, amt, pos);

  /* test ERASE for CLOBs */
  SELECT document INTO dclob FROM lob_store WHERE lob_id = 2 FOR UPDATE;
  dbms_lob.erase(dclob, amt, pos);

  /* end the transaction */
  COMMIT;

  /* checking the ERASE operation */
  amt := 10; pos := 1;
  SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2;
  dbms_lob.read(dblob, amt, pos, bufb);
  dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));

  SELECT document INTO dclob FROM lob_store WHERE lob_id = 2;
  dbms_lob.read(dclob, amt, pos, bufc);
  dbms_output.put_line('Result CLOB: ' || bufc);

  dbms_output.put_line('--------------- ERASE End   ---------------');
  dbms_output.put_line(' ');
end tst_erase;
/

SQL> execute tst_erase
--------------- ERASE Begin ---------------
Result BLOB: 01234
Result CLOB: abcde  h
--------------- ERASE End   ---------------

When using DBMS_LOB.ERASE on BLOBs, the second parameter to the DBMS_LOB.ERASE 
procedure is not taken into consideration.  It from the offset to the end of 
the data.  This is a known bug (BUG:554028).

VI) The COPY procedure allows one to copy part or all of a source internal
    LOB into a destination internal LOB.  The offsets for both the source and
    destination LOBs can be specified.

Syntax

PROCEDURE COPY (
  dest_lob    IN OUT BLOB,
  src_lob     IN     BLOB,
  amount      IN     INTEGER,
  dest_offset IN     INTEGER := 1,
  src_offset  IN     INTEGER := 1);



PROCEDURE COPY (
  dest_lob    IN OUT CLOB  CHARACTER SET ANY_CS,
  src_lob     IN     CLOB  CHARACTER SET dest_lob%CHARSET,
  amount      IN     INTEGER,
  dest_offset IN     INTEGER := 1,
  src_offset  IN     INTEGER := 1);

Example: This example demonstrates how part of the LOBs in row with
         lob_id 2 can be copied into row with lob_id 4;

CREATE OR REPLACE PROCEDURE tst_copy IS
  dblob BLOB;
  sblob BLOB;
  dclob CLOB;
  sclob CLOB;
  amt   NUMBER;
  dpos  NUMBER;
  spos  NUMBER;
  bufb  RAW(30);
  bufc  VARCHAR2(30);
BEGIN
  dbms_output.put_line('--------------- COPY Begin ---------------');
  dbms_output.put_line(' ');

  -- Case 1: copy row 3 LOBs to row 4 LOBs
  amt := 5; dpos := 1; spos := 3;

  -- test COPY for BLOBs
  SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 4 FOR UPDATE;
  SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 2;
  dbms_lob.copy(dblob, sblob, amt, dpos, spos);
  COMMIT;
  -- test COPY for CLOBs
  SELECT document INTO dclob FROM lob_store WHERE lob_id = 4 FOR UPDATE;
  SELECT document INTO sclob FROM lob_store WHERE lob_id = 2;
  dbms_lob.copy(dclob, sclob, amt, dpos, spos);
  COMMIT;
  -- check  of COPY operation

  SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 4;
  dbms_lob.read(sblob, amt, dpos, bufb);
  dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(bufb));

  SELECT document INTO sclob FROM lob_store WHERE lob_id = 4;
  dbms_lob.read(sclob, amt, dpos, bufc);
  dbms_output.put_line('Result CLOB: ' || bufc);

  dbms_output.put_line('--------------- COPY End   ---------------');
  dbms_output.put_line(' ');
EXCEPTION
  WHEN NO_DATA_FOUND
    THEN dbms_output.put_line('COPY: no_data_found error');
END tst_copy;
/

SQL> exec tst_copy
--------------- COPY Begin ---------------
Result BLOB: 23456
Result CLOB: cdefg
--------------- COPY End   ---------------

PL/SQL procedure successfully completed.

VII)  The COMPARE() function allows comparison between two entire LOBs
      or parts of the LOBs.  COMPARE() returns a zero if the data exactly 
      matches over the specified range.  Otherwise, it returns a non-zero 
      value.

Syntax

FUNCTION COMPARE (
    lob_1            IN BLOB,
    lob_2            IN BLOB,
    amount           IN INTEGER := 4294967295,
    offset_1         IN INTEGER := 1,
    offset_2         IN INTEGER := 1)
RETURN INTEGER;

FUNCTION COMPARE (
    lob_1            IN CLOB  CHARACTER SET ANY_CS,
    lob_2            IN CLOB  CHARACTER SET lob_1%CHARSET,
    amount           IN INTEGER := 4294967295,
    offset_1         IN INTEGER := 1,
    offset_2         IN INTEGER := 1)
RETURN INTEGER;

Example:  If this procedure tst_compare is run immediately after the tst_copy
          procedure, the result is similar to that shown in the output of the
          execution  below.

CREATE OR REPLACE PROCEDURE tst_compare IS
  dblob BLOB;
  sblob BLOB;
  dclob CLOB;
  sclob CLOB;
  amt   NUMBER;
  dpos  NUMBER;
  spos  NUMBER;
  ret   INTEGER;
BEGIN
  dbms_output.put_line('--------------- COMPARE Begin ---------------');
  dpos := 3; spos := 1;
  amt := 5;

  /* test COMPARE for BLOBs */
  SELECT video_clip INTO dblob FROM lob_store WHERE lob_id = 2;
  SELECT video_clip INTO sblob FROM lob_store WHERE lob_id = 4;
  ret := -1;
  ret := dbms_lob.compare(dblob, sblob, amt, dpos, spos);
  dbms_output.put_line('Return value for BLOB: ' || ret);

  /* testing the validity of COMPARE for CLOBs */
  SELECT document INTO dclob FROM lob_store WHERE lob_id = 2;
  SELECT document INTO sclob FROM lob_store WHERE lob_id = 4;
  ret := -1;
  ret := dbms_lob.compare(dclob, sclob, amt, dpos, spos);
  dbms_output.put_line('Return value for CLOB: ' || ret);

  dbms_output.put_line('--------------- COMPARE End   ---------------');
  dbms_output.put_line(' ');
END tst_compare;

SQL> exec tst_compare;
--------------- COMPARE Begin ---------------
Return value for BLOB: 1
Return value for CLOB: 1
--------------- COMPARE End   ---------------

PL/SQL procedure successfully completed.


VIII) The TRIM() procedure can be used to trim the value of an internal LOB 
      to a length specified in the newlen parameter.

Syntax

FUNCTION TRIM (
    lob_loc        IN    BLOB,
    newlen        IN    INTEGER);


FUNCTION TRIM (
    lob_loc        IN    CLOB,
    newlen        IN    INTEGER);

Example:  The following example shows how the TRIM() procedure can be used to
          truncate the value stored in a CLOB to 5.

CREATE OR REPLACE PROCEDURE tst_trim IS
  dclob CLOB;
  nlen  NUMBER;
  amt   BINARY_INTEGER;
  bufc  VARCHAR2(5);
BEGIN
  dbms_output.put_line('--------------- TRIM Begin ---------------');
  nlen := 5;

  /* test TRIM for CLOBs */
  SELECT document INTO dclob FROM lob_store WHERE lob_id = 2 FOR UPDATE;
  dbms_lob.trim(dclob, nlen);
  COMMIT;

  /* check  of TRIM operation */
  SELECT document INTO dclob FROM lob_store WHERE lob_id = 2;
  bufc := '';
  dbms_lob.read(dclob, nlen, 1, bufc);
  dbms_output.put_line('Result CLOB: ' || bufc);

  dbms_output.put_line('--------------- TRIM End   ---------------');
  dbms_output.put_line(' ');
END tst_trim;
/

SQL> exec tst_trim
--------------- TRIM Begin ---------------
Result CLOB: abcde
--------------- TRIM End   ---------------

PL/SQL procedure successfully completed.


Manipulating External LOBS (BFILES) with DBMS_LOB Package
---------------------------------------------------------

A BFILE column or attribute for a particular row or user-defined type stores 
a BFILE locator which can be considered as an opaque pointer to a file on the
server's file system.  The locator contains the directory alias, the filename,
and some state information.  A new DIRECTORY database item enables 
administering access and usage of BFILES.  A DIRECTORY item specifies an alias 
for a directory on the server's file system.

The schema creating a directory object needs special privileges.  The SQL
statement below grants to user naveen the privilege to create directory 
objects.  

   grant create any directory to naveen;

Creating a directory object which maps an operating system path where the flat
file is stored.  The directory object name is used to map an operating system
file directory to a BFILE Lob Locator.

   create or replace directory "LOBMANIP" as '/home/usupport/npasumar/bulletin/';

Loading a lob locator of a BFILE using the DBMS_LOB.LOADFROMFILE

The following four steps are involved in loading a BFILE locator to map OS 
file.  Note that ORACLE has only read-only access to BFILEs.

1)  Check if the external file was opened by using the specified FILE locator
    using the DBMS_LOB.FILEISOPEN() function.

Syntax
FUNCTION FILEISOPEN (
      file_loc     IN    BFILE)
RETURN INTEGER;

2)  Open the external file using the DBMS_LOB.FILEOPEN() procedure.

Syntax
PROCEDURE FILEOPEN (
      file_loc   IN OUT  BFILE,
      open_mode  IN      BINARY_INTEGER := file_readonly);

3)  Copy a part or whole of the external LOB into a Internal LOB using
    the DBMS_LOB.LOADFROMFILE.

Syntax
PROCEDURE loadfromfile (
   dest_lob    IN OUT BLOB,
   src_file    IN     BFILE,
   amount      IN     INTEGER,
   dest_offset IN     INTEGER := 1,
   src_offset  IN     INTEGER  := 1);

4)  Close the BFILE that was opened via the DBMS_LOB.FILEOPEN() procedure.

Syntax
PROCEDURE FILECLOSE (
    file_loc IN OUT BFILE);

Example: This program reads data from an operating system file and reads it
         into a BLOB.  It assumes that the directory object LOBMANIP has been 
         created and the directory to which it maps contains a file 
         'sample10.pc'.

CREATE OR REPLACE PROCEDURE load_lob IS
  temp_blob   BLOB;
  file_on_os    BFILE := bfilename('LOBMANIP', 'append.sql');
  ignore INTEGER;
BEGIN
  ignore := dbms_lob.fileexists(file_on_os);
  IF ignore=1 THEN
    ignore := dbms_lob.fileisopen(file_on_os);
    IF (ignore=1) THEN
      null;
    ELSE
      dbms_lob.fileopen(file_on_os, dbms_lob.file_readonly);
    END IF;
    SELECT video_clip INTO temp_blob FROM lob_store WHERE lob_id = 5 FOR UPDATE;
    dbms_output.put_line('External file size is: ' ||
                                        dbms_lob.getlength(file_on_os));
    dbms_lob.loadfromfile(temp_blob,file_on_os, dbms_lob.getlength(file_on_os));
    dbms_lob.fileclose(file_on_os);
    dbms_output.put_line('Internal BLOB size is: ' ||
                        dbms_lob.getlength(temp_blob));
  ELSE
     dbms_output.put_line('File does not exist');
  END IF;

  COMMIT;
END;

SQL> exec load_lob
External file size is: 1230
Internal BLOB size is: 1230

PL/SQL procedure successfully completed.


The next example demonstrates how the data stored in an internal LOB can be
written to a flat file using a combination of the built-in packages DBMS_LOB
and UTL_FILE.

CREATE OR REPLACE PROCEDURE retrieve_lob IS
    temp_blob           BLOB;
    data_buffer         RAW (1);
    temp_buffer         VARCHAR2(1);
    amount              BINARY_INTEGER := 1;
    position            INTEGER := 1;
    filehandle          utl_file.file_type;
    error_number        NUMBER;
    error_message       VARCHAR2(100);
    length_count        INTEGER;

BEGIN
    SELECT video_clip INTO temp_blob FROM lob_store WHERE lob_id = 1;
    length_count := dbms_lob.getlength(temp_blob);
    dbms_output.put_line('Internal LOB size is:  ' || length_count);
    filehandle := utl_file.fopen('/home/usupport/npasumar/bulletin',
                                 'lob_flat.out','W');
    WHILE length_count <> 0 LOOP
       dbms_lob.read (temp_blob, amount, position, data_buffer);
       temp_buffer := utl_raw.cast_to_varchar2(data_buffer);
       utl_file.put (filehandle, temp_buffer);
       position := position + 1;
       length_count := length_count - 1;
       data_buffer := null;
    END LOOP;
    dbms_output.put_line('Exit the loop');
    utl_file.fclose(filehandle);
    dbms_output.put_line('Close the file');
EXCEPTION
    WHEN OTHERS THEN
       BEGIN
          error_number := sqlcode;
          error_message := substr(sqlerrm ,1 ,100);
          dbms_output.put_line('Error #: ' || error_number);
          dbms_output.put_line('Error Message: ' || error_message);
          utl_file.fclose_all;
       END;
END;

   Note: The utl_file package is only suitable for handling TEXT data.


SQL> exec retrieve_lob
Internal LOB size is:  1230
Exit the loop
Close the file

PL/SQL procedure successfully completed.


Comparison Between OCI Functions and DBMS_LOB PACKAGES
------------------------------------------------------

The LOBs can also be manipulated using OCI.  Here is a comparison of the
DBMS_LOB package procedures and the OCI functions.

     OCI (ociap.h)                         DBMS_LOB (dbmslob.sql)

 N/A                            DBMS_LOB.COMPARE()
 N/A                            DBMS_LOB.INSTR()
 N/A                            DBMS_LOB.SUBSTR()
 OCILobAppend                   DBMS_LOB.APPEND()
 OCILobAssign                   N/A [use Pl/SQL assign operator]
 OCILobCharSetForm              N/A
 OCILobCharSetId                N/A
 OCILobCopy                     DBMS_LOB.COPY()
 OCILobDisableBuffering         N/A
 OCILobEnableBuffering          N/A
 OCILobErase                    DBMS_LOB.ERASE()
 OCILobFileClose                DBMS_LOB.FILECLOSE()
 OCILobFileCloseAll             DBMS_LOB.FILECLOSEALL()
 OCILobFileExists               DBMS_LOB.FILEEXISTS()
 OCILobFileGetName              DBMS_LOB.FILEGETNAME()
 OCILobFileIsOpen               DBMS_LOB.FILEISOPEN()
 OCILobFileOpen                 DBMS_LOB.FILEOPEN()
 OCILobFileSetName              N/A (use BFILENAME operator)
 OCILobFlushBuffer              N/A
 OCILobGetLength                DBMS_LOB.GETLENGTH()
 OCILobIsEqual                  N/A [use Pl/SQL equal operator]
 OCILobLoadFromFile             DBMS_LOB.LOADFROMFILE()
 OCILobLocatorIsInit            N/A [always initialize]
 OCILobRead                     DBMS_LOB.READ()
 OCILobTrim                     DBMS_LOB.TRIM()
 OCILobWrite                    DBMS_LOB.WRITE()


Frequently Asked Questions
--------------------------

1. What are the differences between LONG and LOB?

LONG                            LOB
----                            ---
Single column per table         Multiple columns per table
Up to 2 Gigabytes               Up to 4 Gigabytes
SELECT returns data             SELECT returns locator
Data stored in-line             Data stored in-line or out-of-line
No object type support          Support object types
Sequential access of data       Random access of data

2. When can LOBs be stored in-line?

When LOB columns are created as a part of a table, they are created by default
in in-line mode.  In this mode, if the LOB (data+locator) < 4000 bytes, then
the LOB is stored inline.  If the LOB (data+locator) > 4000 bytes, then it is
stored out-of-line.  Inline storage of LOBs smaller than 4K can be disabled
(disable storage in row).  In this case, only the locator is stored in the row
and the data is stored in the LOB segment.


Related Article
---------------

[NOTE:66312.1]  How to Display Images Stored in BFILE Columns
.

 from oracle
[Top]
No.
제목
작성자
작성일
조회
9279유용한 오라클 tuning scripts (2)
정재익
2002-01-05
8968
9278유용한 오라클 tuning scripts (1)
정재익
2002-01-05
11146
9276몇가지 유용한 SQL scripts
정재익
2002-01-05
10821
9273MANIPULATING LARGE OBJECTS USING BMS_LOB PACKAGE
정재익
2002-01-05
6028
9272ORACLE DATABASE 재설치하기
정재익
2002-01-05
6507
9271REBUILD 할 INDEX 와 BITMAP INDEX 로 만들어야 하는 인덱스를 조사하기 [1]
정재익
2002-01-05
6025
9270PARALLEL QUERY OPTION 사용 및 확인
정재익
2002-01-05
6834
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.057초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다