Hi,
I created a function to get the email attachment from gmail api, the function return the json data in clob as below
{
"size": 4596,
"data": "JVBERi0xLjQKJf____8KMTMgMCBvYmoKPDwvTGVuZ3RoIDI0NzYKL1N1YnR5cGUgL1hNTAovVHlwZSAvTWV0YWRhdGEKPj4Kc3RyZWFtCjw_eHBhY2tldCBiZWdpbj0n77u_JyBpZD0nVzVNME1wQ2VoaUh6cmVTek5UY3prYzlkJz8-Cjx4OnhtcG1ldGEgeDp4bXB0az0iMy4xLTcwMSIgeG1sbnM6eD0iYWRvYmU6bnM6bWV0YS8iPgogIDxyZGY6UkRGIHhtbG5zOnJkZj0iaHR0cDovL3d3dy53My5vcmcvMTk5OS8wMi8yMi1yZGYtc3ludGF4LW5zIyI-CiAgICA8cmRmOkRlc2NyaXB0aW9uIHJkZjphYm91dD0iIiB4bWxuczp4bXA9Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC8iPgogICAgICA8eG1wOkNyZWF0ZURhdGU-MjAxNS0xMC0yN1QwNjozMTo1OFo8L3htcDpDcmVhdGVEYXRlPgogICAgICA8eG1wOkNyZWF0b3JUb29sPk5pdHJvIFBybyA5ICAoOS4gMC4gNi4gMjApPC94bXA6Q3JlYXRvclRvb2w-CiAgICAgIDx4bXA6TW9kaWZ5RGF0ZT4yMDE1LTEwLTI3VDA2OjMxOjU5WjwveG1wOk1vZGlmeURhdGU-CiAgICAgIDx4bXA6TWV0YWRhdGFEYXRlPjIwMTUtMTAtMjdUMDY6MzE6NTlaPC94bXA6TWV0YWRhdGFEYXRlPgogICAgPC9yZGY6RGVzY3JpcHRpb24-CiAgICA8cmRmOkRlc2NyaXB0aW9uIHJkZjphYm91dD0iIiB4bWxuczpkYz0iaHR0cDovL3B1cmwub3JnL2RjL2VsZW1lbnRzLzEuMS8iPgogICAgICA8ZGM6Zm9ybWF0PmFwcGxpY2F0aW9uL3BkZjwvZGM6Zm9ybWF0PgogICAgICA8ZGM6Y3JlYXRvcj4KICAgICAgICA8cmRmOlNlcT4KICAgICAgICAgIDxyZGY6bGk-PC9yZGY6bGk-CiAgICAgICAgPC9yZGY6U2VxPgogICAgICA8L2RjOmNyZWF0b3I-CiAgICAgIDxkYzp0aXRsZT4KICAgICAgICA8cmRmOkFsdD4KICAgICAgICAgIDxyZGY6bGkgeG1sOmxhbmc9IngtZGVmYXVsdCI-PC9yZGY6bGk-CiAgICAgICAgPC9yZGY6QWx0PgogICAgICA8L2RjOnRpdGxlPgogICAgICA8ZGM6ZGVzY3JpcHRpb24-CiAgICAgICAgPHJkZjpBbHQ-CiAgICAgICAgICA8cmRmOmxpIHhtbDpsYW5nPSJ4LWRlZmF1bHQiLz4KICAgICAgICA8L3JkZjpBbHQ-CiAgICAgIDwvZGM6ZGVzY3JpcHRpb24-CiAgICA8L3JkZjpEZXNjcmlwdGlvbj4KICAgIDxyZGY6RGVzY3JpcHRpb24gcmRmOmFib3V0PSIiIHhtbG5zOnBkZj0iaHR0cDovL25zLmFkb2JlLmNvbS9wZGYvMS4zLyI-CiAgICAgIDxwZGY6S2V5d29yZHM-PC9wZGY6S2V5d29yZHM-CiAgICAgIDxwZGY6UHJvZHVjZXI-Tml0cm8gUHJvIDkgICg5LiAwLiA2LiAyMCk8L3BkZjpQcm9kdWNlcj4KICAgIDwvcmRmOkRlc2NyaXB0aW9uPgogICAgPHJkZjpEZXNjcmlwdGlvbiByZGY6YWJvdXQ9IiIgeG1sbnM6eG1wTU09Imh0dHA6Ly9ucy5hZG9iZS5jb20veGFwLzEuMC9tbS8iPgogICAgICA8eG1wTU06RG9jdW1lbnRJRD51dWlkOmIyZDdlMTY0LTVjOTUtNDZlZS05N2ZlLTI3MmNkZjBlMDIwOTwveG1wTU06RG9jdW1lbnRJRD4KICAgIDwvcmRmOkRlc2NyaXB0aW9uPgogIDwvcmRmOlJERj4KPC94OnhtcG1ldGE-CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICANCjw_eHBhY2tldCBlbmQ9J3cnPz4KZW5kc3RyZWFtCmVuZG9iagoxMiAwIG9iago8PC9DcmVhdGlvbkRhdGUgKEQ6MjAxNTEwMjcwNjMxNThaKQovQ3JlYXRvciAo_v8ATgBpAHQAcgBvACAAUAByAG8AIAA5ACAAIABcKAA5AC4AIAAwAC4AIAA2AC4AIAAyADAAXCkpCi9Nb2REYXRlIChEOjIwMTUxMDI3MDYzMTU5WikKL1Byb2R1Y2VyICj-_wBOAGkAdAByAG8AIABQAHIAbwAgADkAIAAgAFwoADkALgAgADAALgAgADYALgAgADIAMABcKSkKPj4KZW5kb2JqCjExIDAgb2JqCjw8L0RlY29kZVBhcm1zIFtudWxsIF0KL0ZpbHRlciBbL0ZsYXRlRGVjb2RlIF0KL0xlbmd0aCAyNwo-PgpzdHJlYW0KeNoyUHDnMlRI5yrkCgRjAAAAAP__AwAZ1AMQCmVuZHN0cmVhbQplbmRvYmoKMTAgMCBvYmoKPDwvQ29udGVudHMgMTEgMCBSCi9NZWRpYUJveCBbMCAwIDI4My40NCAxNTUuODggXQovUGFyZW50IDEgMCBSCi9UeXBlIC9QYWdlCj4-CmVuZG9iago5IDAgb2JqCjw8L0RlY29kZVBhcm1zIFtudWxsIF0KL0ZpbHRlciBbL0ZsYXRlRGVjb2RlIF0KL0xlbmd0aCAyNwo-PgpzdHJlYW0KeNoyUHDnMlRI5yrkCgRjAAAAAP__AwAZ1AMQCmVuZHN0cmVhbQplbmRvYmoKOCAwIG9iago8PC9Db250ZW50cyA5IDAgUgovTWVkaWFCb3ggWzAgMCAyODMuNDQgMTU1Ljg4IF0KL1BhcmVudCAxIDAgUgovVHlwZSAvUGFnZQo-PgplbmRvYmoKNyAwIG9iago8PC9EZWNvZGVQYXJtcyBbbnVsbCBdCi9GaWx0ZXIgWy9GbGF0ZURlY29kZSBdCi9MZW5ndGggMjcKPj4Kc3RyZWFtCnjaMlBw5zJUSOcq5AoEYwAAAAD__wMAGdQDEAplbmRzdHJlYW0KZW5kb2JqCjYgMCBvYmoKPDwvQ29udGVudHMgNyAwIFIKL01lZGlhQm94IFswIDAgMjgzLjQ0IDE1NS44OCBdCi9QYXJlbnQgMSAwIFIKL1R5cGUgL1BhZ2UKPj4KZW5kb2JqCjUgMCBvYmoKPDwvRGVjb2RlUGFybXMgW251bGwgXQovRmlsdGVyIFsvRmxhdGVEZWNvZGUgXQovTGVuZ3RoIDI3Cj4-CnN0cmVhbQp42jJQcOcyVEjnKuQKBGMAAAAA__8DABnUAxAKZW5kc3RyZWFtCmVuZG9iago0IDAgb2JqCjw8L0NvbnRlbnRzIDUgMCBSCi9NZWRpYUJveCBbMCAwIDI4My40NCAxNTUuODggXQovUGFyZW50IDEgMCBSCi9UeXBlIC9QYWdlCj4-CmVuZG9iagozIDAgb2JqCjw8Pj4KZW5kb2JqCjIgMCBvYmoKPDwvTWV0YWRhdGEgMTMgMCBSCi9QYWdlTW9kZSAvVXNlTm9uZQovUGFnZXMgMSAwIFIKL1R5cGUgL0NhdGFsb2cKL1ZpZXdlclByZWZlcmVuY2VzIDw8L0NlbnRlcldpbmRvdyBmYWxzZQovRGlyZWN0aW9uIC9MMlIKL0Rpc3BsYXlEb2NUaXRsZSBmYWxzZQovRml0V2luZG93IGZhbHNlCi9IaWRlTWVudWJhciBmYWxzZQovSGlkZVRvb2xiYXIgZmFsc2UKL0hpZGVXaW5kb3dVSSBmYWxzZQovTm9uRnVsbFNjcmVlblBhZ2VNb2RlIC9Vc2VOb25lCi9QcmludEFyZWEgL0Nyb3BCb3gKL1ByaW50Q2xpcCAvQ3JvcEJveAovUHJpbnRTY2FsaW5nIC9BcHBEZWZhdWx0Ci9WaWV3QXJlYSAvQ3JvcEJveAovVmlld0NsaXAgL0Nyb3BCb3gKPj4KPj4KZW5kb2JqCjEgMCBvYmoKPDwvQ291bnQgNAovS2lkcyBbNCAwIFIgNiAwIFIgOCAwIFIgMTAgMCBSIF0KL01lZGlhQm94IFswIDAgNTk1LjI3NTU5MSA4NDEuODg5NzY0IF0KL1Jlc291cmNlcyAzIDAgUgovVHlwZSAvUGFnZXMKPj4KZW5kb2JqCnhyZWYNCjAgMTQNCjAwMDAwMDAwMDAgNjU1MzUgZg0KMDAwMDAwNDA1MSAwMDAwMCBuDQowMDAwMDAzNjczIDAwMDAwIG4NCjAwMDAwMDM2NTMgMDAwMDAgbg0KMDAwMDAwMzU2MCAwMDAwMCBuDQowMDAwMDAzNDM5IDAwMDAwIG4NCjAwMDAwMDMzNDYgMDAwMDAgbg0KMDAwMDAwMzIyNSAwMDAwMCBuDQowMDAwMDAzMTMyIDAwMDAwIG4NCjAwMDAwMDMwMTEgMDAwMDAgbg0KMDAwMDAwMjkxNiAwMDAwMCBuDQowMDAwMDAyNzk0IDAwMDAwIG4NCjAwMDAwMDI1NzMgMDAwMDAgbg0KMDAwMDAwMDAxNSAwMDAwMCBuDQp0cmFpbGVyDQo8PC9JRCBbKJZw0JaaAkkzqA_DLCyWebN5sykgKCrTGVxyGH1KiqmO-vNSp9ci1yIpIF0KL0luZm8gMTIgMCBSCi9Sb290IDIgMCBSCi9TaXplIDE0Cj4-DQpzdGFydHhyZWYNCjQxODMNCiUlRU9G"
}
when I use the below function to convert the clob to blob and save it in a file, the file is corrupted. The original file is a PDF file.
Anyone know the correct way to decode the gmail attachment?
CREATE OR REPLACE FUNCTION base64decode(p_clob CLOB)
RETURN BLOB
-- -----------------------------------------------------------------------------------
-- File Name : https://oracle-base.com/dba/miscellaneous/base64decode.sql
-- Author : Tim Hall
-- Description : Decodes a Base64 CLOB into a BLOB
-- Last Modified: 09/11/2011
-- -----------------------------------------------------------------------------------
IS
l_blob BLOB;
l_raw RAW(32767);
l_amt NUMBER := 7700;
l_offset NUMBER := 1;
l_temp VARCHAR2(32767);
BEGIN
BEGIN
DBMS_LOB.createtemporary (l_blob, FALSE, DBMS_LOB.CALL);
LOOP
DBMS_LOB.read(p_clob, l_amt, l_offset, l_temp);
l_offset := l_offset + l_amt;
l_raw := UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw(l_temp));
DBMS_LOB.append (l_blob, TO_BLOB(l_raw));
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
RETURN l_blob;
END;
Thanks
Vincent