Re: Open file from PL/SQL

From: <blackwoodj_at_logica.com>
Date: 1997/02/28
Message-ID: <331699dc.361895@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 CST