PB 使用utl_file讀寫oracle bfile的封裝.

本文介绍了一种通过PowerBuilder (PB) 调用Oracle存储过程实现BFILE类型读写的方法。通过创建并封装Oracle包PB_API，实现了PB与Oracle数据库间BFILE数据类型的交互。文中还提供了详细的PB端封装代码和测试示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

来至 http://blog.chinaunix.net/uid-173640-id-4187617.html

pb 開發使用 Oracle數據開發系統時,

一般情況下無法讀寫oracle bfile數據,
可重新封裝package讓PB調用.
測試代碼如下
pb package封裝

点击(此处)折叠或打开

create or replace package PB_API is
/****************************************
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2014-3-24 gangjh 1. pb 無法返回record 類型.
無法調用某些package
重新封裝一下
*****************************************************************/
function fopen(location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2) return INTEGER ;
PROCEDURE fput_raw(p_file IN integer, buffer IN RAW) ;
PROCEDURE fclose(p_file IN integer) ;
function blob_new return INTEGER ;
PROCEDURE blob_put_raw(p_loc IN integer, buffer IN RAW) ;
function blob_get_value(p_loc in integer ) return blob ;
PROCEDURE blob_tofile(p_loc IN integer, p_file varchar2) ;
PROCEDURE blob_close(p_loc IN integer) ;
function get_blob(src in bfile ) return blob ;
procedure test ;
end PB_API;
/
create or replace package body PB_API is
type file_type_tab is table of utl_file.file_type ;
type blob_tab is table of blob ;
v_files file_type_tab := new file_type_tab() ;
v_lobs blob_tab := new blob_tab();
function fopen(location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2) return INTEGER is
aa utl_file.file_type ;
x number ;
BEGIN
v_files.extend() ;
x := v_files.last() ;
aa:= utl_file.fopen(location , filename , open_mode) ;
v_files(x) := aa;
return x ;
END fopen;
PROCEDURE fput_raw(p_file IN integer, buffer IN RAW) is
begin
utl_file.put_raw(v_files(p_file), buffer) ;
end ;
PROCEDURE fclose(p_file IN integer) is
begin
utl_file.fclose(v_files(p_file)) ;
v_files.delete(p_file) ;
v_files.trim() ;
end ;
function blob_new return INTEGER is
x integer ;
begin
v_lobs.extend() ;
x := v_lobs.last() ;
dbms_lob.createtemporary(v_lobs(x), true) ;
return x;
end ;
PROCEDURE blob_put_raw(p_loc IN integer, buffer IN RAW) is
begin
dbms_lob.append(v_lobs(p_loc) ,buffer);
end ;
-- for pb use set trans_object
function blob_get_value(p_loc in integer ) return blob is
begin
return v_lobs(p_loc) ;
end ;
PROCEDURE blob_close(p_loc IN integer) is
begin
dbms_lob.freetemporary(v_lobs(p_loc)) ;
v_lobs.delete(p_loc) ;
v_lobs.trim() ;
end ;
PROCEDURE blob_tofile(p_loc IN integer, p_file varchar2) is
begin
utl_lob.to_file( blob_get_value(p_loc), 'MAILTEMP', p_file) ;
end ;
function get_blob(src in bfile ) return blob is
v_file varchar2(200);
v_path varchar2(200);
dest blob ;
bfile_loc bfile;
begin
if src is null then
return null;
end if ;
dbms_lob.createtemporary(dest, true, dbms_lob.call) ;
dbms_lob.filegetname(src, v_path, v_file) ;
bfile_loc := bfilename(v_path, v_file) ;
dbms_lob.open(bfile_loc, dbms_lob.lob_readonly);
dbms_lob.loadfromfile(dest, bfile_loc, dbms_lob.getlength(bfile_loc) ) ;
dbms_lob.close(bfile_loc) ;
return dest ;
end ;
end PB_API;
/

PB端封裝,定義user transaction object

点击(此处)折叠或打开

