Gerando XML com PL/SQL a partir de uma query

[rmanParticipante]

@Sulimar Arse


FOPEN Function

Syntax

UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER DEFAULT 1024)
RETURN FILE_TYPE;

max_linesize: Maximum number of characters for each line, including the newline character, for this file (minimum value 1, maximum value 32767). If unspecified, Oracle supplies a default value of 1024.


Primeiro aumente o max_linesize para o máximo, ou seja, 32767.


PUT Procedure

Syntax

UTL_FILE.PUT (
file IN FILE_TYPE,
buffer IN VARCHAR2);

Usage Notes

The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.


Existe um nota para a PROCEDURE PUT. Resumindo você não pode encher o buffer sem antes descarrega-lo em disco, para isso utilize a PROCEDURE FFLUSH.


FFLUSH Procedure

FFLUSH physically writes pending data to the file identified by the file handle. Normally, data being written to a file is buffered. The FFLUSH procedure forces the buffered data to be written to the file. The data must be terminated with a newline character.

Flushing is useful when the file must be read while still open. For example, debugging messages can be flushed to the file so that they can be read immediately.

Syntax

UTL_FILE.FFLUSH (
file IN FILE_TYPE);

Os dados descarregados pelo FFLUSH devem ser terminados por NEWLINE, utilize a PROCEDURE NEW_LINE.


NEW_LINE Procedure

This procedure writes one or more line terminators to the file identified by the input file handle. This procedure is separate from PUT because the line terminator is a platform-specific character or sequence of characters.

Syntax

UTL_FILE.NEW_LINE (
file IN FILE_TYPE,
lines IN BINARY_INTEGER := 1);


Segue a documentação oficial de UTL_FILE:

docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm