0

Is there a way i can encode a string from a select statement in oracle.

i.e. 

SELECT base64encode('mystring') FROM dual; --this doesn't work

base64 or any other encoding mechanism is welcomed. one that can be decoded

Omari Victor Omosa
  • 2,814
  • 2
  • 24
  • 46
  • 1
    "Hash" has the distinct characteristic that it **cannot** be "decoded". Encoding/decoding is quite different from hashing; both are very important and each has numerous important applications, but their areas of application do not overlap. I will edit your post to remove any reference to "hashing", as obviously you want to encode and decode, not to hash. –  Feb 04 '20 at 14:05
  • Alright @mathguy .Understood – Omari Victor Omosa Feb 04 '20 at 14:08

1 Answers1

3

Oracle has the UTL_ENCODE package, including the BASE64_ENCODE and BASE64_DECODE functions. Both functions work on RAW data type (both the input and the return value are RAW); so, if you want to use this for character data types, like VARCHAR2 or perhaps CLOB, you need to use additional conversions - as found in the UTL_RAW package; functions like CAST_TO_RAW and CAST_TO_VARCHAR2.

Here is a brief example. I start with the string 'mathguy' (in VARCHAR2 data type); first I convert it to RAW, and then I encode it to base64. Then, to demonstrate the decoding step, I apply the BASE64_DECODE function to the output from the first query, and convert the resulting RAW value back to VARCHAR2; the result of the second query should be the input of the first. Let's see:

select utl_encode.base64_encode(utl_raw.cast_to_raw('mathguy')) as encoded
from   dual;

/* ENCODING */

ENCODED
------------------------
625746306147643165513D3D


/* DECODING */

select utl_raw.cast_to_varchar2(
         utl_encode.base64_decode('625746306147643165513D3D')) as decoded
from   dual;

DECODED
-----------
mathguy

Edit Please see Wernfried Domscheit's Comment below: the RAW data type is limited to 2000 bytes, or 32767 bytes with extended max string size. So this may not work as shown here for very long input strings.

  • 1
    Functions `utl_encode.base64_encode()/utl_encode.base64_decode()` work only up to 32k characters. In case you need more see https://stackoverflow.com/questions/3804279/base64-encoding-and-decoding-in-oracle/3806265#40852152 – Wernfried Domscheit Feb 04 '20 at 14:22
  • @WernfriedDomscheit - Good point, but the limit is not imposed by the functions. Rather, the `RAW` datatype itself is limited to 2000 bytes, or 32k bytes with extended max string size. Note that the limit is 32k bytes; this may be less than 32k characters, in multi-byte character sets. It is also possible that the encoding of a < 2000 B (or < 32 kB) `RAW` results in a `RAW` longer than the limit... –  Feb 04 '20 at 14:27
  • @WernfriedDomscheit lucky for now i wanted a simple one since i will be encoding only 10-15 characters. It is helpful you have pointed that out aswell – Omari Victor Omosa Feb 04 '20 at 14:27