Oracle file operation Encapsulation

1. Create or replace package ylfileop_pack is
3. -- Author: yanlei
4. -- Created: 16:08:02
5. -- Purpose: File Operation package
6. Yl_dir constant varchar2 (32): = 'yl _ dir'; -- Financial table charge formid
7. -- Search from the end
8. Function lastindex (STR in varchar2, findstr in varchar2) return integer;
9. -- Obtain the path of a file
10. Function getpath (filename in varchar2) return varchar2;
11. -- Obtain a file
12. Function getfilename (filename in varchar2) return varchar2;
13. Procedure writefile (filename in varchar2, STR in long );
14. Function readfile return varchar2;
16. End ylfileop_pack;
17. /
18. Create or replace package body ylfileop_pack is
19. Isto_file utl_file.file_type; -- above
20. Procedure setoraclepath (Path in varchar2) is
21. Sqlstr varchar2 (1024 );
22. Begin
23. Sqlstr: = 'create or replace directory' | yl_dir | 'as' | path | '''';
24. Execute immediate sqlstr;
25. End;
26. -- Oracle string from the back to find by yanleigis Email: landgis@126.com
27. Function lastindex (STR in varchar2, findstr in varchar2) return integer is
28. I integer;
29. Num integer;
30. Subln integer;
31. Begin
32. Num: = length (STR );
33. Subln: = length (findstr );
34. If subln> num then
35. Return-1;
36. End if;
37. I: = num-subln + 1;
38. While I> 0 Loop
39. If substr (STR, I, subln) = findstr then
40. Return I;
41. End if;
42. I: = I-1;
43. End loop;
44. Return-1;
45. End;
46. -- Obtain the path of a file
47. Function getpath (filename in varchar2) return varchar2 is
48. Idx integer;
49. Begin
50. Idx: = lastindex (filename ,'/');
51. If (idx> 0) then
52. Return substr (filename, 1, idx );
53. End if;
54. Return '';
55. End;
56. -- Obtain a file
57. Function getfilename (filename in varchar2) return varchar2 is
58. Idx integer;
59. Begin
60. Idx: = lastindex (filename ,'/');
61. If (idx> 0) then
62. Return substr (filename, idx + 1 );
63. End if;
64. Return '';
65. End;
66. Function openfile (filename in varchar2, state in varchar2) return integer is
67. Path varchar2 (1024 );
68. Begin
69. Path: = getpath (filename );
70. Setoraclepath (PATH );
71. Isto_file: = utl_file.fopen (yl_dir, getfilename (filename), State );
72. Return 1;
73. Exception
74. When others then
75. Dbms_output.put_line (sqlcode | ':' | sqlerrm );
76. Return 0;
77. End;
78. Function openfileread (filename in varchar2) return integer is
80. Begin
81. Return openfile (filename, 'R ');
82. End;
84. Function openfilewrite (filename in varchar2) return integer is
85. Begin
86. Return openfile (filename, 'w ');
87. End;
88. Procedure writeline (STR in varchar2) is
89. Begin
90. Utl_file.put_line (isto_file, STR); -- write a string
91. End;
92. Procedure writelinelong (STR in long) is
93. Begin
94. Utl_file.put_line (isto_file, STR); -- write a string
95. End;
96. Procedure closefile is
97. Sqlstr varchar2 (1024 );
98. Begin
99. -- Utl_file.fflush (isto_file); -- brush Buffer
100. Utl_file.fclose (isto_file); -- close the file pointer
101. Sqlstr: = 'drop directory' | yl_dir;
102. Execute immediate sqlstr;
104. End;
106. Procedure writefile (filename in varchar2, STR in long) is
107. Begin
108. If openfilewrite (filename)> 0 then
109. Writelinelong (STR );
111. Closefile ();
113. End if;
114. End;
115. Function Readline return varchar2 is
116. -- Read a row and put it in the fp_buffer variable.
117. Fp_buffer varchar2 (20000 );--
118. STR varchar2 (4000 );
119. Begin
120. Loop
121. Begin
122. Utl_file.get_line (isto_file, STR); -- read a row and put it in the fp_buffer variable.
123. Fp_buffer: = fp_buffer | STR;
124. Fp_buffer: = fp_buffer | CHR (13); -- CHR (10)
126. Exception
127. When no_data_found then
128. Exit;
129. End;
130. End loop;
132. Return fp_buffer;
133. End;
134. Function readfile return varchar2 is
135. STR varchar2 (20000 );
136. Begin
137. If openfileread ('C:/YL. W')> 0 then
138. STR: = Readline;
139. Closefile ();
140. Return STR;
142. End if;
143. Return 'failed to read the file ';
144. End;
146. End ylfileop_pack;
147. /