FORMATASCII parameter to output data in external ASCII format instead of the default Oracle GoldenGate canonical format. Using
FORMATASCII, you can format output that is compatible with most database load utilities and other programs that require ASCII input. This parameter is required by the file-to-database-utility initial load method.
FORMATASCII statement affects all extract files or trails that are listed after it in the parameter file. The relative order of the statements in the parameter file is important. If listed after a file or trail specification,
FORMATASCII will not take effect.
Do not use
FORMATASCII if the data will be processed by the Replicat process. Replicat expects the default canonical format.
Do not use
FORMATXML is being used.
Do not use
FORMATASCII if the data contains LOBs.
Do not use
FORMATASCII if Extract is connected to a multi-byte DB2 subsystem.
Do not use
FORMATASCII if Oracle GoldenGate DDL support is active.
Do not use FORMATASCII in pass-through mode in a data pump because there is no table metadata available to generate trail output in the specified form.
Database object names, such as table and column names, and
VARCHAR data are written in the default character set of the operating system.
Without specifying any parameter options,
FORMATASCII generates records in the following format.
Line 1 contains the following tab-delimited list:
The operation-type indicator:
V (insert, delete, update, compressed update).
A before or after image indicator:
The table name in the character set of the operating system.
A column name, column value, column name, column value, and so forth.
A newline character (starts a new line).
Line 2 contains the following tab-delimited begin-transaction record:
The begin transaction indicator,
The timestamp at which the transaction committed.
The sequence number of the transaction log in which the commit was found.
The relative byte address (RBA) of the commit record within the transaction log.
Line 3 contains the following tab-delimited commit record:
The commit character
A newline character.
Every record in a source transaction is contained between the begin and commit indicators. Each combination of commit timestamp and RBA is unique.
See "Default Output" for specific defaults.
FORMATASCII [, option] [, ...]
option can be one of the following:
Formats the output for compatibility with SQL Server's BCP, DTS, or SQL Server Integration Services (SSIS) bulk-load utility.
DATE | TIME | TS
Outputs one of the following:
DATE outputs the date (year to day).
TIME outputs the time (year to second).
TS outputs the transaction timestamp (year to fraction).
(Oracle SQL*Loader) Specifies the encoding of ASCII characters in Oracle
NCHAR columns. Valid value is
CHARSET allows the load to include character-length semantics when the source table contains
NCHAR data and variable-length characters set to UTF-8.
CHARcolumns contain 8-bit ASCII characters, the generated file will contain a mix of operating system-native 8-bit ASCII character coding and UTF-8 coding, and the load will not succeed.
An alternative delimiter character.
TAB (delimit with tabs). This is the default.
A character enclosed within single quotes, for example,
Includes placeholders for additional columns at the end of each record. Use this option when a target table has more columns than the source table.
NAMES | NONAMES
Includes or excludes column names as part of the output. For updates where only the changed values are present, column names are included unless you also specify the
NOHDRFIELDS [IND], [OP]
Suppresses output as follows:
Without options, suppresses everything except the data values themselves.
Suppresses everything except the before or after indicator (
A) and the data values.
Suppresses everything except the operation-type indicator (
V) and the data values.
Excludes quotation marks from character data. Without
NOQUOTE, characters are enclosed within single-quotes.
Excludes transaction information.
Outputs null columns as empty columns. Without
NULLISSPACE, null columns are output as the word
Outputs a placeholder for missing columns. For example, if the second and fourth columns are missing in a four-column table, the following output is possible:
Produces a fixed-length, ASCII-formatted file that is compatible with the Oracle SQL*Loader utility or the IBM Load Utility program.
The following examples are based on a source table named
test.customer and a sample transaction. The examples show how various
FORMATASCII options configure the output.
CUSTNAME CHAR(10) Primary key LOCATION CHAR(10) BALANCE INTEGER
INSERT INTO CUSTOMER VALUES ('Eric', 'San Fran', 550); UPDATE CUSTOMER SET BALANCE = 100 WHERE CUSTNAME = 'Eric'; COMMIT;
FORMATASCII without options produces the following:
B,2011-01-21:14:09:46.421335,8,1873474, I,A,TEST.CUSTOMER,CUSTNAME,'Eric',LOCATION, 'San Fran',BALANCE,550, V,A,TEST.CUSTOMER,CUSTNAME,'Eric',BALANCE,100, C,
FORMATASCII, NONAMES, DELIMITER '|' produces the following:
B|2011-01-21:14:09:46.421335|8|1873474| I|A|CUSTOMER|'Eric'|'San Fran'|550| V|A|CUSTOMER|CUSTNAME|'Eric'|BALANCE|100| C|
The last record returns column names for the
BALANCE columns because the record only contains values for columns that were updated, and
PLACEHOLDERS was not used.
FORMATASCII, NOHDRFIELDS, OP, TS, NONAMES, NOQUOTE produces the following:
I,CUSTOMER,2011-01-21:14:09:46.421335,Eric,San Fran,550, V,CUSTOMER,2011-01-21:14:09:46.421335,Eric,,100,
The absence of a value for the second column is indicated by two consecutive commas.