| Converting utl_http.read_text to PDF file [message #569347] |
Thu, 25 October 2012 02:47  |
 |
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 #569351 is a reply to message #569350] |
Thu, 25 October 2012 03:22  |
|
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 #569373 is a reply to message #569370] |
Thu, 25 October 2012 06:21 |
|
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;
/
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
