Forum Stats

  • 3,770,605 Users
  • 2,253,140 Discussions
  • 7,875,506 Comments

Discussions

ADO DataType/Length changes for column based on using = or LIKE

A client has updated to 12c and we've started to see weird behavior.

When performing the following SQL, if the column is part of the where clause ('LABEL') and in a equals/= condition, it changes the ADO datatype and length for said column. E.g:

SELECT * FROM TABLE_1 a WHERE a.LABEL = 'SOMETHING' AND a.CATEGORY IN (SELECT cat.Category_ID FROM CATEGORIES cat WHERE cat.GROUP = '1' AND cat.LEVEL > 0 GROUP BY cat.Category_ID) ORDER BY TABLE_ID

The column is returned as a type 129/adChar, and the length is the length of the contents, not the actual column length. The column is defined as:

LABEL     VARCHAR2(255)

But if we replace the equals with a LIKE, or change the where condition to use the column TABLE_ID, we get the correct results:

SELECT * FROM TABLE_1 a WHERE a.LABEL LIKE 'SOMETHING' AND a.CATEGORY IN (SELECT cat.Category_ID FROM CATEGORIES cat WHERE cat.GROUP = '1' AND cat.LEVEL > 0 GROUP BY cat.Category_ID) ORDER BY TABLE_ID

The column is returned as a type 200/adVarChar and length of 255.

Any idea why this is occurring?

Oracle DB: 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

Oracle Client: 11.2.0.4.0