json - Decode base64 image into BLOB with PL/SQL -

-

i'm using script below in order fetch json file mongodb, parse , insert oracle table.

  • the script works fine in sense inserts values correctly oracle table. includes value photo image of base64 formate , larger 32kb.

  • the column photo in table appery_photos of type clob while column decodedphoto of type blob.

  • the problem lies in line bloboriginal := base64decode1(photo); used decode clob blob. function base64decode1 has been replaced several functions (i.e. decode_base64 , base64decodeclobasblob_plsql, base64decode , from_base64 & json_ext.decode).

  • the result same of them. is, resultant blob object cannot openned image in of images editors (i'm using oracle sql developer download it).

  • i checked clob, , not find newlines \n, nor find spaces (only + signs found). furthermore, inserted clob value base64-image-converter , displays image correctly. in addition, tried encode resultant blob in base64 in order further validate (using opposite functions provided in links above), resultant base64 not same @ 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         null;       end;       l_list := json_list(l_response_text);       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         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       utl_http.end_response(l_http_response);     end;

any appreciated.

i found not in function used in base64 decoding. instead, value have not base64 encoded strings, base64 encode datauri's, like

data:image/jpeg;base64,/9j/4aaqskzjrgabaqaaa

so have use like: clobbase642blob( substr( photo, instr( photo, ',' ) + 1 ) )

the following script inspired oracle community answer

 declare   l_param_list varchar2(512);   l_http_request utl_http.req;   l_http_response utl_http.resp;   l_response_text clob;   --l_response_text  varchar2(32767);   buf varchar2(32767);   l_list json_list;   l_val json_value;   a_id varchar2(100);   photo clob;   a_name      varchar2(100);   remarks     varchar2(100);   status      varchar2(100);   userid      varchar2(100);   a_date      varchar2(100);   a_time      varchar2(100);   msg_status  varchar2(100);   oracle_flag varchar2(100);   acl         varchar2(100);   obj json_list;   bloboriginal blob := empty_blob();   clobinbase64 clob;   substring varchar2(2000);   tmp blob;   n pls_integer                := 0;   substring_length pls_integer := 2000;   ------------------------------------------------------   function clobbase642blob(       p_clob clob )     return blob       t_blob blob;     t_buffer varchar2(32767);     t_pos    number := 1;     t_size   number := nls_charset_decl_len( 32764, nls_charset_id( 'char_cs' ) );     t_len    number;     t_tmp raw(32767);   begin     dbms_lob.createtemporary( t_blob, true );     t_len := length( p_clob );     loop       exit     when t_pos     > t_len;       t_buffer    := replace( replace( substr( p_clob, t_pos, t_size ), chr(10) ), chr(13) );       t_pos       := t_pos + t_size;       while t_pos  0       loop         t_buffer := t_buffer || replace( replace( substr( p_clob, t_pos, 1 ), chr(10) ), chr(13) );         t_pos    := t_pos + 1;       end loop;       t_tmp := utl_encode.base64_decode( utl_raw.cast_to_raw( t_buffer ) );       dbms_lob.writeappend( t_blob, utl_raw.length( t_tmp ), t_tmp );     end loop;     return t_blob;   end;   ------------------------------------------------------   begin     -- service's input parameters     -- preparing request...     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       null;     end;     l_list := json_list(l_response_text);     in 1..l_list.count     loop       a_id := json_ext.get_string(json(l_list.get(i)),'_id');       --deal base64 uri photo >32kb       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 := clobbase642blob( substr( photo, 24 ) );       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       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     utl_http.end_response(l_http_response);   end;   /

Comments

Post a Comment

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

-
i'm trying plot frequency characteristic equation using ezplot, matlab gives following warning, "contour not rendered non-finite zdata". have used command plot frequency equations warning , plot display empty , not change axis range well. can please help. appreciated. here's code i'm using. % transfer matrix case-i, thin rotor clear all; clc; ei = 1626; l = 0.15; m = 0.44108; = 2.178*10^-4; i_p = 2.205*10^-5; itr = 0.24; i_pr = 0.479; syms p n; f = [1 l*1i l^2/(2*ei)*1i l^3/(6*ei); 0 1 l/ei -l^2/(2*ei)*1i; 0 0 1 -l*1i; 0 0 0 l]; p = [ 1 0 0 0; 0 1 0 0; 0 -it*p^2+i_p*n*p 1 0; -m*p^2 0 0 1]; p_r = [1 0 0 0; 0 1 0 0; 0 -itr*p^2+i_pr*n*p 1 0; -m*p^2 0 0 1]; = f*p*f*p*f*p*f; b = p_r*f*p*f*p*f; r = a(1,2)/a(1,4); a12_p = 0; a22_p = a(2,2)-r*a(2,4); a32_p = a(3,2)-r*a(3,4); a42_p = a(4,2)-r*a(4,4); ap(2,2) = a22_p; ap(3,2) = a32_p; ap(4,2) = a42_p; ap(4,4) = 1; c = b*ap; m = [c(3,2) c(3,4); c(4,2) c(4,...
Read more

delphi - Indy UDP Read Contents of Adata -

-
i using indy udp server read string of data. however, not know how work adata parameter. can around converting binary using bytetobin function below , convert hex using bintohex1 . feel stupid , works slow. know how can directly results without 2 conversions? thanks!! here codes: procedure tform1.idudpserver1udpread(athread: tidudplistenerthread; const adata: tidbytes; abinding: tidsockethandle); var buf: tidbytes; buffer: string; data_received: string; begin if bytestostring(adata) <> 'hello' begin buffer := hextostring('00'); setlength(buf, length(buffer)); copytidstring(buffer, buf, 0); abinding.sendto(abinding.peerip, abinding.peerport, buf); data_received := bintohex1(bytetobin(adata[1]) + bytetobin(adata[2]) + bytetobin(adata[3]) + bytetobin(adata[4]) + bytetobin(adata[5]) + bytetobin(adata[6]) + bytetobin(adata[7]) + bytetobin(adata[8]) + bytetobin(adata[9]) + bytetobin(adata[10]) + bytetobin(adata[11...
Read more

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -

-
any ideas when firefox implement lengthadjust andtextlength?..... working svg application. lengthadjust , textlength working fine in chrome not working in fire fox . <svg viewbox = "0 0 500 300" version = "1.1"> <defs> <path id="s3" d="m 10,90 q 100,15 200,70" /> </defs> <g> <text font-size = "20"> <textpath xlink:href="#s3" textlength="205"> short text</textpath> </text> <use xlink:href="#s3" fill="none" stroke="black" stroke-width="1"/> </g> </svg> this implemented in firefox text elements (not textpath or tspan) in case that's ok though can move attribute text element. <svg viewbox = "0 0 500 300" version = "1.1"> <defs> ...
Read more