2007년 9월 13일 목요일

Oracle DBMS_LOB

Oracle DBMS_LOB

Version 9.2

General Information
Source {ORACLE_HOME}/rdbms/admin/dbmslob.sql
Constants  
Dependencies  
Data Dictionary Objects dbms_lob

lob$
def$_lob

user_lobs
all_lobs
dba_lobs

user_lob_partitions
all_lob_partitions
dba_lob_partitions

user_lob_subpartitions
all_lob_subpartitions
dba_lob_subpartitions

user_part_lobs
all_part_lobs
dba_part_lobs
Object Privileges GRANT execute ON dbms_lob TO <schema_name>;
 
APPEND
appends the contents of a source internal LOB to a destination LOB dbms_lob.append(dest_lob IN OUT NOCOPY BLOB, src_lob IN BLOB);
CREATE OR REPLACE PROCEDURE Example_1a IS

dest_lob BLOB;
src_lob  BLOB;

BEGIN
  -- get the LOB locators
  -- note that the FOR UPDATE clause locks the row
  SELECT b_lob INTO dest_lob
  FROM lob_table
  WHERE key_value = 12
  FOR UPDATE;

  SELECT b_lob INTO src_lob
  FROM lob_table
  WHERE key_value = 21;

  dbms_lob.append(dest_lob, src_lob);
  COMMIT;
END;
dbms_lob.append(
dest_lob IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src_lob  IN             CLOB CHARACTER SET dest_lob%CHARSET);
CREATE OR REPLACE PROCEDURE Example_1b IS

dest_lob, src_lob BLOB;

BEGIN
  -- get the LOB locators
  SELECT b_lob INTO dest_lob
  FROM lob_table
  WHERE key_value = 12
  FOR UPDATE;

  SELECT b_lob INTO src_lob
  FROM lob_table
  WHERE key_value = 12;

  DBMS_LOB.APPEND(dest_lob, src_lob);
  COMMIT;
END;
 
CLOSE
   
 
 
COMPARE
   
 
 
CONVERTOCLOB
   
 
 
COPY
   
 
 
CREATETEMPORARY
Initialize A CLOB Variable dbms_lob.createtemporary(<clob_variable_name>, TRUE);
DECLARE

clobvar CLOB := EMPTY_CLOB;
len     BINARY_INTEGER;
x       VARCHAR2(80);

BEGIN
  dbms_lob.createtemporary(clobvar, TRUE);
  dbms_lob.open(clobvar, dbms_lob.lob_readwrite);
  x := 'before line break' || CHR(10) || 'after line break';
  len := length(x);
  dbms_lob.writeappend(clobvar, len, x);
  dbms_lob.close(clobvar);
END;
/
 
EMPTY_BLOB
Null BLOB EMPTY_BLOB()
INSERT INTO pdm
(dname, sname, fname, iblob)
VALUES
(pdname, psname, pfname, EMPTY_BLOB());
 
ERASE
   
 
 
FILECLOSE
Closes a file opened with dbms_lob.file_open dbms_lob.fileclose(<bfile>);
dbms_lob.fileclose(src_file);
 
FILECLOSEALL
Closes all files opened with dbms_lob.file_open dbms_lob.filecloseall();
dbms_lob.fileclose();
 
FILEEXISTS
Determine whether a file exits

The FILEEXISTS function in the DBMS_LOB package works only with BFILES. In that case, the BFILE pointer must previous be declared and stored in a BFILE column. Without a BFILE pointer, you can not use the FILEEXISTS function.		  
 
 
FILEGETNAME
   
 
 
