Duplicate Parameter Names

Consider this SQL statement:

SELECT * FROM t1 WHERE c1=:a AND c2=:a AND c3=:b AND c4=:a;

Traditionally in TimesTen, multiple instances of the same parameter name in a SQL statement are considered to be multiple occurrences of the same parameter. When assigning parameter numbers to parameters, TimesTen assigns parameter numbers only to the first occurrence of each parameter name. The second and subsequent occurrences of a given name do not get their own parameter numbers. In this case, a TimesTen application binds a value for every unique parameter in a SQL statement. It cannot bind different values for different occurrences of the same parameter name nor can it leave any parameters or parameter occurrences unbound.

In Oracle Database, multiple instances of the same parameter name in a SQL statement are considered to be different parameters. When assigning parameter numbers, Oracle Database assigns a number to each parameter occurrence without regard to name duplication. An Oracle database application, at a minimum, binds a value for the first occurrence of each parameter name. For the subsequent occurrences of a given parameter, the application can either leave the parameter occurrence unbound or it can bind a different value for the occurrence.

The following table shows a query with the parameter numbers that TimesTen and Oracle Database assign to each parameter.

Query TimesTen parameter number Oracle Database parameter number

SELECT *

n/a

n/a

FROM t1

n/a

n/a

WHERE c1=:a

1

1

AND c2=:a

1

2

AND c3=:b

2

3

AND c4=:a;

1

4

The total number of parameter numbers for TimesTen in this example is 2. The total number of parameters for Oracle Database in this example is 4. The parameter bindings provided by an application produce different results for the traditional TimesTen behavior and the Oracle Database behavior.

You can use the DuplicateBindMode general connection attribute to determine whether applications use traditional TimesTen parameter binding for duplicate occurrences of a parameter in a SQL statement or Oracle-style parameter binding. Oracle-style parameter binding is the default.