oracle bfile-凯发app官方网站

大部分来源oracle文档翻译
更详细内容见: oracle9i application developer's guide - large objects (lobs)

与lob(clob, blob), bfile操作有关的包dbms_lob

1>. 初始化
lob --> empty_clob(), empty_blob
bfile --> bfilename()

sql eg1:
-- ad_graphic_dir为directory, 具体oracle directory使用请参考oracle文档
insert into print_media values (3106, 13001, empty_blob(),
    empty_clob(), empty_clob(), empty_clob(), null,
    empty_blob(), bfilename('ad_graphic_dir', '3106_keyboard'),
    null, "your press release text goes here");

sql eg2:
/* inserting row containing a bfile by initializing a bfile locator
   [example script: 3953.sql] */

declare
/* initialize the bfile locator: */
lob_loc bfile := bfilename('adgraphic_dir', 'keyboard_graphic_3106_13001');
begin
    insert into print_media(product_id, ad_id, ad_graphic) values (3106, 13001, lob_loc);
    commit;
end;

2>. bfile数据加载到lob中
/* loading a lob with bfile data.
   procedure loadlobfrombfile_proc is not part of dbms_lob package: */

create or replace procedure loadlobfrombfile_proc is
   dest_loc       blob;
   src_loc        bfile := bfilename('adgraphic_dir', 'keyboard_graphic_3106_13001');
   amount        integer := 4000;
begin
   select ad_graphic into dest_loc from print_media where product_id = 3060 and ad_id = 13001 for update;

   /* opening the lob is mandatory: */
   dbms_lob.open(src_loc, dbms_lob.lob_readonly);

   /* opening the lob is optional: */
   dbms_lob.open(dest_loc, dbms_lob.lob_readwrite);

   dbms_lob.loadfromfile(dest_loc, src_loc, amount);

   /* closing the lob is mandatory if you have opened it: */
   dbms_lob.close(dest_loc);
   dbms_lob.close(src_loc);

   commit;
end;

oci中的使用:
c (oci): loading a lob with bfile data

/* loading a lob with bfile data. select the lob/bfile from the print_media table */
void selectlob(lob_loc, errhp, svchp, stmthp)
ociloblocator *lob_loc;
ocierror *errhp;
ocisvcctx *svchp;
ocistmt *stmthp;
{
     char selstmt[150];
     ocidefine *dfnhp, *dfnhp2;

     strcpy(selstmt, (char *) "select ad_photo from print_media where product_id=3106 and ad_id = 13001 for update");

     /* prepare the sql select statement */
     checkerr (errhp, ocistmtprepare(stmthp, errhp, selstmt,
                                     (ub4) strlen((char *) selstmt),
                                     (ub4) oci_ntv_syntax, (ub4)oci_default));

     /* define the column being selected */
     checkerr (errhp, ocidefinebypos(stmthp, &dfnhp, errhp, 1,
                                     (dvoid *)&lob_loc, 0 , sqlt_blob,
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0,
                                     oci_default)
       || ocidefinebypos(stmthp, &dfnhp2, errhp, 2,
                                     (dvoid *)&lob_loc, 0 , sqlt_blob,
                                     (dvoid *)0, (ub2 *)0, (ub2 *)0,
                                     oci_default));

     /* execute the sql select statement */
     checkerr (errhp, ocistmtexecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                     (const ocisnapshot*) 0, (ocisnapshot*) 0,
                                     (ub4) oci_default));
}

void loadlobfrombfile(envhp, errhp, svchp, stmthp)
ocienv *envhp;
ocierror *errhp;
ocisvcctx *svchp;
ocistmt *stmthp;
{
ociloblocator *dest_loc;
ociloblocator *src_loc;

/* allocate locators */
(void) ocidescriptoralloc((dvoid *) envhp,
                            (dvoid **) &dest_loc, (ub4)oci_dtype_file,
                            (size_t) 0, (dvoid **) 0);

(void) ocidescriptoralloc((dvoid *) envhp,
                            (dvoid **) &src_loc, (ub4)oci_dtype_file,
                            (size_t) 0, (dvoid **) 0);

checkerr(errhp, ocilobfilesetname(envhp, errhp, &src_loc,
                    (text *) "adphoto_dir", (ub2) strlen("adphoto_dir"),
                    (text *) "keyboard_photo_3106_13001",
                    (ub2) strlen(keyboard_photo_3106_13001")));

selectlob(dest_loc, errhp, svchp, stmthp);
checkerr(errhp, ocilobfileopen(svchp, errhp, src_loc, (ub1)oci_file_readonly));
checkerr(errhp, ocilobopen(svchp, errhp, dest_loc, (ub1)oci_lob_readwrite));
checkerr (errhp, ocilobloadfromfile(svchp, errhp, dest_loc, src_loc, (ub4)4000, (ub4)1, (ub4)1));
checkerr(errhp, ocilobclose(svchp, errhp, dest_loc));
checkerr(errhp, ocilobfileclose(svchp, errhp, src_loc));
}

bfile到lob的加载还可以使用dbms_lob.loadclobfromfile, dbms_lob.loadblobfromfile来实现.

bfile包含二进制数据，所以当通过bfile方式加载数据到clob/nclob时，oracle不进行字符集转化。
如果字符集是可变长的，例如utf-8或zhs16gbk，oracle使用ucs2存储lob数据。所以如果想bfile文件数据能够正确
加载入数据库，那么文件需要以usc2字符集存储。
那么还有一点需要注意的是，如果以unicode模式存储文件，那么文件会增加两个字节：
'ff fe'.

阅读(1442) | 评论(0) | 转发(0) |

上一篇：oracle empty_blob() empty_clob()

下一篇：盖茨爱盗版

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们