OraFAQ Forum: SQL & PL/SQL » ult

Home » SQL & PL/SQL » SQL & PL/SQL » ult_http (Oracle 11g R2 - Windows 7x64 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ult_http [message #575568]

Thu, 24 January 2013 04:02

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

Report message to a moderator

Re: ult_http [message #575601 is a reply to message #575568]

Thu, 24 January 2013 11:00

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: ult_http [message #575607 is a reply to message #575601]

Thu, 24 January 2013 12:53

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

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 )

Attachment: PROC_esamina.sql
(Size: 2.49KB, Downloaded 835 times)

[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

_jum
Messages: 577
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.

Report message to a moderator

Re: ult_http [message #575673 is a reply to message #575641]

Fri, 25 January 2013 08:51

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

Report message to a moderator

Re: ult_http [message #575675 is a reply to message #575673]

Fri, 25 January 2013 10:15

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The returned value is a VARCHAR2 so it is limited to 32K.

Regards
Michel

Report message to a moderator

Re: ult_http [message #575690 is a reply to message #575675]

Fri, 25 January 2013 12:17

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?

Report message to a moderator

Re: ult_http [message #575693 is a reply to message #575690]

Fri, 25 January 2013 12:25

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The value is limited to 32K.
What returns the procedure is limited to 32K.

Regards
Michel

Report message to a moderator

Re: ult_http [message #575748 is a reply to message #575693]

Sat, 26 January 2013 16:00

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

Report message to a moderator

Re: ult_http [message #575829 is a reply to message #575748]

Mon, 28 January 2013 04:53

sss111ind
Messages: 636
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

Report message to a moderator

Re: ult_http [message #575834 is a reply to message #575829]

Mon, 28 January 2013 05:14

_jum
Messages: 577
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.

Report message to a moderator

Re: ult_http [message #575837 is a reply to message #575829]

Mon, 28 January 2013 05:34

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 24 23:25:44 CDT 2025