Oracle7 Server Utilities

Specifying Filenames and Database Objects

This section explains how to use quotation marks for specifying database objects and filenames in the load control file. It also shows how the escape character is used in quoted strings.

Database Object Names within Double Quotation Marks

SQL*Loader follows the SQL standard for specifying object names (for example, table and column names): SQL and SQL*Loader reserved words must be specified within double quotation marks. The reserved words most likely to be column names are:

COUNT			DATA		DATE		FORMAT

OPTIONS		PART		POSITION

So if you had an inventory system with columns named PART, COUNT, and DATA, you would specify these column names within double quotation marks in your SQL*Loader control file. For example:

INTO TABLE inventory

(partnum   INTEGER,

 "PART"    CHAR(15),

 "COUNT"   INTEGER,

 "DATA"    VARCHAR2(30))

See Appendix B, "Reserved Words", for a complete list of reserved words.

You use double quotation marks if the object name contains special characters other than those recognized by SQL ($, #, _), or if the name is case sensitive.

SQL String within Double Quotation Marks

You also specify the SQL string within double quotation marks. The SQL string applies SQL operators to data fields. It is described

Filenames within Single Quotation Marks

On many operating systems, attempting to specify a complete file pathname produces an error, due to the use of special characters other than $, #, or _. Usually, putting the pathname within single quotation marks avoids the error. Filenames that use the backslash character, \, may require special treatment, as described in the section, "Using a Backslash in Filenames",

For example:

INFILE 'mydata.dat'

BADFILE 'mydata.bad'

Quotation Marks in Quoted Strings

SQL*Loader uses strings within double quotation marks and strings within single quotation marks in the control file. Each type of string can appear within the other.

Backslash Escape Character

In DDL syntax only, you can place a double quotation mark inside a string delimited by double quotation marks by preceding it with the escape character, \, whenever the escape character is allowed in the string. (The following section tells when the escape character is allowed.) The same holds true for putting a single quotation mark into a string delimited by single quotation marks. For example, a double quotation mark is included in the following string which points to the homedir\data"norm\myfile datafile by preceding it with \:

INFILE 'homedir\data\"norm\mydata'

To put the escape character itself into a string, enter it twice, like this: \\

For example:

"so'\"far"	    or	  'so\'"far'	  is parsed as   so'"far

"'so\\far'"	    or	  '\'so\\far\''  is parsed as  'so\far'

"so\\\\far"	    or	  'so\\\\far'	  is parsed as  so\\far

Note: A double quote in the initial position cannot be escaped, therefore you should avoid creating strings with an initial quote.

Using a Backslash in Filenames

This section is of interest only to users of PCs and other systems that use backslash characters in file specifications. For all other systems, a backslash is always treated as an escape character, as described in the preceding section.

Non-Portable Strings

There are two kinds of character strings in a SQL*Loader control file that are not portable between operating systems: filename strings and file processing options strings. When converting to a different operating system, these strings must generally be rewritten. They are the non-portable strings. All other strings in a SQL*Loader control file are portable between operating systems.

Escaping the Backslash

If your operating system uses the backslash character to separate directories in a pathname and if the version of Oracle running on your operating system implements the backslash escape character for filenames and other non-portable strings, then you need to specify double backslashes in your pathnames and use single quotation marks.

Additional Information: To find out if your version of Oracle implements the backslash escape character for filenames, see your Oracle operating system-specific documentation.

For example, to load a file named "topdir\mydir\mydata", you must specify:

INFILE 'topdir\\mydir\\mydata'

Escape Character Sometimes Disallowed

The version of Oracle running on your operating system may not implement the escape character for non-portable strings. When the escape character is disallowed, a backslash is treated as a normal character, rather than as an escape character (although it is still usable in all other strings). Then pathnames such as:

INFILE 'topdir\mydir\myfile'

can be specified normally. Double backslashes are not needed.

Because the backslash is not recognized as an escape character, strings within single quotation marks cannot be embedded inside another string delimited by single quotation marks. This rule also holds for double quotation marks: A string within double quotation marks cannot be embedded inside another string delimited by double quotation marks.

Determining If the Escape Character is Allowed

As previously mentioned, you can learn if the backslash is used as an escape character in non-portable strings by checking your operating-system-specific Oracle7 documentation, Another way is to specify "test\me" in the file processing options string. Then check the log file. If the log file shows the file processing options string as

"test\me"

then the backslash is not used as an escape character, and double backslashes are not required for file specifications.

However, if the log file shows the file processing options string as:

"testme"

then the backslash is treated as an escape character, and double backslashes are needed.