Home » SQL & PL/SQL » SQL & PL/SQL » Converting utl_http.read_text to PDF file (oracle 10g)
Converting utl_http.read_text to PDF file [message #569347] Thu, 25 October 2012 02:47 Go to next message
monkey_suit
Messages: 21
Registered: February 2011
Junior Member
Hi All,
I was tinkering around with utl_http api in pl/sql. Essentially I wanted to connect to our report server and retrieve an oracle report from the database(not forms). I got the report successful but by using the utl_http.read_text the pdf is essentially a clob now and i have no idea how to convert the clob to pdf and save it on my local pc. I've looped through the clob converting it to raw and tried the utl_file.put_raw and put_line but it didn't work. Any suggestions.

thanks
Re: Converting utl_http.read_text to PDF file [message #569348 is a reply to message #569347] Thu, 25 October 2012 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use UTL_FILE package (PUT_RAW function at the bottom of the page, carefully read the notes).

Regards
Michel
Re: Converting utl_http.read_text to PDF file [message #569349 is a reply to message #569348] Thu, 25 October 2012 03:02 Go to previous messageGo to next message
monkey_suit
Messages: 21
Registered: February 2011
Junior Member
Hi Michael, i tried the put_raw proc but when i try to open the pdf, adobe reader complains saying the that the file is corrupt or not decoded properly.
Re: Converting utl_http.read_text to PDF file [message #569350 is a reply to message #569349] Thu, 25 October 2012 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 59297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the code you use.

Regards
Michel
Re: Converting utl_http.read_text to PDF file [message #569351 is a reply to message #569350] Thu, 25 October 2012 03:22 Go to previous messageGo to next message
monkey_suit
Messages: 21
Registered: February 2011
Junior Member
declare
  lf_file             utl_file.file_type;
  lr_raw_data         raw(32767);
  lb_clob_response    clob;
  lh_http_request     utl_http.req;
  lh_http_response    utl_http.resp;
  ln_counter          pls_integer := 0;
  ln_substr_len       pls_integer := 2000;
  lv_os_out       	  varchar2(2500); -- required for runoscommand
  ln_os_num       	  number := 0;  -- required for runoscommand
  lv_substr_data      varchar2(2000);
  lv_path         	  varchar2(40) := util_get_path('UNIX'); --personal funct to get paths
  lv_pub_path     	  varchar2(40) := util_get_path('PUB');
  
  lv_report_url       varchar2(75) := 'http://foreg.only.com:8888/reports/rwservlet?server=reportserver'; -- eg only
  lv_report_name      varchar2(50) := 'report=repotrt_name.rdf';
  lv_desname          varchar2(50) := 'desname=/oracle/tmp/jp_rep.pdf';
  lv_dest_type        varchar2(20) := 'destype=cache';
  lv_desformat        varchar2(20) := 'desformat=pdf';
  lv_db_cred          varchar2(100) := 'userid=user/pass@schema.foregonly.com';
  lv_params           varchar2(500) := 'pn_region_no=2'||'&'||'pn_season_code=''W12'''||'&'||'pv_username=''USERNAME''';

begin
  lh_http_request := utl_http.begin_request(lv_report_url||'&'||
                                            lv_db_cred||'&'||
                                            lv_report_name||'&'||
                                            lv_dest_type||'&'||
                                            lv_desformat||'&'||
                                            lv_desname||'&'||
                                            lv_params);
  lh_http_response := utl_http.get_response(lh_http_request);
  utl_http.read_text(lh_http_response, lb_clob_response);
  utl_http.end_response(lh_http_response);
  --dbms_output.put_line(lb_clob_response);
  
  /*this part i can't get working */
  lf_file := utl_file.fopen(lv_path, 'test.pdf','wb',32000);
  while true loop
    lv_substr_data := dbms_lob.substr(lb_clob_response,
                                      least(ln_substr_len, ln_substr_len * ln_counter + 1 - length(lb_clob_response)),
                                      ln_substr_len * ln_counter + 1);
    utl_file.put_raw(lf_file, utl_raw.cast_to_raw(lv_substr_data), true);
    --utl_file.put_line(lf_file, lv_substr_data);
    exit when lv_substr_data is null;
  end loop;
  utl_file.fclose(lf_file);
  osutil.RunOsCmd('cp -f '||lv_path ||'/'||'test.pdf'||' '||lv_pub_path,lv_os_out,ln_os_num); --external pkg
  osutil.RunOsCmd('rm -f '||lv_path ||'/'||'test.pdf', lv_os_out,ln_os_num);
exception
  when others then
    utl_http.end_response(lh_http_response);
    utl_file.fclose(lf_file);
    dbms_output.put_line(sqlerrm);
end;
/
Re: Converting utl_http.read_text to PDF file [message #569358 is a reply to message #569351] Thu, 25 October 2012 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 59297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You must use utl_http.read_raw to get a PDF file and use a BLOB, not a CLOB, to receive it.

Regards
Michel
Re: Converting utl_http.read_text to PDF file [message #569369 is a reply to message #569358] Thu, 25 October 2012 05:11 Go to previous messageGo to next message
monkey_suit
Messages: 21
Registered: February 2011
Junior Member
hi michel once i have blob do i have to use the substr the blob and save to a file using the put_raw
Re: Converting utl_http.read_text to PDF file [message #569370 is a reply to message #569369] Thu, 25 October 2012 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 59297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If it is greater than 32000 (your open size parameter), yes.
And lv_substr_data must be a RAW.

Regards
Michel
Re: Converting utl_http.read_text to PDF file [message #569373 is a reply to message #569370] Thu, 25 October 2012 06:21 Go to previous messageGo to next message
monkey_suit
Messages: 21
Registered: February 2011
Junior Member
Hi Michel coupled with your advice and some stuff in the docs and the web i was able to come up with a solution. the only issue i get with this is that when i try to open the pdf it complains that the pdf is already open, when close adobe reader and open it again it works as per normal. thanks again Michel.

SOLUTION
declare
  lf_file             utl_file.file_type;
  lr_raw_data         raw(32767);
  lb_blob_data        blob;
  ln_buffer_size      binary_integer := 32767;
  ln_read_offset      integer := 1;
  ln_blob_len         integer;
  
  lh_http_request     utl_http.req;
  lh_http_response    utl_http.resp;

  lv_os_out           varchar2(2500); -- required for my  runoscommand
  ln_os_num           number := 0;  -- required for my runoscommand
  lv_path             varchar2(40) := util_get_path('UNIX'); --personal funct to get paths
  lv_pub_path         varchar2(40) := util_get_path('PUB');
  
  lv_report_url       varchar2(75) := 'http://foreg.only.com:8888/reports/rwservlet?server=reportserver'; -- eg only
  lv_report_name      varchar2(50) := 'report=repotrt_name.rdf';
  lv_desname          varchar2(50) := 'desname=/oracle/tmp/jp_rep.pdf';
  lv_dest_type        varchar2(20) := 'destype=cache';
  lv_desformat        varchar2(20) := 'desformat=pdf';
  lv_db_cred          varchar2(100) := 'userid=user/pass@schema.foregonly.com';
  lv_params           varchar2(500) := 'pn_region=2'||'&'||'pn_season_code=''12'''||'&'||'pv_username=''USERNAME''';

begin
  dbms_lob.createtemporary(lb_blob_data, false);
  lh_http_request := utl_http.begin_request(lv_report_url||'&'||
                                            lv_db_cred||'&'||
                                            lv_report_name||'&'||
                                            lv_dest_type||'&'||
                                            lv_desformat||'&'||
                                            lv_desname||'&'||
                                            lv_params);
  lh_http_response := utl_http.get_response(lh_http_request);
  
  begin  
    loop
      utl_http.read_raw(lh_http_response, lr_raw_data, ln_buffer_size);      
      dbms_lob.writeappend (lb_blob_data, utl_raw.length(lr_raw_data), lr_raw_data);      
    end loop;    
  exception  
    when utl_http.end_of_body then
      utl_http.end_response(lh_http_response);
  end;
   
  ln_blob_len := dbms_lob.getlength(lb_blob_data);
  ln_read_offset := 1;
  lf_file := utl_file.fopen(lv_path, 'test.pdf', 'wb', ln_buffer_size);
  while ln_read_offset < ln_blob_len loop
    dbms_lob.read(lb_blob_data, ln_buffer_size, ln_read_offset, lr_raw_data);    
    utl_file.put_raw(lf_file, lr_raw_data, true);    
    ln_read_offset := ln_read_offset + ln_buffer_size;
  end loop;
  
  dbms_lob.freetemporary(lb_blob_data); 
  utl_file.fclose(lf_file);
  osutil.RunOsCmd('cp -f '||lv_path ||'/'||'test.pdf'||' '||lv_pub_path,lv_os_out,ln_os_num); --external pkg you could probably use utl_file.rename or copy
  osutil.RunOsCmd('rm -f '||lv_path ||'/'||'test.pdf', lv_os_out,ln_os_num);
exception
  when others then
    utl_http.end_response(lh_http_response);
    utl_file.fclose(lf_file);
    dbms_lob.freetemporary(lb_blob_data);
    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
/

Re: Converting utl_http.read_text to PDF file [message #569376 is a reply to message #569373] Thu, 25 October 2012 06:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59297
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback and code.
Given the code you posted, I think you do not open the file you write with the procedure (as you remove it) but the copy that you make, so the problem very likely comes from your 'osutil.RunOsCmd' procedure which may not (cleanly) end the process that copies the file.

Regards
Michel
Re: Converting utl_http.read_text to PDF file [message #569377 is a reply to message #569376] Thu, 25 October 2012 06:52 Go to previous message
monkey_suit
Messages: 21
Registered: February 2011
Junior Member
i suspected that as well because its usually used to move flat files across servers.
Previous Topic: Procedure Doubt
Next Topic: Information on Package Contents
Goto Forum:
  


Current Time: Thu Oct 02 10:13:16 CDT 2014

Total time taken to generate the page: 0.08801 seconds