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

mathguy
  • 42,476
  • 6
  • 23
  • 50
Omari Victor Omosa
  • 2,682
  • 1
  • 23
  • 41
  • 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. – mathguy 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.

mathguy
  • 42,476
  • 6
  • 23
  • 50
  • 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... – mathguy 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