TRANSLATE function doesn't work correctly?

From: Michael Carmack <mcarmack_at_freenet.columbus.oh.us>
Date: 1997/06/11
Message-ID: <5nn245$9ef@login.freenet.columbus.oh.us>#1/1

I'm scratching my head over what seems to be buggy behavior in the TRANSLATE function in Oracle 7.1.5. I'm using it to implement some simple encryption, but the decrypting doesn't seem to always work correctly. Here's what I mean:

Here's a sample encryption statement:

1 select TRANSLATE(

  2     'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
  3     'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789',
  4      '3EOX4KA7NVFJWPCS2GTH158DRLQYMGB9ZU0I'
  5          ) "Encrypted String"

6* from dual
SQL> / Encrypted String

3EOX4KA7NVFJWPCS2GTH158DRLQYMGB9ZU0I The result is exactly what it should be. But if I turn around and try to decrypt that result back into its original form, there is an anomoly:

1 select TRANSLATE(

  2      '3EOX4KA7NVFJWPCS2GTH158DRLQYMGB9ZU0I',
  3      '3EOX4KA7NVFJWPCS2GTH158DRLQYMGB9ZU0I',
  4     'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
  5          ) "Decrypted String"

6* from dual
SQL> / Decrypted String

ABCDEFGHIJKLMNOPQ3STUVWXYZ0123456789
^
^

As you can see, the letter 'R' has not been correctly decrypted, i.e. the character '2' is not being translated back into an 'R' as specified in the statement.

Has anyone else experienced this odd behavior? Is there a known workaround (besides upgrading to 7.3)?

-- 
========================================================================
"Villains, I say to you now:       |   Mike Carmack
 KNOCK OFF ALL THAT EVIL!"         |   Vulcan Dragon -==(UDIC)==-
    S P O O N !!!!    - The Tick   |   mcarmack_at_freenet.columbus.oh.us

Received on Wed Jun 11 1997 - 00:00:00 CDT