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" } ] }Have you enjoyed? Please leave a comment or give a 👍!