Home

LewisC

PL/JSON is a generic JSON object written in PL/SQL. Using PL/SQL object syntax, users instantiate a JSON objects and then add members, arrays and additional JSON objects. This object type can store JSON data, in Oracle, persistently.


Project Members:


  • Hi,

    I'm using the script below in order to fetch JSON file from MongoDB, parse it and then insert it into Oracle table.

    • The script works fine in a sense that it inserts all values correctly into Oracle table. That includes the value Photo which is an image of base64 formate and it is much larger than 32KB.
    • The column Photo in the table Appery_Photos is of the type CLOB while column DecodedPhoto is of the type BLOB.

    • The problem lies in the line blobOriginal := base64decode1(Photo); which I used to decode the CLOB into BLOB. The function base64decode1 has been replaced with several functions (i.e. decode_base64 , base64DecodeClobAsBlob_plsql, base64decode , from_base64 & finally JSON_EXT.DECODE).

    • The result was the same for all of them. That is, the resultant BLOB object cannot be openned as an image in any of images editors (I'm using Oracle SQL Developer to download it).

    • I checked CLOB, and I could not find any newlines \n, nor could I find any spaces (only + signs found). Furthermore, I inserted CLOB value into the base64-image-converter and it displays the image correctly. In addition, I tried to encode the resultant BLOB in base64 back in order to further validate (using the opposite functions provided in the links above), the resultant base64 is not the same at all.

    
    BEGIN
      l_http_request := UTL_HTTP.begin_request('https://api.appery.io/rest/1/db/collections/Photos?where=%7B%22Oracle_Flag%22%3A%22Y%22%7D' , 'GET' , 'HTTP/1.1');
      -- ...set header's attributes
      UTL_HTTP.set_header(l_http_request, 'X-Appery-Database-Id', '53f2dac5e4b02cca64021dbe');
      l_http_response := UTL_HTTP.get_response(l_http_request);
      BEGIN
        LOOP
          UTL_HTTP.read_text(l_http_response, buf);
          l_response_text := l_response_text || buf;
        END LOOP;
      EXCEPTION
      WHEN UTL_HTTP.end_of_body THEN
        NULL;
      END;
      l_list := json_list(l_response_text);
      FOR i IN 1..l_list.count
      LOOP
        A_id  := json_ext.get_string(json(l_list.get(i)),'_id');
        l_val := json_ext.get_json_value(json(l_list.get(i)),'Photo');
        dbms_lob.createtemporary(Photo, true, 2);
        json_value.get_string(l_val, Photo);
        dbms_output.put_line(dbms_lob.getlength(Photo));
        dbms_output.put_line(dbms_lob.substr(Photo, 20, 1));
        blobOriginal := base64decode1(Photo);
        A_Name       := json_ext.get_string(json(l_list.get(i)),'Name');
        Remarks      := json_ext.get_string(json(l_list.get(i)),'Remarks');
        Status       := json_ext.get_string(json(l_list.get(i)),'Status');
        UserId       := json_ext.get_string(json(l_list.get(i)),'UserId');
        A_Date       := json_ext.get_string(json(l_list.get(i)),'Date');
        A_Time       := json_ext.get_string(json(l_list.get(i)),'Time');
        MSG_status   := json_ext.get_string(json(l_list.get(i)),'MSG_status');
        Oracle_Flag  := json_ext.get_string(json(l_list.get(i)),'Oracle_Flag');
        acl          := json_ext.get_string(json(l_list.get(i)),'acl');
        INSERT
        INTO Appery_Photos
          (
            A_id,
            Photo,
            DecodedPhoto,
            A_Name,
            Remarks,
            Status,
            UserId,
            A_Date,
            A_Time,
            MSG_status ,
            Oracle_Flag,
            acl
          )
          VALUES
          (
            A_id,
            Photo,
            blobOriginal,
            A_Name,
            Remarks,
            Status,
            UserId,
            A_Date,
            A_Time,
            MSG_status ,
            Oracle_Flag,
            acl
          );
        dbms_lob.freetemporary(Photo);
      END LOOP;
      -- finalizing
      UTL_HTTP.end_response(l_http_response);
    EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response(l_http_response);
    END;
    

    Any advice would be deeply appreciated.

     
    Last edit: Haytham Alzeini 2014-11-27