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

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

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

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

点击(此处)折叠或打开

1. create or replace package PB_API is
   
2. /****************************************
   
3.    Ver Date Author Description
   
4.    --------- ---------- --------------- ------------------------------------
   
5.    1.0 2014-3-24 gangjh 1. pb 無法返回record 類型.
   
6.                                             無法調用某些package
   
7.                                             重新封裝一下
   
8. *****************************************************************/
   
9. 
   
10.  function fopen(location IN VARCHAR2,
    
11.                  filename IN VARCHAR2,
    
12.                  open_mode IN VARCHAR2) return INTEGER ;
    
13.  
    
14.  PROCEDURE fput_raw(p_file IN integer, buffer IN RAW) ;
    
15.  PROCEDURE fclose(p_file IN integer) ;
    
16.  
    
17.  
    
18.  function blob_new return INTEGER ;
    
19.  PROCEDURE blob_put_raw(p_loc IN integer, buffer IN RAW) ;
    
20.  function blob_get_value(p_loc in integer ) return blob ;
    
21.  PROCEDURE blob_tofile(p_loc IN integer, p_file varchar2) ;
    
22. 
    
23.  PROCEDURE blob_close(p_loc IN integer) ;
    
24.  
    
25.  function get_blob(src in bfile ) return blob ;
    
26.  
    
27.  procedure test ;
    
28. 
    
29. end PB_API;
    
30. /
    
31. create or replace package body PB_API is
    
32. 
    
33. 
    
34.  type file_type_tab is table of utl_file.file_type ;
    
35.  type blob_tab is table of blob ;
    
36.  
    
37.  v_files file_type_tab := new file_type_tab() ;
    
38.  v_lobs blob_tab := new blob_tab();
    
39. 
    
40.  function fopen(location IN VARCHAR2,
    
41.                  filename IN VARCHAR2,
    
42.                  open_mode IN VARCHAR2) return INTEGER is
    
43.    aa utl_file.file_type ;
    
44.    x number ;
    
45.  BEGIN
    
46.    v_files.extend() ;
    
47.    x := v_files.last() ;
    
48.    aa:= utl_file.fopen(location , filename , open_mode) ;
    
49.    v_files(x) := aa;
    
50.    return x ;
    
51.  END fopen;
    
52.  
    
53.  
    
54. 
    
55.    PROCEDURE fput_raw(p_file IN integer, buffer IN RAW) is
    
56.    begin
    
57.      utl_file.put_raw(v_files(p_file), buffer) ;
    
58.    end ;
    
59.           
    
60.    PROCEDURE fclose(p_file IN integer) is
    
61.    begin
    
62.      utl_file.fclose(v_files(p_file)) ;
    
63.      v_files.delete(p_file) ;
    
64.      v_files.trim() ;
    
65.    end ;
    
66.    
    
67. 
    
68.    
    
69.  function blob_new return INTEGER is
    
70.    x integer ;
    
71.  begin
    
72.    v_lobs.extend() ;
    
73.    x := v_lobs.last() ;
    
74.    dbms_lob.createtemporary(v_lobs(x), true) ;
    
75.    return x;
    
76.  end ;
    
77.  
    
78.    
    
79.    PROCEDURE blob_put_raw(p_loc IN integer, buffer IN RAW) is
    
80.    begin
    
81.       dbms_lob.append(v_lobs(p_loc) ,buffer);
    
82.    end ;
    
83.    
    
84. 
    
85. -- for pb use set trans_object
    
86.    function blob_get_value(p_loc in integer ) return blob is
    
87.    begin
    
88.         return v_lobs(p_loc) ;
    
89.    end ;
    
90.  
    
91.    PROCEDURE blob_close(p_loc IN integer) is
    
92.  
    
93.    begin
    
94.      dbms_lob.freetemporary(v_lobs(p_loc)) ;
    
95.      v_lobs.delete(p_loc) ;
    
96.      v_lobs.trim() ;
    
97.    end ;
    
98.        
    
99.    PROCEDURE blob_tofile(p_loc IN integer, p_file varchar2) is
    
100.    begin
     
101.     utl_lob.to_file( blob_get_value(p_loc), 'MAILTEMP', p_file) ;
     
102.    end ;
     
103.       
     
104. 
     
105. function get_blob(src in bfile ) return blob is
     
106.   v_file varchar2(200);
     
107.   v_path varchar2(200);
     
108.   dest blob ;
     
109.   bfile_loc bfile;
     
110. begin
     
111.   if src is null then
     
112.     return null;
     
113.   end if ;
     
114.   dbms_lob.createtemporary(dest, true, dbms_lob.call) ;
     
115.   dbms_lob.filegetname(src, v_path, v_file) ;
     
116.   
     
117.   bfile_loc := bfilename(v_path, v_file) ;
     
118.   dbms_lob.open(bfile_loc, dbms_lob.lob_readonly);
     
119.   dbms_lob.loadfromfile(dest, bfile_loc, dbms_lob.getlength(bfile_loc) ) ;
     
120.   dbms_lob.close(bfile_loc) ;
     
121.   return dest ;
     
122.     
     
123. end ;
     
124.        
     
125.    
     
126. end PB_API;
     
127. /

PB端封裝,定義user transaction object

点击(此处)折叠或打开

