RE: PL/SQl question

Check the definition of table C. It sounds like it is defined as CHAR(3) instead of VARCHAR2(3). I would also check the PL/SQL for using CHAR instead of VARCHAR2 for storing the value -- the trim should have eliminated this problem if it was put in the right place.

Kevin Kennedy
First Point Energy Corporation

If you take RAC out of Oracle you get OLE! What can this mean?

-----Original Message-----
Sent: Wednesday, August 21, 2002 7: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: kkennedy
  INET: kkennedy_at_firstpoint.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).