【dbms包】DBMS_LOB

DBMS_LOB

Datatypes:

BLOB                      Source or destination binary LOB.

RAW                                       Source or destination RAW buffer (used with BLOB).

CLOB                      Source or destination character LOB (including NCLOB).

VARCHAR2           Source or destination character buffer (used with CLOB and NCLOB).

INTEGER                               Specifies the size of a buffer or LOB, the offset into a LOB, or the amount to access.

BFILE                      Large, binary object stored outside the database.

 

dbms_lob.append

declare

  --type tp_row is table of t_test_2%rowtype index by binary_integer;

  --v_tp_row tp_row; 

  v_blob1 blob;

  v_blob2 blob;

begin

  select lobs into v_blob1 from t_test_2 where rownum = 1 for update;

  select lobs into v_blob2 from t_test_2 where rownum = 1;

  dbms_lob.append(v_blob1,v_blob2);

  commit;

end;

 

dbms_lob.copy

declare

  v_blob1 blob;

  v_blob2 blob;

  copy_amount integer;

 

begin

  select lobs into v_blob1 from t_test_2 where id = 1 for update;

  insert into t_test_2 values (2,empty_blob());

  select lobs into v_blob2 from t_test_2 where id = 2 for update;

 

  copy_amount := dbms_lob.getlength(v_blob1);

  dbms_lob.copy(v_blob2,v_blob1,copy_amount);

 

  --commit;

 

end;

 

fileopen&fileclose

begin

   dbms_lob.filecloseall();

end ;

 

DECLARE

    BF BFILE;

BEGIN

    BF := BFILENAME('TESTDIR', 'asd.txt');

 

    DBMS_LOB.FILEOPEN(BF, OPEN_MODE => 0);

    DBMS_LOB.FILECLOSE(BF);

END;

 

 

Loadblobfromfile

Update blob:

DECLARE

    V_BLOB      BLOB;

    BF          BFILE;

    SRC_OFFSET  INTEGER := 1;

    DEST_OFFSET INTEGER := 1;

 

BEGIN

    BF := BFILENAME('TESTDIR', 'asd.txt');

    DBMS_LOB.FILEOPEN(BF, OPEN_MODE => 0);

    SELECT LOBS INTO V_BLOB FROM T_TEST_2 WHERE ID = 2 FOR UPDATE;

    DBMS_LOB.LOADBLOBFROMFILE(V_BLOB,

                              BF,

                              DBMS_LOB.LOBMAXSIZE,

                              SRC_OFFSET,

                              DEST_OFFSET);

    DBMS_LOB.FILECLOSEALL();

    COMMIT;

END;

 

Insert blob:

DECLARE

    V_BLOB      BLOB;

    BF          BFILE;

    SRC_OFFSET  INTEGER := 1;

    DEST_OFFSET INTEGER := 1;

 

BEGIN

    BF := BFILENAME('TESTDIR', 'asd.txt');

    DBMS_LOB.FILEOPEN(BF, OPEN_MODE => 0);

    INSERT INTO T_TEST_2 VALUES (3, EMPTY_BLOB()) RETURN LOBS INTO V_BLOB;

    DBMS_LOB.LOADBLOBFROMFILE(V_BLOB,

                              BF,

                              DBMS_LOB.LOBMAXSIZE,

                              SRC_OFFSET,

                              DEST_OFFSET);

    DBMS_LOB.FILECLOSE(FILE_LOC => BF);

    COMMIT;

END;

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-729873/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-729873/