USEANSISQLQUOTES | NOUSEANSISQLQUOTES

Valid For

GLOBALS

Description

Use the USEANSISQLQUOTES and NOANSISQLQUOTES parameters to control how Oracle GoldenGate treats column names and literals that are enclosed within single or double quote marks.

Note:

When capturing and mapping object names, such as table names, Oracle GoldenGate always recognizes double-quoted strings as case-sensitive object names, regardless of whether USEANSISQLQUOTES or NOUSEANSISQLQUOTES is specified.

USEANSISQLQUOTES is the default behavior of Oracle GoldenGate. It directs Oracle GoldenGate to follow SQL-92 rules for using quotation marks. With USEANSISQLQUOTES enabled, Oracle GoldenGate treats a string within double quotes as a case-sensitive column name, and it treats a string within single quotes as a literal. For example, consider the behavior of the @STRLEN conversion function, which returns a string length. By default, Oracle GoldenGate interprets the double-quoted "ABC" as an upper-case column name, and @STRLEN returns the length of whatever the value is for column "ABC".

COLMAP ( TGT1 = @STRLEN("ABC") )

If the double quotes are changed to single quotes in the preceding example, Oracle GoldenGate interprets 'ABC' as a literal, and @STRLEN returns 3.

COLMAP ( TGT1 = @STRLEN('ABC') )

NOUSEANSISQLQUOTES is intended for backward compatibility with the parameter files of Oracle GoldenGate versions that predate version 12c, where strings in double quotes are intended to be literals and case-sensitive column names are not supported (whether or not they are within quotes). For example, consider the behavior of the @STRLEN conversion function, which returns a string length. With NOUSEANSISQLQUOTES, the following @STRLEN specification returns a value of 3 because Oracle GoldenGate interprets the double-quoted "ABC" as a literal.

  • COLMAP ( TGT1 = @STRLEN("ABC") )
    

When used, NOUSEANSISQLQUOTES affects all TABLE and MAP statements in the local Oracle GoldenGate instance.

Default

USEANSISQLQUOTES

Syntax

USEANSISQLQUOTES | NOUSEANSISQLQUOTES

Examples

Example 1   

The following matrix shows the difference in the use of quote marks around input variables between the default of USEANSIISQLQUOTES and NOUSEANSISQLQUOTES.

Table 3-36 Comparison of Input Requirements for [NO]USEANSISQLQUOTES

Input Variable USEANSISQLQUOTES NOUSEANSISQLQUOTES

Literal text

'text string'

"text string"

Unquoted column names in database

COLUMN1

COLUMN1

Quoted column name

(Case is preserved by Oracle GoldenGate.)

"Column1"

Not supported. All names are converted to upper case.


Example 2   

The following matrix shows how to escape literal single or double quote marks that are part of literal input.

Table 3-37 Escaping Literal Quote Marks

Input Variable USEANSISQLQUOTES NOUSEANSISQLQUOTES

Literal text is:

John's Car

'John''s' car

This example uses two apostrophes, one as the literal apostrophe and the other as the escape character. (The apostrophe is the same character as the single quote mark.)

"John's car"

No escape character is needed.

Literal text is:

Double quote (")

'Double quote (")'

No escape character is needed.

"Double quote ("")"

This example uses two double quotes, one as the literal double quote and the other as the escape character.

Column name is:

"Column"1

"Column""1"

This example uses two double quotes, one as the literal double quote and the other as the escape character.

Not supported.