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

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