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 9363 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 9363
MANIPULATING LARGE OBJECTS USING DBMS_LOB PACKAGE (2)
작성자
정재익(advance)
작성일
2002-01-07 23:51
조회수
7,874
첨부파일: lob-insert.txt (28,030bytes)

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.

[Top]
No.
제목
작성자
작성일
조회
9754레드햇7.x 혹은 와우7.1 에서의 demo_proc.mk 컴파일 관련 힌트(?) [7]
송호진
2002-02-01
7061
9442Simple Guide : Hierarchical Queries [5]
최공훈
2002-01-11
6504
9414인덱스 분포도에따라 액세스 플랜을 달리하는 SQL문장 [2]
서민구
2002-01-10
5778
9363MANIPULATING LARGE OBJECTS USING DBMS_LOB PACKAGE (2)
정재익
2002-01-07
7874
9362MANIPULATING LARGE OBJECTS USING DBMS_LOB PACKAGE (1)
정재익
2002-01-07
6944
9357RedHat 6.0 에서 PHP3 와 오라클 8.1.5 의 연동
정재익
2002-01-07
4665
9356Oracle 8i 에서 Pro*C 를 이용한 웹 CGI 프로그래밍
정재익
2002-01-07
5185
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다