Home » SQL & PL/SQL » SQL & PL/SQL » ult_http (Oracle 11g R2 - Windows 7x64 )
ult_http [message #575568] Thu, 24 January 2013 04:02 Go to next message
limner
Messages: 9
Registered: January 2013
Location: Rome
Junior Member
Good Morning to everyone.

I have a problem: i need to retrieve html using oracle and setting the correct user agent.

I used the ut_http package and his fuction utl_http.request_pages.

I worked perfectly for me, execpt that i could be able to setup the correct user-agent.
I read the documentation and i found that this instruction utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
should fix my problem, but when i used it, nothing changed and the webserver didn't recognize the correct user agent.

I post downhere a piece of the pl-sql procedure i made (it seems that i could find the correct "link" between utl_http.set_header instruction and utl_http.request_pieces instruction)

while(ng < nmax)
loop
ch_in:=p_mese_elab||'-'||ng;

url:=url to retrieve;

req := UTL_HTTP.BEGIN_REQUEST(url);
UTL_HTTP.SET_HEADER(req, 'User-Agent', 'Mozilla/4.0');
resp := UTL_HTTP.GET_RESPONSE(req);

RISULTATO:=utl_http.request_pieces(url,99999);

delete temp_esamina
where data_ril = trunc(to_date(ch_in,'yyyy-mm-dd'));

step:=3;


i:=0;

for i in 1 .. RISULTATO.count
loop
step:=4;

select to_date(ch_in,'yyyy-mm-dd') into app_data from dual;


insert into temp_esamina
( testo
,data_ril
,num )
values ( RISULTATO(i)
,app_data
,i );

step:=5;
end loop;
UTL_HTTP.END_RESPONSE(resp);

ng:=ng+1;
ngg:=ngg+1;
end loop;




If someone could help me, it would be greately apreciated!
Samantha
Re: ult_http [message #575601 is a reply to message #575568] Thu, 24 January 2013 11:00 Go to previous messageGo to next message
Littlefoot
Messages: 19303
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It's probably me, but - I don't quite understand what you are saying. It is difficult to run code you posted because we don't have your tables (which you use in DELETE and INSERT statements); also, variables' declaration section is missing.

Perhaps you should rewrite it, but this time posting complete code, along with CREATE TABLE statement as well as URL you are testing the code with. Explain what you got (but aren't satisfied with (why not?)) and what you would like to get as a result. Then, people could run your code and try to help.

Finally: please, before posting your next message, format the code and enclose it into the CODE tags which will preserve formatting and make your code easier to read. If you don't know how to do that, check this; won't take more than several seconds.
Re: ult_http [message #575607 is a reply to message #575601] Thu, 24 January 2013 12:53 Go to previous messageGo to next message
limner
Messages: 9
Registered: January 2013
Location: Rome
Junior Member
Hi
the code was an example, but i can attach the procedure.

My problem is that i don't know how to specify the "user agent" when using the utl_http.request_pieces instruction.

Thanks you in advance, if you would help me Smile Smile Smile

P.S.
please keep in mind that the procedure i'm building is in "alpha phase".....

Samantha



This is the code for the only table i use:
CREATE TABLE TEMP_ESAMINA
( DATA_RIL  DATE,
  TESTO     VARCHAR2(4000 BYTE),
  NUM       NUMBER )  

[Updated on: Thu, 24 January 2013 12:57]

Report message to a moderator

Re: ult_http [message #575641 is a reply to message #575607] Fri, 25 January 2013 01:00 Go to previous messageGo to next message
_jum
Messages: 486
Registered: February 2008
Senior Member
You said Quote:
that this instruction utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
should fix my problem

What is the problem you try to fix ?

I didn't need to use utl_http.set_header until now, here is an example, that works with or without:
set serveroutput on unlimited

DECLARE
 req   utl_http.req;
 resp  utl_http.resp;
 value VARCHAR2(1024);
BEGIN
  req := utl_http.begin_request('http://www.orafaq.com/forum/t/140212/120300/');
  --utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
  resp := utl_http.get_response(req);
  FOR i IN 1..100
  LOOP
    utl_http.read_line(resp, value, TRUE);
    dbms_output.put_line(value);
  END LOOP;
  utl_http.end_response(resp);
EXCEPTION
  WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
END;
/

<!DOCTYPE html>
<html lang="en" dir="ltr">
<head>
	<meta charset="utf-8">
	<meta name="description" content="Getting Data From the web without PL/SQL " />
	<title>OraFAQ Forum: SQL &amp; PL/SQL &raquo; Getting Data From the web without PL/SQL</title>
	<base href="http://www.orafaq.com/forum/" />
...

PL/SQL procedure successfully completed.
Re: ult_http [message #575673 is a reply to message #575641] Fri, 25 January 2013 08:51 Go to previous messageGo to next message
limner
Messages: 9
Registered: January 2013
Location: Rome
Junior Member
Dear Jum
first, thank you for your answer Smile

Then
The problem i'm trying to resolve is that when i use my procedure, the website recognize that i'm using oracle to retrieve data while i want to simulate a browser, so i need to change the user agent.

But........

I see in your procedure that you use the instruction utl_http.read_line(resp, value, TRUE); to retrieve information while i used utl_http.request_pieces: do your instruction work also for very long html pages?
Today i'll try your code in place of mine (thanks again) and i'll post the result i'll have

As soon as i'l have result, i'll post them so maybe also other people could get an answer if the could have the same problem.
Samantha
Re: ult_http [message #575675 is a reply to message #575673] Fri, 25 January 2013 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The returned value is a VARCHAR2 so it is limited to 32K.

Regards
Michel
Re: ult_http [message #575690 is a reply to message #575675] Fri, 25 January 2013 12:17 Go to previous messageGo to next message
limner
Messages: 9
Registered: January 2013
Location: Rome
Junior Member
what do you mean with the "limit to 32K?

that the entire value of utl_http.read_line(resp, value, TRUE); is 32k or each line printed is limited to 32k?
Re: ult_http [message #575693 is a reply to message #575690] Fri, 25 January 2013 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The value is limited to 32K.
What returns the procedure is limited to 32K.

Regards
Michel
Re: ult_http [message #575748 is a reply to message #575693] Sat, 26 January 2013 16:00 Go to previous messageGo to next message
limner
Messages: 9
Registered: January 2013
Location: Rome
Junior Member
wow, it worked perfectly!

setting the correct user agent now the browser send me the correct html to parse Smile

thanks to all, you were very kind!
Samantha
Re: ult_http [message #575829 is a reply to message #575748] Mon, 28 January 2013 04:53 Go to previous messageGo to next message
sss111ind
Messages: 464
Registered: April 2012
Location: India
Senior Member

Hi All,

I tried to run _jum's anonymous block directly ,it was throwing error. How it is possible to execute the same.
The error I got
Error report:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1029
ORA-12545: Connect failed because target host or object does not exist
ORA-06512: at line 6
29273. 00000 -  "HTTP request failed"
*Cause:    The UTL_HTTP package failed to execute the HTTP request.
*Action:   Use get_detailed_sqlerrm to check the detailed error message.
           Fix the error and retry the HTTP request.


Regards,
Nathan
Re: ult_http [message #575834 is a reply to message #575829] Mon, 28 January 2013 05:14 Go to previous messageGo to next message
_jum
Messages: 486
Registered: February 2008
Senior Member
The code still workes for me (ORACLE 11.2.0.3.0), so check your URL in utl_http.begin_request.
Re: ult_http [message #575837 is a reply to message #575829] Mon, 28 January 2013 05:34 Go to previous message
Michel Cadot
Messages: 58521
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, the Action part tells you what to first do: "Use get_detailed_sqlerrm to check the detailed error message.".

Regards
Michel

Previous Topic: ORA-55617: Flashback Archive
Next Topic: Schema populate
Goto Forum:
  


Current Time: Wed Jul 23 18:05:03 CDT 2014

Total time taken to generate the page: 0.24400 seconds