OUTPUTFORMAT

Valid For

Extract

Description

Use the OUTPUTFORMAT parameter to output data in text, SQL, and XML formats.

Default

None

Syntax

OUTPUTFORMAT format_type  [, option]  [, ...]

OUTPUTFORMAT TEXT

Use the TEXT format_type to output data in external text format instead of the default Oracle GoldenGate canonical format. 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-utilityinitial load method.

This type of 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, OUTPUTFORMAT TEXT will not take effect.

option can be one of the following:

INCLUDE (HEARTBEAT)

Includes the heartbeat table records. By default, the heartbeat table records are ignored.

BCP

Formats the output for compatibility with SQL Server’s Bulk Copy Program and other bulk load utilities.

The following options are ignored when the BCP option is specified:

• NAMES | NONAMES — Specifies whether or not to include column names. NAMES is the default.

• NULLISSPACE — Output NULL columns as empty columns. Without NULLISSPACE, NULL columns are output as NULL.

• PLACEHOLDERS — Outputs placeholder for missing columns.

• NOHDRFIELDS — Does not include any metadata, such as the before and after indicator, and transaction information. Outputs column data only.

• DELIMITER 'delimiter' — Specifies the field delimiter character. To specify tabulation, use TAB. The default is a comma ‘,’.

• OP | _NOOP — Specifies whether or not to include operation type indicator (I, D, U, V). OP is the default.

• IND | _NOIND — Specifies whether or not to include the before and after image indicator (B or A). IND is the default.

• _TRANSTMTS | _NOTRANSTMTS — Specifies whether or not to include transaction information. _TRANSTMTS is the default.

• _WHOLEFILE — Includes the fully-qualified object name including the schema name.

• _FILE — Includes the object name only.

SQLLOADER

Produces a fixed-length text formatted file that is compatible with the Oracle SQL*Loader utility or the IBM load utility.

**DATE

TIME

TS**

Specifies the record timestamp precision to output. By default, this parameter does not output record timestamp. You can use 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 microseconds).

SQLLOADER

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

OUTPUTFORMAT SQL

Use the OUTPUTFORMAT SQL parameter to output data in external SQL format, instead of the default Oracle GoldenGate canonical format. OUTPUTFORMAT SQL generates SQL statements (INSERT, UPDATE, DELETE) that can be applied to SQL tables by utilities other than Oracle GoldenGate Replicat.

INCLUDE (HEARTBEAT)

Includes the heartbeat table records. By default, heartbeat table records are ignored.

ENCODING encoding

Outputs the SQL format file in the specified encoding. Oracle GoldenGate character set names are supported. By default, is current operating system character set. No character set conversion on column data is performed with the default character set.

The following options specify the specific output format. The options are exclusive so cannot be specified together.

ORACLE

Formats records for compatibility with Oracle Databases by converting date and time columns to a format accepted by SQL*Plus.

SQLPLUS

Formats records for compatibility with Oracle Databases by converting date and time columns to a format accepted by SQL*Plus.

SQLLOADER

Produces a fixed-length text formatted file that is compatible with the Oracle SQL*Loader utility or the IBM load utility program.

This is exactly the same as OUTPUTFORMAT TEXT SQLLOADER, which Oracle recommends that you use..

**_TRANSTMTS

_NOTRANSTMTS**

Includes SQL transaction information as comment. _NOTRANSTMTS is the default.

For example:

-B, 2016-07-09:09:9:21.000000,1357991461,627

WHOLEFILE

Includes the fully-qualified object name including the schema name.

FILE

Includes the object name only.

NOPKUPDATES

Formats PKUPDATE and UNIFIED UPDATE operations as a pair of DELETE and INSERT operations. PKUPDATE and UNIFIED UPDATE operations are formatted as an UPDATE operation if the option is not specified. This option is ignored if the SQLLOADER option is used.

OUTPUTFORMAT XML

Use the OUTPUTFORMAT XML parameter to output data in XML format, instead of the default Oracle GoldenGate canonical format. An OUTPUTFORMAT XML statement affects all Extract files or trails that are defined after it. By default, the XML is output in the character set of the local operating system.

XML stored as CLOB or BLOB is output up to 4000 bytes. To include larger XML stored as BLOB or CLOB, use the ENCODING option.

