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