| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/Sql question
Sounds like in the table the field c.marketcode is a char(3) instead of
varchar2(3).
Raj
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
Sent: Wednesday, August 21, 2002 10:28 AM
To: Multiple recipients of list 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.
dwilliams_at_lifetouch.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM 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: ListGuru_at_fatcity.com (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).Received on Wed Aug 21 2002 - 11:33:28 CDT-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.com 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: ListGuru_at_fatcity.com (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).
- text/plain attachment: InterScan_Disclaimer.txt
 |
 |