Oracle ORDS returns RAW columns as base64 encoding

Those days, I was building a REST API service for retrieving the columns of a checksum table.

The table structure was pretty simple:

CREATE TABLE "DEMO" (
  "PATH"     VARCHAR2(500 CHAR),
  "MD5_HASH" RAW(16)
);

INSERT INTO DEMO (
  PATH,
  MD5_HASH
) VALUES (
  'bundle.xml',
  '70D53BE37073B31347EE42B622EA4A75'
);

COMMIT;

So, as you can see, very simple table with a path to a file and its checksum.

Now, create a simple ORDS service to just show the table contents:

BEGIN

  ORDS.DEFINE_MODULE(
      p_module_name    => 'test',
      p_base_path      => '/test/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'test',
      p_pattern        => 'test',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'test',
      p_pattern        => 'test',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 'SELECT * FROM DEMO');
        
  COMMIT;

END;
/

And now, getting the data from the endpoint:

$ curl -s -S -k --request GET https://xxx.yyy.zzz.adb.us-phoenix-1.oraclevcn.com/ords/test/test/test | jq
{
  "items": [
    {
      "path": "bundle.xml",
      "md5_hash": "cNU743BzsxNH7kK2IupKdQ=="
    }
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 1,
  "links": [
    {
      "rel": "self",
      "href": "https://xxx.yyy.zzz.adb.us-phoenix-1.oraclevcn.com/ords/test/test/test"
    },
    {
      "rel": "describedby",
      "href": "https://xxx.yyy.zzz.adb.us-phoenix-1.oraclevcn.com/ords/test/metadata-catalog/test/item"
    },
    {
      "rel": "first",
      "href": "https://xxx.yyy.zzz.adb.us-phoenix-1.oraclevcn.com/ords/test/test/test"
    }
  ]
}

So, as you can see, the column md5_hash was converted from 70D53BE37073B31347EE42B622EA4A75 to cNU743BzsxNH7kK2IupKdQ==.

Why? In the official doc, you can see:

So this is expected. The reason RAWs are converted is mainly that the base64 needs 1.33 characters per byte. Hex encoding requires 2 characters per byte. Base64 is the common encoding of binary values transported as text in various protocols.

How do we convert back? If using shell, you could simply:

$ echo "cNU743BzsxNH7kK2IupKdQ==" | base64 -d | hexdump -v -e '/1 "%02X"'
70D53BE37073B31347EE42B622EA4A75

Now, what if instead, you want to make ORDS not convert RAW to BASE64?

The only way to do that is if in the SQL layer you return a VARCHAR2 instead of a RAW, and this can be easily achievable through the RAWTOHEX function.

BEGIN

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'test',
      p_pattern        => 'test',
      p_method         => 'GET',
      p_source_type    => 'json/collection',
      p_items_per_page => 25,
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 'SELECT PATH, RAWTOHEX(MD5_HASH) MD5_HASH FROM DEMO');
        
  COMMIT;

END;
/

And finally, getting the data from the endpoint:

$ curl -s -S -k --request GET https://xxx.yyy.zzz.adb.us-phoenix-1.oraclevcn.com/ords/test/test/test | jq
{
  "items": [
    {
      "path": "bundle.xml",
      "md5_hash": "70D53BE37073B31347EE42B622EA4A75"
    }
  ],
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "count": 1,
  "links": [
    {
      "rel": "self",
      "href": "https://xxx.yyy.zzz.adb.us-phoenix-1.oraclevcn.com/ords/test/test/test"
    },
    {
      "rel": "describedby",
      "href": "https://xxx.yyy.zzz.adb.us-phoenix-1.oraclevcn.com/ords/test/metadata-catalog/test/item"
    },
    {
      "rel": "first",
      "href": "https://xxx.yyy.zzz.adb.us-phoenix-1.oraclevcn.com/ords/test/test/test"
    }
  ]
}

+2