OUTFILE

The OUTFILE command lets you redirect the text output of statements to a file.

Syntax

OUTFILE [APPEND|EOF] file-name [NOCACHE] [NLS_CHARSET charset-exp]

Arguments

APPEND

Specifies that the output should be added to the end of an existing disk file. When you omit this argument, the new output replaces the current contents of the file. APPEND has no effect when the file does not already exist or when you specify EOF.

file-name

A text expression that is the name of the file to which output should be written. Unless the file is in the current directory, you must include the name of the directory object in the name of the file.

Note:

Directory objects are defined in the database, and they control access to directories and file in those directories. You can use the CDA command to identify and specify a current directory object. Contact your Oracle DBA for access rights to a directory object where your database user name can read and write files.

EOF

The current outfile is closed and output is redirected to the default outfile.

NOCACHE

Specifies that Oracle OLAP should write lines to the outfile as they are generated. Without this keyword, Oracle OLAP reduces file I/O activity by saving text and writing it periodically to the file. The NOCACHE keyword slows performance significantly, but it ensures that every line is immediately recorded in the outfile. This argument must be specified afterfile-name

NLS_CHARSET charset-exp

Specifies the character set that Oracle OLAP will use when writing data to the file specified by file-name. This allows Oracle OLAP to convert the data accurately into that character set. This argument must be specified after file-name. When this argument is omitted, then Oracle OLAP writes the data to the file in the database character set, which is recorded in the NLS_LANG option.

Notes

File Reading and Writing Options

A number of options are important during file read and write operations. These options are listed in Table 15-1, "File Reading and Writing Options".

Permission Programs: Copying to and from Analytic Workspaces

When you export PERMIT_READ or PERMIT_WRITE programs which are hidden, they are empty when imported. Additionally, when you outfile PERMIT_READ or PERMIT_WRITE programs which are hidden, then they are empty when infiled.

Tip:

Rename PERMIT_READ and PERMIT_WRITE programs before using EXPORT (to EIF) or OUTFILE. After copying the programs to an analytic workspace using IMPORT (from EIF) or INFILE.

Current Outfile Identifier

As a first step, every OUTFILE command closes the current outfile. When OUTFILE opens a new outfile on disk, it automatically assigns to it an arbitrary integer as its file unit number. The current file unit number is held in the OUTFILEUNIT option.

Appending to an Outfile

When you send output to a file and then send output to a second file, the first file does not remain open. To resume sending output to the first file, you must execute another OUTFILE command and include the APPEND file-name phrase.

Automatic Closing of Outfile

When you use OUTFILE file-name to direct output to a disk file, OUTFILE closes any outfile currently open. This happens even when the new file is not actually opened (as when you specify an invalid file-name in the OUTFILE command).

Paging Options and Redirected Output

The paging options control the organization of text output in pages. Examples are: BMARGIN, LINENUM, LINESLEFT, PAGESIZE, PAGENUM, PAGEPRG, PAGING, TMARGIN, and LSIZE. The paging options have a separate value for each separate outfile. When you set one of the paging options to control output to a disk file, the new value remains in effect until you use the OUTFILE command again to redirect output. At this point, the paging option returns to its default value. Therefore, when you want a paging option to have a particular value for a disk file, you generally have set it after you execute the OUTFILE command.

Line Length

The maximum line length in Oracle OLAP is 4000 characters.

Current and Default Outfiles

The current outfile is the destination for the output of statements, such as REPORT and DESCRIBE, that produce text. When you have not used the OUTFILE command to send output to a file, Oracle OLAP uses your default outfile.

Examples

Example 19-16 Sending a Report to an Output File

In this example, you want to send the output of a REPORT command to an output file.

OUTFILE 'budget.rpt'
REPORT budget
OUTFILE EOF

Example 19-17 Directing Output to a File

Suppose you have a program called year.end.sales, and you want to save the report it creates in a file. Type the following commands to write a file of the report. In this example, userfiles is a directory object and yearend.txt is the name of the file.

OUTFILE 'userfiles/yearend.txt'
year.end.sales
OUTFILE EOF

Now the file contains the year.end.sales report. You can add more reports to the same file with the APPEND keyword for OUTFILE. Suppose you have another program called year.end.expenses. Add its report to the file with the following commands. Remember that without APPEND, the OUTFILE command overwrites the expense report.

OUTFILE APPEND 'userfiles/yearend.txt'
year.end.expenses
OUTFILE EOF