OCCI handling different types of char string variables

Issue Background :

An old application, the original application is written in proc, 9i Library, now the application needs to use OCCI instead, which has a query logic: SELECT ... where upper (state) =upper (: 1).

(Please do not tangle in the Where condition the state field uses the upper function because the table has a small amount of data and has a long history and is not indexed.) )

The state field type defined in the corresponding table is char (3), but the value of the query condition variable here may be two bits, for example ' NY '.

Phenomenon :

1. Use Sqlplus to perform select ... where Upper (state) =upper (: 1) can be displayed normally.

2. Use SQL Developer to execute SELECT ... where upper (state) =upper (: 1) can be displayed normally.

3. Use proc to perform, can display normally.

4. use Occi mode, execute, show as empty .

Solution :

For a way to use OCCI, rewrite it to:

1. Select ... where trim (upper (state)) = Trim (upper (: 1));

2. Select ... where upper (state) = Upper (Rpad (: 1, 3, '));

principle Inference :

1. First, the biggest difference between char and VARCHAR2 type is that char is a fixed length type, VARCHAR2 is an indefinite length type, there are many introductions on the web including official documents, in simple case, it is:

CREATE TABLE Test (

A char (25),

b varchar2 (25)

);

INSERT into test values (' A ', B ');

The A field stores "a+24 spaces", and the B field stores "B".

Can be verified from select a, Length (a), B, Length (b) from test;

That is, char consumes the most storage space, and VARCHAR2 only stores the space that is actually occupied.

2. From http://www.itpub.net/thread-1014651-1-1.html This post can be seen, and the same problem. inference is caused by a bug that is Occi.

Although the Occi document was turned over, no explanation was found for the problem,but from the Oracle Official document to fill the space comparison string semantic description, you can see some clues:
blank-padded Comparison semantics
If the values have different lengths and thenOracle first adds blanks to the end of the shorter one so their lengths is equal. Oracle then compares the values of character by character-to-the-first character that differs. The value with the greater character on the first differing position is considered greater.
If the values have no differing characters and then they is considered equal. This rule means and the values is equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only if both values in the comparison is either expressions of datatype C HAR, NCHAR, text literals, or values returned by the USER function.

nonpadded Comparison semantics
Oracle compares-character by character-to-the-first character that differs. The value with the greater character in, position is considered greater. If the values of different length are identical up to the end of the shorter one, then the longer value is considered Grea ter. If The values of equal length have no differing characters and then the values is considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison has the datatype VARCHAR2 or NVA RCHAR2.

that is, for a char, nchar type of string comparison, Oracle will first automatically filled the space, and then one character to compare, not because the number of spaces different think the two different, and this process should not be a simple trim () operation, Because if the field has an index, it will still be used.

forstring comparisons of VARCHAR2, NVARCHAR2 types, because they do not automatically store spaces, and if there are spaces, they are also stored as meaningful, so there is no such problem.

In summary, for a char type, it should not be used as a basis for comparison, unless the Where a = Trim (' a '), the value is handled artificially, so there is reason to suspect that Occi to the char type string comparison , at least, and other terminal query logic is different, as to whether it is a bug, need to see if there is an official explanation .

OCCI handling different types of char string variables