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

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) ;

以上,及可將本地文件寫入主機目錄.
查詢bfile時,需將bfile轉為blob

select pb_api.get_blob(bfilename('MAILTEMP','cc.sql'))
from dual;

阿飛
2014/03/29