EVL – ETL Tool

Writeora

(since EVL 2.2)

Write stdin or <f_in> into <table> in Oracle database. If the table is not empty, it is truncated unless ‘--append’ option is used. When delete statement need to be used instead of truncate, use option ‘--delete’.

When <schema> is not present, environment variable ‘ORADATABASE’ is used.

Password is taken from file ‘$EVL_PASSFILE’, which is by default ‘$HOME/.evlpass’. When such file has not permissions 600 (or 400), it is ignored! For details see ‘evl-password’.

Writeora

is to be used in EVS job structure definition file. <f_in> is either input file or flow name.

evl writeora

is intended for standalone usage, i.e. to be invoked from command line and reading records from standard input.

EVD and EVS are EVL definition files, for details see evl-evd(5) and evl-evs(5).

SQL*Loader Field Separator:

Writing the table by SQL*Loader uses as field seprator the value of ‘$EVL_ORACLE_FIELD_SEPARATOR’, which is by default set to ‘\x1f’ (i.e. an Unit Separator), and last field in each record is separated by ‘\n’.

SQL*Loader script options

Custom options might be added to SQL*Loader script by environment variable ‘$EVL_ORACLE_SQLLDR_OPTIONS’, which can contain comma or newline separated list of options like:

BINDSIZE = n
COLUMNARRAYROWS = n
DATE_CACHE = n
DEGREE_OF_PARALLELISM = {degree-num|DEFAULT|AUTO|NONE}
DIRECT = {TRUE | FALSE} 
EMPTY_LOBS_ARE_NULL = {TRUE | FALSE}
ERRORS = n
EXTERNAL_TABLE = {NOT_USED | GENERATE_ONLY | EXECUTE}
FILE = tablespace file 
LOAD = n 
MULTITHREADING = {TRUE | FALSE}
PARALLEL = {TRUE | FALSE}
READSIZE = n
ROWS = n 
SDF_PREFIX = string
SILENT = {HEADER | FEEDBACK | ERRORS | DISCARDS | PARTITIONS | ALL} 
SKIP = n   
SKIP_INDEX_MAINTENANCE = {TRUE | FALSE}
SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}
STREAMSIZE = n
TRIM = {LRTRIM|NOTRIM|LTRIM|RTRIM|LDRTRIM}

and by default the variable is defined as:

export EVL_ORACLE_SQLLDR_OPTIONS="DIRECT = TRUE"

Synopsis

Writeora
  <f_in> [<schema>.]<table> <evd> [-x|--text-input]
  [-a|--append | --delete] [-u|--username=<oracle_user>]
  [ --connect=<connect_identifier> | --host=<hostname> [--port=<port>] ]
  [--reject=<f_out>] [--control=<ctl_file>]

evl writeora
  [<schema>.]<table> <evd> [-x|--text-input]
  [-a|--append | --delete] [-u|--username=<oracle_user>]
  [ --connect=<connect_identifier> | --host=<hostname> [--port=<port>] ]
  [--reject=<f_out>] [--control=<ctl_file>]
  [-v|--verbose]

evl writeora
  ( --help | --usage | --version )

Options

-a, --append

with this option data will be added to the table, otherwise overwrite it

--control=<ctl_file>

to use other than generated control file for SQL*Loader

--delete

with this option data will be deleted, not truncated

--reject=<f_out>

to catch the ‘BADFILE’ file from SQL*Loader

-x, --text-input

suppose the input as text, not binary

Standard options:

--help

print this help and exit

--usage

print short usage information and exit

-v, --verbose

print to stderr info/debug messages of the component

--version

print version and exit

’sqlldr’ options:

--connect=<connect_identifier>

sqlldr will be called in the form:

<username>/<password>@<connect_identifier>

where <connect_identifier> can be in the form:

[<net_service_name> | [//]Host[:Port]/<service_name>]

without this option environment variable ‘ORACONN’ (if defined) is used as connection identifier for sqlldr

-h, --host=<hostname>

either this or environment variable ‘ORAHOST’ should be provided when connecting to other host than localhost. If also ‘ORAHOST’ variable is set, this option has preference.

-p, --port=<port>

either this or environment variable ‘ORAPORT’ should be provided when using other than standard port ‘1521’.

-u, --username=<oracle_user>

without this option environment variable ‘ORAUSER’ is used as user for sqlldr