FILEISOPEN
Determine whether a file is open with the DBMS_LOB package dbms_lob.fileisopen(
 
 
FILEOPEN
Open a file for reading dbms_lob.fileopen(<bfile>, <file_open_mode>);
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
 
FREETEMPORARY
   
dbms_lob.freetemporary(clobvar);
 
GETCHUNKSIZE
   
 
 
GETLENGTH
Gets the length of a file in bytes lgh_file is a variable of type binary_integer <binary_integer> := dbms_lob.getlength(<bfile>);
DECLARE

src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;

BEGIN
  src_file := bfilename('CTEMP', 'myfile.txt');
  lgh_file := dbms_lob.getlength(src_file);
END;
/
 
INSTR
Test for a string in a CLOB  
 
 
ISOPEN
Test for whether a file has been opened with dbms_lob.fileopen  
 
 
ISTEMPORARY
   
 
 
LOADBLOBFROMFILE
   
 
 
LOADCLOBFROMFILE
   
 
 
LOADFROMFILE
Reads the binary file into a BLOB type variable dbms_lob.loadfromfile(<blob_var>, <bfile>, <binary_integer>);
exec dbms_lob.loadfromfile (dst_file, src_file, lgh_file);
 
OPEN
   
 
 
READ
Reads the contents of a file for loading a CLOB type variable dbms_log.read(
 
 
SUBSTR
   
 
 
TRIM
   
 
 
WRITE
   
 
 
WRITEAPPEND
   
 
 
DBMS_LOB Demo
Blob Load Demo /*
define the directory inside Oracle when logged on as SYS
create or replace directory ctemp as 'c:\temp\';

grant read on the directory to the Staging schema
grant read on directory ctemp to staging;
*/

-- the storage table for the image file
CREATE TABLE pdm (
dname   VARCHAR2(30),   -- directory name
sname   VARCHAR2(30),   -- subdirectory name
fname   VARCHAR2(30),   -- file name
iblob   BLOB)           -- image file
pctfree  5
pctused 95
tablespace <tablespace_name>;

-- create the procedure to load the file
CREATE OR REPLACE PROCEDURE load_file (
pdname VARCHAR2,
psname VARCHAR2,
pfname VARCHAR2) IS

src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;

BEGIN
   src_file := bfilename('CTEMP', pfname);

   -- insert a NULL record to lock
   INSERT INTO pdm
   (dname, sname, fname, iblob)
   VALUES
   (pdname, psname, pfname, EMPTY_BLOB())
   RETURNING iblob INTO dst_file;

   -- lock record
   SELECT iblob
   INTO dst_file
   FROM pdm
   WHERE dname = pdname
   AND sname = psname
   AND fname = pfname
   FOR UPDATE;

   -- open the file
   dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

   -- determine length
   lgh_file := dbms_lob.getlength(src_file);

  -- read the file
  dbms_lob.loadfromfile (dst_file, src_file, lgh_file);

  -- update the blob field
  UPDATE pdm
  SET iblob = dst_file
  WHERE dname = pdname
  AND sname = psname
  AND fname = pfname;

  -- close file
  dbms_lob.fileclose(src_file);

END load_file;
/
  How to save a BLOB to a file on disk in PL/SQL
From: Thomas Kyte <tkyte@oracle.com>

Use DBMS_LOB to read from the BLOB

You will need to create an external procedure to take binary data and write it to the operating system, the external procedure can be written in C. If it was CLOB data, you can use UTL_FILE to write it to the OS but UTL_FILE does not support the binary in a BLOB.

There are articles on MetaLink explaining how to do and it has a C program ready for compiling and the External Procedure stuff, i'd advise a visit.

Especially, look for Note:70110.1, Subject: WRITING BLOB/CLOB/BFILE CONTENTS TO A FILE USING EXTERNAL PROCEDURES

Here is the Oracle code cut and pasted from it. The outputstring procedure is the oracle procedure interface to the External procedure.

-------------------------------------

DECLARE

i1    BLOB;
len   NUMBER;
my_vr RAW(10000);
i2    NUMBER;
i3    NUMBER := 10000;

BEGIN
  -- get the blob locator
  SELECT c2
  INTO i1
  FROM lob_tab
  WHERE c1 = 2;

  -- find the length of the blob column
  len := dbms_lob.getlength(i1);
  dbms_output.put_line('Column Length: ' || TO_CHAR(len));

  -- Read 10000 bytes at a time
  i2 := 1;
  IF len < 10000 THEN
    -- If the col length is < 10000
    dbms_lob.read(i1,len,i2,my_vr);

    outputstring('p:\bfiles\ravi.bmp',
    rawtohex(my_vr),'wb',2*len);

    -- You have to convert the data to rawtohex format.
    -- Directly sending the buffer
    -- data will not work
    -- That is the reason why we are sending the length as
    -- the double the size of the data read

    dbms_output.put_line('Read ' || to_char(len) || 'Bytes');
  ELSE
    -- If the col length is > 10000
    dbms_lob.read(i1,i3,i2,my_vr);

    outputstring('p:\bfiles\ravi.bmp',
    rawtohex(my_vr),'wb',2*i3);

    dbms_output.put_line('Read ' || TO_CHAR(i3) || ' Bytes ');
  END IF;

  i2 := i2 + 10000;

  WHILE (i2 < len )
  LOOP
    -- loop till entire data is fetched
    dbms_lob.read(i1,i3,i2,my_vr);

    dbms_output.put_line('Read ' || TO_CHAR(i3+i2-1) ||
    ' Bytes ');

    outputstring('p:\bfiles\ravi.bmp',
    rawtohex(my_vr),'ab',2*i3);

    i2 := i2 + 10000 ;
  END LOOP;
END;
/
  CREATE OR REPLACE PROCEDURE read_file IS

src_file BFILE := bfilename('DOCUMENT_DIR', 'image.gif');
dst_file BLOB;
lgh_file BINARY_INTEGER;

BEGIN
  -- lock record
  SELECT bin_data
  INTO dst_file
  FROM db_image
  FOR update;

  -- open the file
  dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

  -- determine length
  lgh_file := dbms_lob.getlength(src_file);

  -- read the file
  dbms_lob.loadfromfile(dst_file, src_file, lgh_file);

  -- update the blob field
  UPDATE db_image
  SET bin_data = dst_file;
  COMMIT;

  -- close file
  dbms_lob.fileclose(src_file);

EXCEPTION
  WHEN access_error THEN

  WHEN invalid_argval THEN

  WHEN invalid_directory THEN

  WHEN no_data_found THEN

  WHEN noexist_directory THEN

  WHEN nopriv_directory THEN

  WHEN open_toomany THEN

  WHEN operation_failed THEN

  WHEN unopened_file THEN

  WHEN others THEN

END read_file;
/
Replaces All Code Occurences Of A String With Another Within A CLOB -- 1) clob src - the CLOB source to be replaced.
-- 2) replace str - the string to be replaced.
-- 3) replace with - the replacement string.

FUNCTION replaceClob (
srcClob IN CLOB,
replaceStr IN VARCHAR2,
replaceWith IN VARCHAR2)
RETURN CLOB IS

vBuffer    VARCHAR2 (32767);
l_amount   BINARY_INTEGER := 32767;
l_pos      PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
newClob    CLOB := EMPTY_CLOB;
   
BEGIN
  -- initalize the new clob
  dbms_lob.createtemporary(newClob,TRUE);
   
  l_clob_len := dbms_lob.getlength(srcClob);

  WHILE l_pos < l_clob_len
  LOOP
    dbms_lob.read(srcClob, l_amount, l_pos, vBuffer);

    IF vBuffer IS NOT NULL THEN
      -- replace the text
      vBuffer := replace(vBuffer, replaceStr, replaceWith);
      -- write it to the new clob
      dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer);
    END IF;
    l_pos := l_pos + l_amount; 
  END LOOP;
   
  RETURN newClob;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
/
작성자: 시간:
라벨:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)