Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problem with joining char field to varchar2 field
The parameter, which I found useful and a pain in the ___ with our =
TurboImage interconnects strips spaces off the ends of char fields as =
they are pulled from the database, like an rtrim. So it effects joins, =
and displays, but not storage. The problem is that if the trailing =
spaces ARE significant you can get into trouble.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]
Sent: Wednesday, May 05, 2004 11:53 AM
To: oracle-l_at_freelists.org
Subject: RE: Problem with joining char field to varchar2 field=20
Thanks. I saw this parameter yesterday but couldn't test it (no database =
=3D
to bounce).
So does it affect how the comparison works or does it simply affect the =
=3D
way char columns get stored (it trims the space)?
Wondering if you insert 'aa' in a char(5) column, what would be the =3D length(column) and udump(column)!
Thanks
Waleed
-----Original Message-----
From: Goulet, Dick [mailto:DGoulet_at_vicr.com]
Sent: Wednesday, May 05, 2004 11:18 AM
To: oracle-l_at_freelists.org
Subject: RE: Problem with joining char field to varchar2 field=3D20
Yes.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM]
Sent: Wednesday, May 05, 2004 9:53 AM
To: oracle-l_at_freelists.org
Subject: RE: Problem with joining char field to varchar2 field=3D3D20
It does not say in the doc that blank_trimming affects joins. Did you test it?
Regards,
Waleed
-----Original Message-----
From: Goulet, Dick [mailto:DGoulet_at_vicr.com]
Sent: Wednesday, May 05, 2004 9:35 AM
To: oracle-l_at_freelists.org
Subject: RE: Problem with joining char field to varchar2 field=3D3D3D20
Warren,
There is a structural difference between a varchar and char datatypes. =
=3D
=3D3D
=3D3D3D
=3D3D3D3D
Char's will get padded to the full size of the field whereas Varchar's =
=3D
=3D3D
=3D3D3D
=3D3D3D3D
won't. To explain the differences in Windoze vs. AIX check the init =
=3D
=3D3D
=3D3D3D
=3D3D3D3D
file to see if BLANK_TRIMMING is true. Something in the past tells me =
=3D
=3D3D
=3D3D3D
=3D3D3D3D
that the default on Windoze is true and Unix is false.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Warren Homer [mailto:wazhomer_at_hotmail.com]
Sent: Tuesday, May 04, 2004 8:51 PM
To: oracle-l_at_freelists.org
Subject: Problem with joining char field to varchar2 field=3D3D3D3D20
Hi all,
we are currently running Oracle 9.2.0.1.0 on AIX 4.3.
I am trying to join two tables on a varchar2(50) field and a CHAR(30) =
=3D
=3D3D
=3D3D3D
=3D3D3D3D
field=3D3D3D3D20
where both are defined as NOT NULL. Although these two fields have the =
=3D
=3D3D
=3D3D3D
=3D3D3D3D
same=3D3D3D3D20
value inserted Oracle interprets these two columns to be NOT equal. I =
=3D
=3D3D
=3D3D3D
=3D3D3D3D
have=3D3D3D3D20
tried the same SQL test on a Window 2000 Oracle 9.2.0.3.0 =3D3D3D
environment=3D3D3D3D20
without any problems. Is this a bug in Oracle 9201 or a problem with AIX =
=3D
=3D3D
=3D3D3D
=3D3D3D3D
?=3D3D3D3D20
Thanks in advance for any help.
The following shows the process I have used to create the problem.
CREATE TABLE PTMBT01
(COL1_ID NUMBER(10) NOT NULL , COL2 CHAR(30) NOT NULL)
(COL1_ID NUMBER(10) NOT NULL , COL2 VARCHAR2(50) NOT NULL)
SELECT *
FROM PTMBT01 T01
, PTMBT02 T02
WHERE T01.COL2 =3D3D3D3D3D T02.COL2;
Thanks in advance,
Warren.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Wed May 05 2004 - 10:59:46 CDT