Oracle BFILE

大部分来源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'.