$PBExportHeader$uo_trans_yy3mat.sru
forward
global type uo_trans_yy3mat from transaction
end type
end forward
global type uo_trans_yy3mat from transaction
end type
global uo_trans_yy3mat uo_trans_yy3mat
type prototypes
subroutine PBAPI_FCLOSE(long P_FILE) RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"FCLOSE~""
function long PBAPI_FOPEN(string LOCATION,string FILENAME,string OPEN_MODE) RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"FOPEN~""
subroutine PBAPI_FPUT_RAW(long P_FILE,blob BUFFER) RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"FPUT_RAW~""
function long PBAPI_BLOB_NEW() RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"BLOB_NEW~""
subroutine PBAPI_BLOB_PUT_RAW(long P_LOC,blob BUFFER) RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"BLOB_PUT_RAW~""
subroutine PBAPI_BLOB_CLOSE(long P_LOC) RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"BLOB_CLOSE~""
end prototypes
type variables
//
end variables
forward prototypes
public function integer fwritefile (string filename, string dbpath, string dbfile)
public function integer fwriteblob (string filename)
end prototypes
public function integer fwritefile (string filename, string dbpath, string dbfile);integer li_FileNum, loops, i
long flen, bytes_read
blob b
long filehandle;
flen = FileLength(filename)
li_FileNum = FileOpen(filename, StreamMode!, Read!, LockRead!)
if ( li_FileNum = -1 or isnull(li_FileNum) ) then
messagebox('error', 'file ['+filename+'] read error') ;
return -1;
end if ;
IF flen > 32765 THEN
IF Mod(flen, 32765) = 0 THEN
loops = flen/32765
ELSE
loops = (flen/32765) + 1
END IF
ELSE
loops = 1
END IF
filehandle = PBAPI_FOPEN(dbpath, dbfile, "wb") ;
FOR i = 1 to loops
bytes_read = FileRead(li_FileNum, b)
PBAPI_FPUT_RAW(filehandle, b) ;
NEXT
PBAPI_FCLOSE(filehandle);
FileClose(li_FileNum)
return 0
end function
public function integer fwriteblob (string filename);integer li_FileNum, loops, i
long flen, bytes_read
blob b
long blobhandle;
flen = FileLength(filename)
li_FileNum = FileOpen(filename, StreamMode!, Read!, LockRead!)
if ( li_FileNum = -1 or isnull(li_FileNum) ) then
messagebox('error', 'file ['+filename+'] read error') ;
return -1;
end if ;
IF flen > 32765 THEN
IF Mod(flen, 32765) = 0 THEN
loops = flen/32765
ELSE
loops = (flen/32765) + 1
END IF
ELSE
loops = 1
END IF
blobhandle = PBAPI_BLOB_NEW() ;
if sqlcode <> 0 then
messagebox('error', sqlerrtext) ;
return -1;
end if;
FOR i = 1 to loops
bytes_read = FileRead(li_FileNum, b)
PBAPI_BLOB_PUT_RAW(blobhandle, b) ;
if sqlcode <> 0 then
messagebox('error', sqlerrtext) ;
end if;
NEXT
//PBAPI_BLOB_CLOSE(filehandle);
FileClose(li_FileNum)
return blobhandle
end function
on uo_trans_yy3mat.create
call super::create
TriggerEvent( this, "constructor" )
end on
on uo_trans_yy3mat.destroy
TriggerEvent( this, "destructor" )
call super::destroy
end on

PB測試代碼:
在buttun click事件中入調用代碼

点击(此处)折叠或打开

string ls_path, ls_file_name, ls_filetype
integer li_value ;
blob xlsdata ;
li_value = GetFileOpenName('選擇檔案:', ls_path, ls_file_name, "xls","Excel 2000 Files (*.xls),*.xls,Excel 2007 Files (*.xlsx),*.xlsx,OpenOffice Files (*.ods),*.ods,ALL Files (*.*),*.*")
IF LI_VALUE <> 1 AND LI_VALUE <> 0 THEN
MESSAGEBOX('提示:','無效路徑或文件名!!!',STOPSIGN!)
RETURN
ELSEIF LI_VALUE = 0 THEN
//沒有選舉文件 cancel退出
RETURN
END IF
sqlca.fwritefile(ls_path, 'MAILTEMP', ls_file_name) ;