chap12_5.html

Oracle资料大集合

</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.components.html"><font face="verdana,helvetica" size="1" color="000000"><b>Components</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.operatingsystems.html"><font face="verdana,helvetica" size="1" color="000000"><b>Operating Systems</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.userinterfaces.html"><font face="verdana,helvetica" size="1" color="000000"><b>User Interfaces</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.groupwareandcollaboration1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Groupware &amp; Collaboration</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>
<p>
<b><font size="+1">12.5	How do I匯ead and write operating system files with UTL_FILE?</font></b>

<p>

<B>Problem</B>

<p>

I need a method to read and write operating system files from within PL/SQL. I want to read and write files from within stored procedures and functions. How do I read and write operating system files?
<p>
<B>Technique</B>
<p>
The UTL_FILE built-in package is available with PL/SQL 2.3 and higher. The package enables you to both read and write operating system files. UTL_FILE modules can be called from within stored modules or client-side modules. Table 12.8 shows the modules that make up the UTL_FILE database package.
<p>
<table cellpadding=3 cellspacing=3 border=0 bgcolor="cdcdcd">

<TR><td>Name</td>
<td>Type</td>
<td>Description</td></TR>

<TR><td>FOPEN</td>
<td>Function</td>
<td>Opens the specified file.</td></TR>

<TR><td>IS_OPEN</td>
<td>Function</td>
<td>Returns TRUE if the file is currently open.</td></TR>

<TR><td>GET_LINE</td>
<td>Procedure</td>
<td>Gets the next line from the file.</td></TR>

<TR><td>PUT_LINE</td>
<td>Procedure</td>
<td>Writes a line to the file after appending the line terminator.</td></TR>

<TR><td>PUT</td>
<td>Procedure</td>
<td>Writes a line to the file without appending the line terminator.</td></TR>

<TR><td>PUTF</td>
<td>Procedure</td>
<td>Writes a formatted line to the file without appending the line terminator.</td></TR>

<TR><td>NEW_LINE</td>
<td>Procedure</td>
<td>Terminates current line and begins a new line.</td></TR>

<TR><td>FFLUSH</td>
<td>Procedure</td>
<td>Writes all data from the buffer to the file.</td></TR>

<TR><td>FCLOSE</td>
<td>Procedure</td>
<td>Closes the specified file.</td></TR>

<TR><td>FCLOSE_ALL</td>
<td>Procedure</td>
<td>Closes all open files.</td></TR>
</table>

<p>

In order to read a file you first have to declare a file handle, which is used to reference the file in the other procedures. Next, you must open the file with a call to FOPEN. The file can be opened to read, write, or append. The PUT_LINE, PUT, and PUTF procedures are used to write data to the file, which is closed using the FCLOSE procedure. The GET_LINE procedure is used to read a line of data and places it in a VARCHAR2 variable.

<p>

The locations of directories accessible to UTL_FILE must be defined using the utl_file_dir parameter in the INIT&lt;SID&gt;.ORA initialization file. Only exact directories listed in the INIT&lt;SID&gt;.ORA file are accessible. Subdirectories of accessible directories are not automatically accessible unless you specify utl_file_dir = * in your INIT&lt;SID&gt;.ORA file; then all directories are accessible to UTL_FILE.

<p>

After changing the INIT&lt;SID&gt;.ORA file, the Oracle instance must be brought down and back up for the changes to be effective. Ensure that the Oracle owner has read and write permissions on these directories.
<p>
<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user account. Run the CHP12_18.SQL file, as shown in <A HREF="image/12oht21.gif" TARGET="_blank">Figure 12.21</A>. The file contains PL/SQL code to open a file and write two lines to it.
<p>
Line 2 declares a file handle variable, the datatype of the file handle defined in the UTL_FILE package. Line 4 uses the FOPEN function to open the file CHP12.TXT in the C:\TEMP directory. The file handle returned by FOPEN is used in all subsequent calls. The W parameter specifies that the file is opened in write mode. If A is specified, the file is opened in append mode; R opens the file in a read mode.
<p>
Line 5 calls PUT_LINE to write a line of text to the file and line 6 calls PUTF to write a formatted line of text to the file. The maximum size for the buffer in any PUT_LINE procedure is 1,023 bytes. If an error occurred while writing to the file, the exception handler in lines 9 to 11 is raised. If the file was already opened for read by another process in the system, the exception handler in lines 12 to 14 is raised.
<P>
Line 7 closes the file using the FCLOSE procedure. Check the C:\TEMP directory to view the contents of the CHP12.TXT file. On UNIX, file permissions on a file created using the UTL_FILE package are rw-r--r--. If the UTL_FIL.INVALID_PATH EXCEPTION is raised, verify that you do not have a slash (\ or /) at the end of the directory path in either the INIT&lt;SID&gt;.ORA file or your code.
<p>
<B>2.</B>	Run the CHP12_19.SQL file in SQL*Plus. The PL/SQL block in the file reads the line of data written in the previous step. <A HREF="image/12oht22.gif" TARGET="_blank">Figure 12.22</A> shows the results of the operation in SQL*Plus.
<p>
Line 2 declares a VARCHAR2 variable which receives the data when read from the file. Line 3 declares the file handle controlling access to the file. Line 5 opens the file for reading by using the FOPEN function and passing 慠