0384-0387

Previous Table of Contents Next

Page 384

Figure 16.14.
SQL*Loader functions.

The function CONSTANT, followed by a value, generates the fixed value for every row inserted into the table. SQL*Loader interprets the value as a character but does convert it, if necessary, to the database column type. The value can be enclosed in quotation marks. Numeric values larger than 2 ³² _1 (4,294,967,295) must be enclosed in quotes.

The RECNUM function, which has no specified value associated with it, sets the column value to the number of the logical record from which the row was loaded. The records are counted sequentially from the beginning of the data file, beginning with record 1. The value is incremented for each logical record, even if the record was discarded, skipped , or rejected. If you are using the parallel option to load multiple data files concurrently, each SQL*Loader session generates duplicate values because each session begins with 1.

The SYSDATE function gets the current system date for each array of records when using the conventional path and for each block of records when using the direct path . The format of the date is the same as the SQL SYSDATE function. The database column must be type VARCHAR, CHAR, or DATE.

The SEQUENCE SQL*Loader function (which is not the same as the database object SEQUENCE) increments by the specified increment value specified for each logical record that is loaded or rejected. This function does not increment for discarded or skipped records. The starting value for the SEQUENCE, based on your specification, is

• The specified integer
• COUNT, which is the number of rows in the table, plus the increment
• MAX, which is the current maximum value for the column, plus the increment

If you are using the parallel option, the SEQUENCE function is the only option available to you in SQL*Loader to generate unique numbers ; all other options require SQL functions, which you cannot use in a direct path load.

Specifying the Datatype

SQL*Loader extracts data from a field in the data file, according to the datatype specification in the control file, and sends it to the RDBMS to be stored in the appropriate database column. The RDBMS automatically attempts to perform any data conversion. If the data conversion cannot be done, an error is returned and the record is rejected.

Page 385

Oracle interprets both character and binary (native) data. Character data is human-readable data you can produce in a text editor, whereas binary data is usually generated programmatically. SQL*Loader supports these character types:

CHAR

DATE

NUMERIC EXTERNAL

NOTE

Do not confuse the CHAR and DATE character datatypes in SQL*Loader with the column datatypes of the same names ; they are not the same.

SQL*Loader supports these binary (native) datatypes:

INTEGER	ZONED
SMALLINT	VARCHAR
FLOAT	GRAPHIC
DOUBLE	GRAPHIC EXTERNAL
BYTEINT	VARGRAPHIC
packed DECIMAL	RAW

Refer to the Oracle8 Server Utilities Guide for a complete description of the various character and binary datatypes that SQL*Loader supports.

Using SQL Functions

You can apply SQL functions to the field data by using a SQL string. The string can contain any combination of SQL expressions that return a single value. The string appears after any other specifications for a given column and must be enclosed in double quotation marks. If the string contains a syntax error, the load terminates with an error. If the syntax in the string is correct but causes a database error, the row is rejected, but processing continues. You can reference field names in the SQL string by prefacing them with a colon . Here are some examples of the usage of SQL functions:

 my_field position(1:18) integer external      "TRANSLATE (RTRIM(my_field),'N/A','0')" my_string CHAR terminated by ","      "SUBSTR (:my_string,1,10)" my_number position(1:9) DECIMAL EXTERNAL(8)      ":field/1000" 

Page 386

Multiple Table Statements

You have the ability with SQL*Loader to specify multiple INTO TABLE statements. Using multiple statements, you can perform these tasks :

• Extract multiple logical records from one physical record.
• Distinguish different record formats.
• Load data into different tables.

Listing 16.7 illustrates a case where you might want to use the values from different fields to populate a column, depending on conditions within the data.

Listing 16.7. Example of using field conditions to populate columns .

 INTO TABLE proj   WHEN projno ! = `  `        (empo         position(1:4) integer external,        projno        position(29:31) integer external) INTO TABLE proj   WHEN PROJNO ! = `  `        (empno        position(1:4) integer external        projno        position(33:35) integer external) 

Listing 16.8 illustrates a case where you might want to use the same data within a physical record to load the data into multiple tables.

Listing 16.8. Loading data from a single physical record into multiple tables.

 INTO TABLE dept      WHEN deptno < 100      (deptno POSITION(1:4) INTEGER EXTERNAL      (dname POSITION(6:19) CHAR) INTO TABLE emp      WHEN empno > 1000      (empno POSITION(1:4) INTEGER EXTERNAL,      ename POSITION(6:15) CHAR      deptno POSITION(17:18) INTEGER EXTERNAL) 

Figure 16.15 illustrates a case where multiple tables are populated based on WHEN conditions.

Figure 16.15.
Populating multiple
tables based on WHEN
conditions.

Page 387

Command-Line Options and Parameter Files

The name of the SQL*Loader executable is operating-system dependent; refer to the installation and configuration guide for your operating system to determine the executable filename.

By invoking SQL*Loader with no arguments, you see the following summary of the command-line arguments and their default values:

`="360"> userid Oracle username and password control Control filename log Log filename bad Bad filename data Data filename discard Discard filename discardmax Number of discards to allow (default all) skip Number of logical records to skip (default 0) load Number of logical records to load (default all) errors Number of errors to allow (default 50) rows Number of rows in conventional path bind array or between direct path data saves (default for conventional path is 64; direct path is all) bindsize Size of conventional path bind array in bytes (default 65536) silent Suppress messages during run (header, feedback, errors, discards, partitions, all) direct Use direct path (default FALSE) parfile Parameter file: name of file that contains parameter specifications parallel Do parallel load (default FALSE) file File from which to allocate extents

Two new Oracle8 index maintenance options are available:

SKIP_UNUSABLE_INDEXES (default FALSE)

SKIP_INDEX_MAINTENANCE (default FALSE)

For a complete description of the command-line arguments, refer to the Oracle8 Server Utilities Guide.

You can also specify command-line arguments in the OPTIONS clause of the control file or from a PARFILE, which makes arguments easier to maintain. See Listing 16.9 for an example of using a PARFILE.

Previous Table of Contents Next