SAS will support the use of the FNA (File Name Augmentation) facility utilized by the SQL*Loader prior to opening its files in circumstances where these files have not been allocated externally (via ALLOC or DD statement).

The Oracle Bulk Loader (SQL*Loader) reads CTL and DAT files as input to its function of loading Oracle tables. In the process, it creates a LOG file and possibly, DISCARD and BAD files. The SAS interface allocates and creates the CTL and DAT files before invoking the SQL*Loader, and later opens the LOG file to echo its contents to the SAS log. The DISCARD and BAD files are not processed, other than to pass any specified user options across to the SQL*Loader.

The FNA facility is invoked by SQL*Loader prior to opening its files in order to allow an installation to manage its dataset naming and attribute functions. The installation specifies to FNA an FTYPE (CTL, DAT, LOG, DSC or BAD), an FNAME, which may allow for dataset name expansion and substitution of variable symbols, and an FATTR keyword which allows dataset atributes to be specified for the SQL*Loader's output files.

SAS will check for a file having been externally defined with a logical filename of ORA$FNA. If found, SAS will open the file and read its contents, storing these for later use. The file contents are not checked for correct syntax. SAS searches for sub-entries FTYPE and FNAME, and extracts information to allow datasets to be created with names matching the names that SQL*Loader will generate when invoked. The FATTR sub-entry is ignored by SAS, since it is not pertinent to any of the SAS processing functions.

The rules for FNAME definition are as defined in the Oracle description, i.e. the name must be enclosed in single or double quotes, and may be a fully qualified name or subject to symbolic substitution and/or expansion. There are a couple of minor restrictions imposed:

• The maximum logical record size of the FNA file is 255 bytes.
• Each FNAME entry must be complete within one record of the input file. SAS does not cater for overflow from one record to the next in tacking together the dataset name.

Select the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

A fix for SAS 9.1.3 (9.1 TS1M3) for this issue is available at:

A fix for SAS 9.1.3 (9.1 TS1M3) with Asian Language Support (DBCS) for this issue is available at :