Is the field in question in table C defined as CHAR or VARCHAR2? If CHAR
that is why it is blank padded. Check datatype of variables
in pl/sql
Rick
DENNIS WILLIAMS
<DWILLIAMS@life To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
touch.com> cc:
Sent by: Subject: PL/SQl question
[EMAIL PROTECTED]
m
08/21/2002
10:28 AM
Please respond
to ORACLE-L
> I have a question for from one of my developers related to PL/SQL and how
> data is loaded.
>
> I have a field (marketcode) that is defined as VARCHAR2(3).
>
> I have a problem when I try to load the value of '20' into this field.
> All values with three characters work fine. The problem is when the
value
> is less then 3 characters.
>
> When tables A and B have data loaded into this field using SQL/Loader the
> resulting value in the field appears to me as '20' with the third
position
> =null.
>
> I have a separate PL/SQL process that loads this field into table C.
> When PL/SQL populates this same value into this field the field appears
to
> me as '20' with the third position = space. I can't use SQL/Loader for
> this table as the data needs to be massaged before loading into Oracle.
> Thus when you try to link the tables together it does not find a match.
>
> select A.marketcode, C.marketcode
> from tableA A, tableC C
> where A.marketcode=C.marketcode;
>
> (this returns 0 records)
>
> If I change the SQl statement to the following:
>
> select A.marketcode, C.marketcode
> from tableA A, tableC C
> where A.marketcode=trim(C.marketcode);
>
> (it correctly matches these up)
>
> Things I have tried to remedy this problem:
> 1) I have tried to modify my PL/SQL program to put a TRIM statement
> around the marketcode field when I populate table C. This did not work.
> 2) I have tried to check the 3rd position and if it is = space then I
set
> the third position to null. But the field in Oracle is still a space
when
> the program is finished.
>
> Does anyone have any thoughts on how I can properly output this field
from
> Pl/SQl so it will match the data loaded via SQL/Loader? Thanks.
>
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
