FORMATSQL | NOFORMATSQL

Valid for

Extract

Description

Use FORMATSQL to format subsequent output records in external SQL DML format. This is the format SQL needed to create the operation, that is, an INSERT, UPDATE or DELETE statement. You can apply this format to both SQL and Enscribe records.

FORMATSQL applies to all Oracle GoldenGate files specified below the FORMATSQL entry. Turn off FORMATSQL with the NOFORMATSQL parameter.

The output contains the following transaction-related information before the first record output for each transaction:

• the begin transaction indicator, B

• the timestamp at which the record committed

• the sequence number of the audit trail in which the commit was found

• the relative byte address (RBA) of the commit record within the audit trail

• commas following each of the above fields

• a new line character

After the last record in each transaction is output, a commit record will appear. This record contains C, the delimiter and a new line character.

Every record in a transaction, and no other records, are contained between the begin and commit indicators. Each combination of commit timestamp and RBA is unique and increases as records are output.

Do not use this format if the data will be processed by Replicat. Replicat expects the default format.

Syntax

NOFORMATSQL | FORMATSQL {ORACLE | NONAMES | FILE}

ORACLE

Records are formatted for compatibility with Oracle databases. Primarily this means that date and time fields are converted to a format suitable to SQL\*Plus (for example, TO_DATE('2010-05-01','YYYY-MM-DD').

NONAMES

Omits column names when all columns are present in insert records to conserve space.

FILE

Outputs only the file portion of the NonStop file name in the SQL statement.