Re: PL/SQl question

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_at_life       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    touch.com>            cc:                                                                       
                    Sent by:              Subject:     PL/SQl question                                              
                    root_at_fatcity.co                                                                                 
                    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.
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: 
  INET: Rick_Cale_at_teamhealth.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).