6.115 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-utility initial 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

FORMATXML NOINLINEPROPERTIES, NOTRANS