XML stored as CLOB is always output in a CDATA section regardless of its size. This is to avoid the overhead of converting reserved characters such as <, > and & to the appropriate XML representation.

Binary data including BLOB are encoded as Base64, which represents binary data in an ASCII string format and allows output to XML.

The XML, the 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 unless the ENCODING option is used to output in UTF-8.

**INCLUDE (HEARTBEAT

LOB

USERTOKEN)**

Includes the heartbeat table records. LOB more than 4000 bytes and Oracle GoldenGate user tokens.

By default, heartbeat table records are ignored and doesn’t include LOB more than 4000 bytes and user tokens.

BLOB more than 4000 bytes is encoded in Base64, and CLOB more than 4000 bytes is formatted in a CDATA section.

**INLINEPROPERTIES

NOINLINEPROPERTIES**

Controls whether or not properties are included within the XML tag or written separately. INLINEPROPERTIES is the default.

**TRANS

NOTRANS**

Controls whether or not transaction boundaries and commit timestamps should be included in the XML output. TRANS is the default.

**CLOSETRANS

NOCLOSETRANS**

Forces the closure of opened transaction boundaries and commits the timestamp upon rollover. It adds same transaction boundaries and commit timestamp tags to the next XML file after rollover.

The option is ignored if the TRANS option is not specified.

ENCODING xml_encoding

Outputs an XML file in the specified encoding. The default is UTF-8. The following MIME encoding names are supported.

UTF-8 ISO-10646 UTF-8, surrogate pairs are 4 bytes per character

UTF-16 ISO-10646 UTF-16

windows-1250 Windows Central Europe

windows-1251 Windows Cyrillic

windows-1252 Windows Latin-1

windows-1253 Windows Greek

windows-1254 Windows Turkish

windows-1255 Windows Hebrew

windows-1256 Windows Arabic

windows-1257 Windows Baltic

windows-1258 Windows Vietnam

windows-874 Windows Thai

IBM437 DOS Latin-1

IBM775 DOS 775, Baltic

IBM850 DOS multilingual

cp851 DOS Greek-1

IBM852 DOS Latin-2

IBM855 DOS Cyrillic

IBM857 DOS Turkish

IBM00858 DOS Multilingual with Euro

IBM860 DOS Portuguese

IBM861 DOS Icelandic

IBM862 DOS Hebrew

IBM863 DOS French

IBM864 DOS Arabic

IBM865 DOS Nordic

IBM866 DOS Cyrillic / GOST 19768-87

IBM868 DOS Urdu

IBM869 DOS Greek-2

ISO-8859-1 ISO-8859-1 Latin-1/Western Europe

SO-8859-2 ISO-8859-2 Latin-2/Eastern Europe

ISO-8859-3 ISO-8859-3 Latin-3/South Europe

ISO-8859-4 ISO-8859-4 Latin-4/North Europe

ISO-8859-5 ISO-8859-5 Latin/Cyrillic

ISO-8859-6 ISO-8859-6 Latin/Arabic

ISO-8859-7 ISO-8859-7 Latin/Greek

ISO-8859-8 ISO-8859-8 Latin/Hebrew

ISO-8859-9 ISO-8859-9 Latin-5/Turkish

ISO-8859-10 ISO-8859-10 Latin-6/Nordic

ISO-8859-13 ISO-8859-13 Latin-7/Baltic Rim

ISO-8859-14 ISO-8859-14 Latin-8/Celtic

ISO-8859-15 ISO-8859-15 Latin-9/Western Europe

ISO-8859-16 ISO-8859-16, Latin-10, South Eastern Europe

KOI8-R KOI8-R, Russian

KOI8U KOI8-U, Ukranian

TIS-620 Thai Industrial Standard 620-2533

DEC-MCS DEC Multilingual

hp-roman8 HP Latin-1 Roman8

Shift_JIS Shift_JIS, Windows-932

GBK GBK, Windows-936

KSC_5601 KSC-5601, Windows-949

Big5 Big-5 Traditional Chinese, Windows-950

EUC-JP EUC Japanese

GB2312 GB-2312-1980

EUC-KR EUC Korean

GB18030 GB-18030

HZ-GB-2312 HZ GB-2312

Big5-HKSCS Big-5, HongKong extension

Example

OUTPUTFORMAT XML NOINLINEPROPERTIES, NOTRANS