SQL*LOADER

The sql_loader plugin invokes Oracle SQL*Loader to load files into a database table. The SQL*Loader controlfile is generated from the specification in the item parameters:

• name. The name of this item.

• user_id. The username and password used to authenticate against the database, in the format <user>/<password>@<tns_alias> where @<tns_alias> is optional. If the userid is not supplied, the standard pipeline authentication settings are used.

• controlfile. The name of the SQL*Loader controlfile that will be generated.

• specification. The SQL*Loader control file specification. If the specification spans multiple lines, the final character of each line must be a backslash. The final field must be file_id constant %file_id%.

• table. The name of the table being loaded.

• mode. append, truncate, insert or replace. Defaults to append.

• is_warning_error. Should a SQL*Loader warning be treated as an error (e.g. when a bad record is detected).

• logfile. The name of the SQL*Loader log file.

• badfile. The name of the SQL*Loader bad file.

• discardfile. The name of the SQL*Loader discard file.

• keep_logfile. Whether the log file will be kept after the completion of the item. Defaults to yes (1).

• keep_controlfile. Whether the control file will be kept after the completion of the item. Defaults to no (0).

• keep_badfile. Whether the bad file will be kept after the completion of the item. Defaults to yes (1).

• keep_discardfile. Whether the discard file will be kept after the completion of the item. Defaults to yes (1).

• localize. A boolean setting that instructs the loader to localize the end-of-line markers for the current file system. Defaults to 0.

• parameters. A section containing SQL*Loader options.

• on_error. Override the setting for the job..

Example

    <item>
        name          = load scores
        type          = sql_loader
        controlfile   = %app_root%/log/%basename(filename)%.ctl # default = %filename%.ctl
        table         = scores
        mode          = truncate
        is_warning_error = 1
        
        # Specification: use slash at the end-of-line to signal
        # continuity. You must always include "file_id constant %file_id%"
        
        specification =  fields terminated by "," \
                (id, name, score, file_id constant %file_id%)
               
        # These are optional. If the filename never changes (i.e. it
        # has the same name with every load, you may wish to include $$
        # in the name to include the OS process id in the name. This
        # id will not be unique as process ids are reused.
        
        logfile          = %app_root%/log/%basename(filename)%.log
        badfile          = %app_root%/log/%basename(filename)%.bad
        discardfile      = %app_root%/log/%basename(filename)%.disc
        
        keep_controlfile = 1 # Default no, probably keep during initial dev
        keep_logfile     = 1 # Optional. Default yes
        keep_badfile = 1 # Optional. Default yes
        
        localize         = 0

        # Optional SQL*Loader keywords
        <parameters>
            skip = 1
            direct = true
        </parameters>
    </item>