Oracle如何操作LOB

DBMS_LOB

CREATE TABLE lob_table (
      key_value      INTEGER,
      b_lob          BLOB,
      c_lob          CLOB,
      n_lob          NCLOB,
      f_lob          BFILE);

INSERT INTO a_table values (empty_blob());

INSERT INTO lob_table VALUES
         (21, NULL, NULL, NULL, BFILENAME('IMG', 'image1.gif'));

INSERT INTO lob_table VALUES
         (22, NULL, NULL, NULL, BFILENAME('IMG', 'image2.gif'));

UPDATE lob_table SET f_lob = BFILENAME('IMG', 'image3.gif')
        WHERE key_value = 22;
UPDATE lob_table
   SET f_lob = (SELECT f_lob FROM lob_table WHERE key_value = 22)    
   WHERE key_value = 21;

CREATE DIRECTORY scott_dir AS '/usr/home/scott';

Maximum Number of Open BFILEs
SESSION_MAX_OPEN_FILES=20
INSERT INTO lob_table
   VALUES (1, NULL, 'abcd', NULL, NULL);

COMMIT;

DECLARE
  num_var           INTEGER;
  clob_selected     CLOB;
  clob_updated      CLOB;
  clob_copied       CLOB;
  read_amount       INTEGER;
  read_offset       INTEGER;
  write_amount      INTEGER;
  write_offset      INTEGER;
  buffer            VARCHAR2(20);  
BEGIN

  SELECT c_lob INTO clob_selected FROM lob_table WHERE key_value = 1;

  SELECT c_lob INTO clob_updated FROM lob_table WHERE key_value = 1 FOR UPDATE;
 
  clob_copied := clob_selected;

  read_amount := 10;
  read_offset := 1; 

  dbms_lob.read(clob_selected, read_amount, read_offset, buffer);

--  dbms_output.put_line('clob_selected value: ' || buffer);
insert into temp values(1,buffer);
 
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
--  dbms_output.put_line('clob_copied value: ' || buffer);
insert into temp values(2,buffer);
 
  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
--  dbms_output.put_line('clob_updated value: ' || buffer);
insert into temp values(3,buffer);
 
  -- At time t4:
  write_amount := 3;
  write_offset := 5;
  buffer := 'efg';
  dbms_lob.write(clob_updated, write_amount, write_offset, buffer);
 
  read_amount := 10;
  dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
--  dbms_output.put_line('clob_updated value: ' || buffer);
insert into temp values(4,buffer);
 
  read_amount := 10;
  dbms_lob.read(clob_selected, read_amount, read_offset, buffer);
--  dbms_output.put_line('clob_selected value: ' || buffer);
insert into temp values(5,buffer);
  -- Produces the output 'abcd'
 
  read_amount := 10;
  dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
--  dbms_output.put_line('clob_copied value: ' || buffer);
insert into temp values(6,buffer);
  -- Produces the output 'abcd'
END;
/
OCILobGetLength()    OCILobWrite()

FUNCTION EMPTY_BLOB() RETURN BLOB;
FUNCTION EMPTY_CLOB() RETURN CLOB;
FUNCTION BFILENAME(directory_alias IN VARCHAR2,filename IN VARCHAR2) RETURN BFILE;