Enable quoted identifiers property

To maintain the case-sensitivity of Oracle schema object names, you can manually enter double quotation marks around each name or set the Enable quoted identifiers property to Yes.

Usage

The Oracle connector automatically generates and runs SQL statements when either of these properties are set:

Generate SQL at runtime is set to Yes.
Table action is set to Create, Replace, or Truncate.

In these cases, the generated SQL statements contain the names of the columns and the name of the table on which to perform the operation. The column names in the database table match the column names that are specified on the link for the stage. The table name matches the table that is specified in the Table name property.

By default, the Oracle database converts all object names to uppercase before it matches the names against the Oracle schema object names in the database. If the Oracle schema object names all use uppercase, then how you specify the names in the connector properties, by using uppercase, lowercase, or mixed case, has no effect on schema matching. The names will match. However, if the Oracle schema object names use all lowercase or mixed case, you must specify the names exactly as they appear in the Oracle schema. In this case, you must manually enter double quotation marks around each name or set the Enable quoted identifiers property to Yes.

Examples

For example, assume that the Enable quoted identifiers property is set to No and that you want to create a table that contains one column and use a SELECT statement that references the column. The statement CREATE TABLE Table2b (Col1 VARCHAR2(100)) creates the table TABLE2B, which contains one column, COL1. The statement SELECT Col1 FROM tABLE2B runs successfully because the Oracle database automatically changes the Col1 and tABLE2B names in the statement to the uppercase versions COL1 and TABLE2B and matches these names with the actual schema object name and column name in the database.

Now assume that you use the statement CREATE TABLE "Table2b" ("Col1" VARCHAR2(100)) to create the table Table2b, which contains one column, Col1. Case-sensitivity is preserved because you enclosed the table and column names in double quotation marks. Now the statement SELECT Col1 FROM tABLE2B fails because the Oracle database automatically changes Col1 and Table2b to the uppercase versions COL1 and TABLE2B, and these names do not match the actual names, Col1 and Table2b, in the database. However, the statement SELECT "Col1" FROM "Table2b" runs successfully.

Now consider an example that illustrates the effect of the Enable quoted identifiers property on table and column creation. Assume that the Table name property is set to john.test. The input link contains the columns Col1, Col2, and Col3, all of which are of VarChar(10) data type. The Table action property is set to Create. If the Enable quoted identifiers property is set to No, the connector generates and runs these SQL statements at runtime and creates the table JOHN.TEST with the columns COL1, COL2, and COL3:

CREATE TABLE john.test(Col1 VARCHAR2(10),Col2 VARCHAR2(10),Col3 VARCHAR2(10));

However, if the Enable quoted identifiers property is set to Yes, the connector generates and runs this SQL statement at runtime and creates the table john.test with the columns Col1, Col2, and Col3:

CREATE TABLE "john"."test"("Col1" VARCHAR2(10),"Col2" VARCHAR2(10),
	"Col3" VARCHAR2(10));