Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!newscon02.news.prodigy.com!prodigy.net!news.glorb.com!postnews.google.com!g14g2000cwa.googlegroups.com!not-for-mail
From: "Jesse" <jesjdavis@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Update/Select on CHAR Column Behaves Differently In Stored Procedure
Date: 5 May 2005 11:35:27 -0700
Organization: http://groups.google.com
Lines: 26
Message-ID: <1115318126.856705.260820@g14g2000cwa.googlegroups.com>
References: <1115303967.585241.275350@f14g2000cwb.googlegroups.com>
   <96kk71dud61gpdui3g0r3dsbb64n7l2mc9@4ax.com>
   <1115314726.991159.214430@f14g2000cwb.googlegroups.com>
   <lunk71d8koo3i4f24kk3nfnnv0i1r8qiil@4ax.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 1115318132 26096 127.0.0.1 (5 May 2005 18:35:32 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 5 May 2005 18:35:32 +0000 (UTC)
In-Reply-To: <lunk71d8koo3i4f24kk3nfnnv0i1r8qiil@4ax.com>
User-Agent: G2/0.2
Complaints-To: groups-abuse@google.com
Injection-Info: g14g2000cwa.googlegroups.com; posting-host=147.72.234.6;
   posting-account=WdLmNQ0AAAAJ7n6mnpHR3z7iQFO631ek
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:242100

Yes, it fails.  Keep in mind that the hard-coded literal was just as an
example.  It was based on a query from another app (written in VB).  In
that app (outsourced), most of the SQL is in the VB code, and doesn't
use too many Oracle procedures (I know; worst-case performance).  My
point was that I couldn't change the column from a CHAR to a VARCHAR2
to fix my app (and to save some space) without causing the other app to
fail.  It does a select where it's looking for utstandardfilename_text
= 'A0000J6W  ' (example only, the value is actually in a VB string
variable; note two spaces after W, not three).  If the column is a
CHAR, the select will return a record.  If it's a VARCHAR2, it won't
(i.e. I will have broken the other app).

It's a bit hard to see without me posting all the code from both apps,
but long story short, I need to change my procedure (which isn't using
any hard coded literals) to trim the utstandardfilename_text column
when it does the update (i.e. WHERE TRIM(utstandardfilename_text) =
param).

If anything, I've learned a lot in the past 24 hours about the
differences in the way Oracle treats CHARs and VARs...

Thanks again.  I continue to be amazed at how responsive folks are in
this group.

Jesse