2013年6月18日 星期二

Oracle: Update CLOB from file


Check file directory:
select * from dba_directories

Create directory:

create or replace directory dirName as
 'c:\data';



create or replace
procedure UPDATE_CLOB
AS

or use

DECLARE

   dest_clob CLOB;
   src_clob BFILE := BFILENAME('xxx from dba_directories', 'data1.txt');
   dst_offset number := 1 ;
   src_offset number := 1 ;
   lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
   warning number;
   BEGIN
DBMS_OUTPUT.ENABLE(100000);

SELECT text_value
      INTO   dest_clob
      FROM   SUBSIDY_ADMINISTRATION_SETTING
      WHERE  setting_id = 'test1'
      FOR UPDATE;

DBMS_OUTPUT.PUT_LINE('dest_clob' || dest_clob);

DBMS_LOB.OPEN(src_clob, DBMS_LOB.LOB_READONLY);
DBMS_OUTPUT.PUT_LINE('length: ' || DBMS_LOB.GETLENGTH(src_clob));
DBMS_OUTPUT.PUT_LINE('length: ' || lang_ctx);

DBMS_LOB.LoadCLOBFromFile(
DEST_LOB => dest_clob
, SRC_BFILE => src_clob
, AMOUNT => DBMS_LOB.GETLENGTH(src_clob)
, DEST_OFFSET => dst_offset
, SRC_OFFSET => src_offset
, BFILE_CSID => DBMS_LOB.DEFAULT_CSID
, LANG_CONTEXT => lang_ctx
, WARNING => warning
);

UPDATE   SUBSIDY_ADMINISTRATION_SETTING set text_value= dest_clob
WHERE  setting_id = 'test1';
     
     
DBMS_LOB.CLOSE(src_clob);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Loaded File using DBMS_LOB.LoadCLOBFromFile: (ID=1001).');
END;



1 則留言:

  1. Are you looking to earn cash from your websites/blogs by using popunder advertisments?
    If so, have you tried using PopAds?

    回覆刪除