Re: Update/Select on CHAR Column Behaves Differently In Stored Procedure

Jesse wrote:

> Ok, first, let me say that I didn't design this table, so don't blame
> me for using CHAR when a VARCHAR2 would have worked just fine. I'm
> using Oracle 9.2.0.4 on Win2k3 Server.
>
> My problem is this. I have a table with CHAR(11) column
> "utstandardfilename_text" where the maximum value length is never
> greater than 8 (why not CHAR(8) or VARCHAR(x); who knows; again, not my
> design...). I have a stored procedure that's attempting to update
> another column based on this column. If I run the actual update just
> as a standard SQL statement, it works. If I run it in the stored
> procedure it doesn't. The reason being that (for example) it's trying
> to match "A0000123" to "A0000123 " and failing. In standard SQL,
> Oracle ignores the whitespace in the actual table value and returns a
> result. In PL/SQL, it's not matching.
>
> Here's the procedure:
>
> PROCEDURE update_dim_data_flag (
> stubeid IN VARCHAR2,
> nvalue IN NUMBER,
> ntubeisstandard IN NUMBER
> )
> IS
> BEGIN
> IF ntubeisstandard = 1
> THEN
> UPDATE zkora.ttr_utstandardsrun
> SET dimdatauploaded = nvalue
> WHERE utstandardfilename_text = stubeid;
> ELSE
> UPDATE zkora.ttr_fueltubedata
> SET dimdatauploaded = nvalue
> WHERE fueltubebarcode_text = stubeid;
> END IF;
> END update_dim_data_flag;
>
> My guess is that the problem lies in the fact that the stubeid variable
> is defined as a VARCHAR2. Am I right on this? Is my only alternative
> to change the procedure to either use a CHAR or to use "WHERE
> TRIM(utstandardfilename_text) = stubeid;"? (note: changing the column
> to either a VARCHAR2 or CHAR(8) is no good; there are other apps that
> use the data and I would have to verify that it didn't have an impact
> on them).
>
> Basically, I think that I'm stuck and that I'll have to change the
> procedure (and re-verify the app, paperwork, etc.) The only thing that
> is really throwing me is that it didn't behave this way during
> testing/verification, and I can't figure out why (the test environment
> was an exp/imp of the prod environment). The answer's probably staring
> my right in the face, but it's been a long week if you know what I
> mean.
>
> Thanks.
>
> Jesse
>

UPDATE ...
SET ...
WHERE TRIM(some_column) = TRIM(the_other_column);

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)