Re: Open file from PL/SQL
Date: 1997/02/28
Message-ID: <331699dc.361895_at_news>
On 25 Feb 1997 10:18:37 GMT, "ARRASATE SISTEMAS" <arrasis_at_arrakis.es> wrote:
>
> Hi Oraclers:
>
> I need to open an ASCI file from a stored procedure and I don´t know how.
>I know how I can make in Forms, through the Package TEXT_IO and its
>functions FOPEN, PUT_LINE ...
>
> The file would content something like the following:
>
> Hello Oracle-world<EOL>
> Bye<EOL>
> <EOF>
>
> Can anybody help me ?
>
> Thanks in advance.
>
> Clemen.
>
>____________________________________________________________________________
>
>
> Arrasate Sistemas S.L.
> Mondragon (Gipuzkoa)
> Spain
>
> arrasis_at_arrakis.es
>____________________________________________________________________________
>
>
Hi Clemen!
Oracle PL/SQL 2.3 and above supports a file I/O package called UTL_FILE. This should pretty much solve all your simple file handling problems.
The functions within the package are as follows:
FOPEN opens a text file, creating a new file if the file doesn't already exist.
FOPEN (location, filename, open_mode)
where
location VARCHAR2 Directory path of file filename VARCHAR2 Name of file open_mode VARCHAR2 Mode to be used r Read text w Write text a Append text
The return value of this function is of type UTL_FILE.FILE_TYPE and represents the file handle to be used in subsequent functions.
FCLOSE will close a file if it is already open and raise an exception if it is closed (EXCEPTION INVALID_FILEHANDLE).
FCLOSE(file_handle)
where
file_handle FILE_TYPE file handle as returned by FOPEN
ISOPEN will return TRUE if the file specified by file_handle is open and false if it is not.
IS_OPEN(file_handle)
where
file_handle FILE_TYPE file handle as returned by FOPEN
PUT will output the specified string to the specified file.
PUT(file_handle, buffer)
where
file_handle FILE_TYPE file handle as returned by FOPEN buffer VARCHAR2 Text string to be output to file
If the file wasn't opened in w or a mode then an exception INVALID_OPERATION will be raised.
NEW_LINE writes one or more line terminators to the file.
NEW_LINE(file_handle, lines)
where
file_handle FILE_TYPE file handle as returned by FOPEN lines NATURAL number of line terminators to be output *********************************************************************************PUT_LINE does the same as put except with an additional NEW_LINE.
PUT_LINE(file_handle, buffer)
where
file_handle FILE_TYPE file handle as returned by FOPEN buffer VARCHAR2 Text string to be output to file
If the file wasn't opened in w or a mode then an exception INVALID_OPERATION will be raised.
PUTF is similar to put but with formatting instructions.
PUTF(file_handle, format, arg1, arg2, arg3, arg4, arg5) where
file_handle FILE_TYPE file handle as returned by FOPEN format VARCHAR2 format string %s substitution indicator \n new line arg1..arg5 VARCHAR2 substitution variable to replace each . occurence of %s *********************************************************************************
The file output functions write to a memory buffer until either the buffer is full or the programmer specifies that the buffer should be flushed. When the buffer is emptied it is then and only then written to the file. The FFLUSH function allows the programmer to manually flush the buffer.
FFLUSH(file_handle)
where
file_handle FILE_TYPE file handle as returned by FOPEN
GET_LINE is used to read from a file one line at a time and placed in the buffer.
GET_LINE(file_handle, buffer)
where
file_handle FILE_TYPE file handle as returned by FOPEN buffer VARCHAR2 buffer into which the line is written
If the file was not opened in read mode then an exception (INVALID_OPERATION) is raised. When the last line is read from a file, further read attempts will produce the NO_DATA_FOUND exception. If a line is greater in size than the physical limit on the buffer, then a VALUE_ERROR exception is raised
Regards
James Blackwood
Consultant
Logica UK Limited
e-mail blackwoodj_at_logica.com tel 00 +44 (0)1224 843847 fax 00 +44 (0)1224 632089
The opinions expressed within this document are those of James
Blackwood and are not associated in any way with his employer Logica
UK Limited or any associated company.
James Blackwood
Consultant
Logica UK Limited
e-mail blackwoodj_at_logica.com tel 00 +44 (0)1224 843847 fax 00 +44 (0)1224 632089
The opinions expressed within this document are those of James Blackwood and are not associated in any way with his employer Logica UK Limited or any associated company. Received on Fri Feb 28 1997 - 00:00:00 CET