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. WithoutNULLISSPACE
, 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, useTAB
. 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
andUNIFIED UPDATE
operations as a pair ofDELETE
andINSERT
operations.PKUPDATE
andUNIFIED UPDATE
operations are formatted as anUPDATE
operation if the option is not specified. This option is ignored if theSQLLOADER
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
OUTPUTFORMATXML NOINLINEPROPERTIES, NOTRANS