来至 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) ;
以上,及可將本地文件寫入主機目錄.
查詢bfile時,需將bfile轉為blob
点击(此处)折叠或打开
- select pb_api.get_blob(bfilename('MAILTEMP','cc.sql'))
- from dual;
阿飛
2014/03/29