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

Sorry, the SQL statement was simply the same core statement, only no variable:

UPDATE zkora.ttr_utstandardsrun

   SET dimdatauploaded = 1
 WHERE utstandardfilename_text = 'A0000J6W'

I am guessing as you suggested that there is an implicit conversion going on here (i.e. Oracle is taking the literal and converting it to CHAR for a fixed length comparison). Apparently the rules of the fixed length comparison are such that when both sides of the = are CHARS, it doesn't care about whitespace. I say this because the following query also works (from another application);

SELECT * FROM zkora.ttr_utstandardsrun
WHERE utstandardfilename_text = 'A0000J6W '

as does...

SELECT * FROM zkora.ttr_utstandardsrun

WHERE utstandardfilename_text = 'A0000J6W                     '

In other words, no matter how many spaces I pad the value with, it still returns the appropriate record. Now the moment I make that literal a VARCHAR2 variable in PL/SQL, it very much cares about the spaces. Specifically, it requires 3 spaces to make it return anything (myvar = 'A0000J6W '). As my app isn't adding these three spaces when it calls the procedure, the update fails.

My biggest issue is that it didn't happen this way in the test environment (again an exp/imp of the prod system). The way I see it, I either had an invalid data set or something changed in the procedure from the time I tested to the time I implemented (code should have been locked, so this shouldn't have happened).

Thanks.

Jesse Received on Thu May 05 2005 - 12:38:47 CDT