I believe it is acting appropriately. You are trying to load a two-"character" byte filed into three-byte "character" field. Loader, if you don't terminate by whitespace or nulls, will add the blank into the field because it is character.
Thus, you have two options: 1. Change the field to numeric. 2. Trim the data before it is loaded. Check the third position to see if it is a space or null; if so, only load n positions of data. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -----Original Message----- Sent: Wednesday, August 21, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject: RE: PL/SQl question Dennis, In your PL/SQL program, did you try the RTRIM(date_field,' ') command? I know that TRIM is new, but I thought it needed additional parameters to tell it what to trim. Tom Mercadante Oracle Certified Professional -----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. [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: Mercadante, Thomas F 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). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen 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).