1. $PBExportHeader$uo_trans_yy3mat.sru
2. forward
3. global type uo_trans_yy3mat from transaction
4. end type
5. end forward
6. 
   
7. global type uo_trans_yy3mat from transaction
8. end type
9. global uo_trans_yy3mat uo_trans_yy3mat
10. 
    
11. type prototypes
12. subroutine PBAPI_FCLOSE(long P_FILE) RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"FCLOSE~""
13. function long PBAPI_FOPEN(string LOCATION,string FILENAME,string OPEN_MODE) RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"FOPEN~""
14. subroutine PBAPI_FPUT_RAW(long P_FILE,blob BUFFER) RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"FPUT_RAW~""
15. 
    
16. 
    
17. function long PBAPI_BLOB_NEW() RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"BLOB_NEW~""
18. subroutine PBAPI_BLOB_PUT_RAW(long P_LOC,blob BUFFER) RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"BLOB_PUT_RAW~""
19. subroutine PBAPI_BLOB_CLOSE(long P_LOC) RPCFUNC ALIAS FOR "~"PCNDBA~".PB_API.~"BLOB_CLOSE~""
20. 
    
21. end prototypes
22. type variables
23. //
24. end variables
25. 
    
26. forward prototypes
27. public function integer fwritefile (string filename, string dbpath, string dbfile)
28. public function integer fwriteblob (string filename)
29. end prototypes
30. 
    
31. public function integer fwritefile (string filename, string dbpath, string dbfile);integer li_FileNum, loops, i
32. long flen, bytes_read
33. blob b
34. long filehandle;
35. 
    
36. 
    
37. flen = FileLength(filename)
38. li_FileNum = FileOpen(filename, StreamMode!, Read!, LockRead!)
39. 
    
40. if ( li_FileNum = -1 or isnull(li_FileNum) ) then
41. messagebox('error', 'file ['+filename+'] read error') ;
42. return -1;
43. end if ;
44. 
    
45. IF flen > 32765 THEN
46. IF Mod(flen, 32765) = 0 THEN
47. loops = flen/32765
48. ELSE
49. loops = (flen/32765) + 1
50. END IF
51. ELSE
52. loops = 1
53. END IF
54. 
    
55. filehandle = PBAPI_FOPEN(dbpath, dbfile, "wb") ;
56. 
    
57. 
    
58. FOR i = 1 to loops
59. bytes_read = FileRead(li_FileNum, b)
60. 
    
61. PBAPI_FPUT_RAW(filehandle, b) ;
62. NEXT
63. PBAPI_FCLOSE(filehandle);
64. FileClose(li_FileNum)
65. 
    
66. return 0
67. 
    
68. 
    
69. 
    
70. end function
71. 
    
72. public function integer fwriteblob (string filename);integer li_FileNum, loops, i
73. long flen, bytes_read
74. blob b
75. long blobhandle;
76. 
    
77. 
    
78. flen = FileLength(filename)
79. li_FileNum = FileOpen(filename, StreamMode!, Read!, LockRead!)
80. 
    
81. if ( li_FileNum = -1 or isnull(li_FileNum) ) then
82. messagebox('error', 'file ['+filename+'] read error') ;
83. return -1;
84. end if ;
85. 
    
86. IF flen > 32765 THEN
87. IF Mod(flen, 32765) = 0 THEN
88. loops = flen/32765
89. ELSE
90. loops = (flen/32765) + 1
91. END IF
92. ELSE
93. loops = 1
94. END IF
95. 
    
96. blobhandle = PBAPI_BLOB_NEW() ;
97. if sqlcode <> 0 then
98. messagebox('error', sqlerrtext) ;
99. return -1;
100. end if;
101. 
     
102. FOR i = 1 to loops
103. bytes_read = FileRead(li_FileNum, b)
104. PBAPI_BLOB_PUT_RAW(blobhandle, b) ;
105. if sqlcode <> 0 then
106. messagebox('error', sqlerrtext) ;
107. end if;
108. NEXT
109. 
     
110. //PBAPI_BLOB_CLOSE(filehandle);
111. 
     
112. FileClose(li_FileNum)
113. 
     
114. return blobhandle
115. 
     
116. 
     
117. 
     
118. end function
119. 
     
120. on uo_trans_yy3mat.create
121. call super::create
122. TriggerEvent( this, "constructor" )
123. end on
124. 
     
125. on uo_trans_yy3mat.destroy
126. TriggerEvent( this, "destructor" )
127. call super::destroy
128. end on

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

点击(此处)折叠或打开

1. string ls_path, ls_file_name, ls_filetype
2. integer li_value ;
3. blob xlsdata ;
4. 
   
5. 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 (*.*),*.*")
6. 
   
7. IF LI_VALUE <> 1 AND LI_VALUE <> 0 THEN
8. MESSAGEBOX('提示:','無效路徑或文件名!!!',STOPSIGN!)
9. RETURN
10. ELSEIF LI_VALUE = 0 THEN
11. //沒有選舉文件 cancel退出
12. RETURN
13. END IF
14. 
    
15. 
    
16. 
    
17. 
    
18. sqlca.fwritefile(ls_path, 'MAILTEMP', ls_file_name) ;

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

点击(此处)折叠或打开

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

阿飛
2014/03/29