Prev: Trasaction between procedure calls.
Next: Differences in BULK COLLECT between 9i and 10g?
From: Veeru71 on 6 Jul 2008 02:59
We are trying to upgrade our database from Oracle 9i to 10g.

We have a CHAR(30) column in a table.

When we perform string comparisons using '=' operator (Eg.....where
col = 'XYZ' ) the trailing spaces are ignored, which is perfectly
fine (both in 9i & 10g).

When we use LIKE operator without specifying any wildcard char in
Oracle 9i (Eg.....where col like 'XYZ'), the trailing spaces are
being ignored and so the behavior of "LIKE without wildcard char" is
identical to direct '=' operator. But in 10g, the trailing spaces in
the column value are making a difference.

I know that it doesn't make much sense to use LIKE operator when there
is no wildcard char but the SQL is dynamically generated based on some
complex conditions in the app.

So my question is what is the expected behaviour of LIKE operator on
CHAR column with regard to trailing spaces when there is no wildcar
character?

Is it possible to simulate 9i behavior in 10g by setting any config
param ?

Thanks
Murty
From: sybrandb on 6 Jul 2008 08:06
On Sat, 5 Jul 2008 23:59:04 -0700 (PDT), Veeru71 <m_adavi(a)hotmail.com>
wrote:

>We are trying to upgrade our database from Oracle 9i to 10g.
>
>We have a CHAR(30) column in a table.
>
>When we perform string comparisons using '=' operator (Eg.....where
>col = 'XYZ' ) the trailing spaces are ignored, which is perfectly
>fine (both in 9i & 10g).

This is incorrect. XYZ is stored with trailing spaces. All text stored
in CHAR columns is always stored padded to the maximum lenght.
The comparison is using fixed length semantics, so the right hand
expression is automatically padded to 30 characters.
This is why it works.
Oracle automatically switches between variable length semantics and
fixed length semantics.

>
>When we use LIKE operator without specifying any wildcard char in
>Oracle 9i (Eg.....where col like 'XYZ'), the trailing spaces are
>being ignored and so the behavior of "LIKE without wildcard char" is
>identical to direct '=' operator.
This is again incorrect. Fixed length semantics being used.
LIKE without wildcard char is always identical to using the equality
operator.

But in 10g, the trailing spaces in
>the column value are making a difference.
>

If that is true, which I doubt, it is either described in the New
Features Manual, or a bug which has been removed in a patch release.

>I know that it doesn't make much sense to use LIKE operator when there
>is no wildcard char but the SQL is dynamically generated based on some
>complex conditions in the app.

This is the #1 recipe for unscalable applications.
>
>So my question is what is the expected behaviour of LIKE operator on
>CHAR column with regard to trailing spaces when there is no wildcar
>character?
>
>Is it possible to simulate 9i behavior in 10g by setting any config
>param ?
>
It is pretty daft to use CHAR columns where a VARCHAR2 would have been
way more appropiate. If you don't want to find whether this is a bug
in 10g which has been fixed in the meantime your only choice is to set
the compatible parameter to somthing like 9.2.0.0, *provided* it
wasn't set to 10..... when you created the database.
Obviously you will now have a 9i database in a 10g guise, and no 10g
new features will be working.


>Thanks
>Murty


--
Sybrand Bakker
Senior Oracle DBA
From: Ana C. Dent on 6 Jul 2008 10:33
Veeru71 <m_adavi(a)hotmail.com> wrote in news:df183ca6-bbc9-4569-b4c8-
5b9287cbe365(a)f36g2000hsa.googlegroups.com:

http://www.dbforums.com/showthread.php?t=1631914
From: William Robertson on 7 Jul 2008 11:23
On Jul 7, 2:29 pm, Veeru71 <m_ad...(a)hotmail.com> wrote:
> you are right, we should have used
> varchar2 cols instead of char cols in the first place but this is a
> legacy app.

I wonder why they used CHAR even back in those far-off legacy days.
There has never been a good reason to use a blank-padded character
type in any version of Oracle. This anti-feature was introduced in
Oracle 6 for ANSI compatibility - you weren't actually expected to use
it.
From: fitzjarrell on 7 Jul 2008 15:11
On Jul 7, 10:23 am, William Robertson <williamr2...(a)googlemail.com>
wrote:
> On Jul 7, 2:29 pm, Veeru71 <m_ad...(a)hotmail.com> wrote:
>
> > you are right, we should have used
> > varchar2 cols instead of char cols in the first place but this is a
> > legacy app.
>
> I wonder why they used CHAR even back in those far-off legacy days.
> There has never been a good reason to use a blank-padded character
> type in any version of Oracle. This anti-feature was introduced in
> Oracle 6 for ANSI compatibility - you weren't actually expected to use
> it.

But, if it's there, it will be used ... be it daft or not.


David Fitzjarrell
 |  Next  |  Last
Pages: 1 2
Prev: Trasaction between procedure calls.
Next: Differences in BULK COLLECT between 9i and 10g?