Update/Select on CHAR Column Behaves Differently In Stored Procedure

From: Jesse <jesjdavis_at_gmail.com>
Date: 5 May 2005 07:39:27 -0700
Message-ID: <1115303967.585241.275350@f14g2000cwb.googlegroups.com>

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 Received on Thu May 05 2005 - 09:39:27 CDT