Skip Headers

Oracle® OLAP DML Reference
10g Release 1 (10.1)

Part Number B10339-02
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF


The INFILE command causes Oracle OLAP to read statement input from a specified file.


INFILE {file-id|EOF} [NOW] [NLS_CHARSET charset-exp



The name of a file from which to read input. File-id is a text expression that represents the name of the file. The name must be in a standard format for a file identifier.

The input file must contain only OLAP DML statements, along with appropriate responses to any prompts generated by the statements. Each statement or response must appear on a separate line in the file.


Terminates the reading of input from the current file and causes Oracle OLAP to resume reading input from the location from which the INFILE command was executed. Use of INFILE EOF is optional. See "About the Input File" and "INFILE with Both NOW and EOF".


Indicates that Oracle OLAP should open the input file specified in the INFILE and read its statements immediately upon encountering the INFILE instead of waiting until the program containing the INFILE is finished. This has the effect of nesting the input file's statements within the program. See "INFILE with Both NOW and EOF". This argument must be specified after file-id.

NLS_CHARSET charset-exp

Specifies the character set that Oracle OLAP will use when reading data from the file specified by file-id. This allows Oracle OLAP to convert the data accurately into the current character set, as identified by the NLS_LANG option. This argument must be specified after file-id. When this argument is omitted, then Oracle OLAP handles the data in the file as having the database character set, which is recorded in the NLS_LANG option.


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".

Table 15-1 File Reading and Writing Options

Statement Description
An option that determines whether or not input lines and error messages should be echoed to the current outfile.
An option that specifies the behavior of Oracle OLAP when an error is reached when reading from a file using the INFILE command
An option that contains the type of escape that is produced by the INFILE keyword of the CONVERT function.
(Read-only) An option that contains the file unit number of the current OUTFILE destination, set by the last OUTFILE command.

About the Input File

When the end of the input file is reached, Oracle OLAP resumes reading input from the location from which the INFILE command was executed. This could be another input file. You do not need to end the input file with the statement INFILE EOF.

INFILE ignores trailing blanks at the end of a line, or between the last text on a line and a continuation mark. INFILE also ignores blank lines.

When you use the NOW keyword and the input file ends with a continued statement, the statement is ignored. For example, if the file ends with "show - ," Oracle OLAP ignores the SHOW command.

Using INFILE in a Program

When you include an INFILE command without the NOW keyword in a program, the INFILE command is not executed until the program has finished executing. In a nested program, it is not executed until all the programs involved have finished executing. Also, when several INFILE commands have been executed by a program, the input files are read in the opposite order from which they were specified.

For example, assume that program.a calls program.b which calls program.c, and each program contains two INFILE commands, one before and one after the call to the next program (as illustrated in the following code). In this case, the order of execution is: a2, b2, c2, c1, b1, a1.

   INFILE a1
           INFILE b1
                   INFILE c1
                   INFILE c2
           INFILE b2
   INFILE a2

When you include an INFILE command in a program with the NOW keyword, the INFILE command executes immediately. However, INFILE with the NOW keyword requires more space than usual on the program stack. To conserve stack space, you should use the NOW keyword only when it is necessary.

INFILE with NOW Outside of Programs

The NOW keyword is intended for use within programs, but you can use it at any time. When you use it when the input file would not ordinarily be deferred, the NOW keyword has no visible effect. However, since it requires extra stack space, you should not use it in these situations.

INFILE with Both NOW and EOF

When you use both the NOW and EOF keywords, the NOW keyword is ignored.

Displaying Infiled Statements and Responses

When you want the statements from a disk file to be copied to a debugging file as they are executed, see DBGOUTFILE.

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.


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.


Example 15-2 Reading the Input File Immediately

The following line of code in a program causes the file called newdefs to be read in immediately.

INFILE newdefs NOW