RE: PL/SQl question

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:	Stephen.Karniotis_at_Compuware.com
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.
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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).



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: Stephen_Karniotis_at_compuware.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).