Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!f14g2000cwb.googlegroups.com!not-for-mail
From: "Jesse" <jesjdavis@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Update/Select on CHAR Column Behaves Differently In Stored Procedure
Date: 5 May 2005 07:39:27 -0700
Organization: http://groups.google.com
Lines: 56
Message-ID: <1115303967.585241.275350@f14g2000cwb.googlegroups.com>
NNTP-Posting-Host: 147.72.234.6
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1115303973 30582 127.0.0.1 (5 May 2005 14:39:33 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 5 May 2005 14:39:33 +0000 (UTC)
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: f14g2000cwb.googlegroups.com; posting-host=147.72.234.6;
   posting-account=WdLmNQ0AAAAJ7n6mnpHR3z7iQFO631ek
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:242067

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