FORMATASCII

Valid For

Extract

Description

Use the 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.

A 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.

Limitations

  • Do not use FORMATASCII if the data will be processed by the Replicat process. Replicat expects the default canonical format.

  • Do not use FORMATASCII if FORMATSQL or 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.

Default Output

Database object names, such as table and column names, and CHAR 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: I, D, U, V (insert, delete, update, compressed update).

  • A before or after image indicator: B or A.

  • 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, B.

  • 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 C.

  • 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.

Custom Output

You can customize the output format with optional arguments.

Default

See "Default Output" for specific defaults.

Syntax

FORMATASCII [, option] [, ...]

option can be one of the following:

BCP

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).

CHARSET set

(Oracle SQL*Loader) Specifies the encoding of ASCII characters in Oracle NCHAR columns. Valid value is UTF8.

CHARSET allows the load to include character-length semantics when the source table contains NCHAR data and variable-length characters set to UTF-8.

Note:

If both NCHAR and CHAR columns 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.
DELIMITER delimiter

An alternative delimiter character.

Valid values:

  • TAB (delimit with tabs). This is the default.

  • A character enclosed within single quotes, for example, '/'.

EXTRACOLS number

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 PLACEHOLDERS option.

NOHDRFIELDS [IND], [OP]

Suppresses output as follows:

NOHDRFIELDS

Without options, suppresses everything except the data values themselves.

IND

Suppresses everything except the before or after indicator (B or A) and the data values.

OP

Suppresses everything except the operation-type indicator (I, D, U, V) and the data values.

NOQUOTE

Excludes quotation marks from character data. Without NOQUOTE, characters are enclosed within single-quotes.

NOTRANSTMTS

Excludes transaction information.

NULLISSPACE

Outputs null columns as empty columns. Without NULLISSPACE, null columns are output as the word NULL.

PLACEHOLDERS

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:

'ABC',,123,,
SQLLOADER

Produces a fixed-length, ASCII-formatted file that is compatible with the Oracle SQL*Loader utility or the IBM Load Utility program.

Examples

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.

Table test.customer:

CUSTNAME    CHAR(10)   Primary key
LOCATION    CHAR(10)
BALANCE     INTEGER

Transaction:

INSERT INTO CUSTOMER VALUES ('Eric', 'San Fran', 550);
UPDATE CUSTOMER SET BALANCE = 100 WHERE CUSTNAME = 'Eric';
COMMIT;
Example 1   

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,
Example 2   

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 CUSTNAME and BALANCE columns because the record only contains values for columns that were updated, and PLACEHOLDERS was not used.

Example 3   

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.