Home » SQL & PL/SQL » SQL & PL/SQL » from html to xml and insert to table (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0)
from html to xml and insert to table [message #478950] Wed, 13 October 2010 14:01 Go to next message
mape
Messages: 227
Registered: July 2006
Location: Slovakia
Senior Member
Hi

I basically get it how to insert selected records from html to table by converting into the xml format thanks to
http://www.orafaq.com/forum/m/478306/88643
(Barbara and Michel know what Im talking about).

But now I cant get the hang of convert one html site
where are a very usefull information for my job.

Its about the site http://www.mobitola.sk/phones/Sony_Ericsson/C702/

where I need to get informations about phone specs and insert into the table.
Informations begin with" rozmery telefónu 106 x 48 x 16 mm"
and ends "podpora WCSS".

However I try to figure it out always I gonna get some ORA errors.

Would you be so kind and show me how to do it?

Thanks a lot

Regards



Re: from html to xml and insert to table [message #478952 is a reply to message #478950] Wed, 13 October 2010 14:14 Go to previous messageGo to next message
mape
Messages: 227
Registered: July 2006
Location: Slovakia
Senior Member
I got a problem to remote connect to database right now Sad
but it's all about to select phone specs and insert into the table.

I try to do it like:

DECLARE 
     l_pieces  utl_http.html_pieces;
      l_url     varchar2(32767) default  'http://www.mobitola.sk/phones/Sony_Ericsson/C702/'; 
     l_page     clob;
 
BEGIN
    
      -- get html pieces from main url and put page into clob:
     dbms_lob.createtemporary (l_page, TRUE);
     l_pieces := utl_http.request_pieces(l_url);
     for i in 1 .. l_pieces.count loop
        l_page := l_page || l_pieces(i);
        
          --DBMS_OUTPUT.PUT_LINE(l_pieces(i) );
     end loop;
     
      -- do whatever needs to be done to convert the html to valid xml:
        l_page := substr (l_page, instr (l_page, 'základné údaje'));
        l_page := substr (l_page, 1, instr (l_page, 'podpora WCSS') + 7);
   
        DBMS_OUTPUT.PUT_LINE(l_page);

 -- free temporary clob:
        dbms_lob.freetemporary (l_page);
  -- end loop;
 end;



If you run it you will see ORA errors.

[Updated on: Wed, 13 October 2010 14:15]

Report message to a moderator

Re: from html to xml and insert to table [message #478954 is a reply to message #478952] Wed, 13 October 2010 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 57600
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> DECLARE 
  2       l_pieces  utl_http.html_pieces;
  3        l_url     varchar2(32767) default  'http://www.mobitola.sk/phones/Sony_Ericsson/C702/'; 
  4       l_page     clob;
  5   
  6  BEGIN
  7      
  8        -- get html pieces from main url and put page into clob:
  9       dbms_lob.createtemporary (l_page, TRUE);
 10       l_pieces := utl_http.request_pieces(l_url);
 11       for i in 1 .. l_pieces.count loop
 12          l_page := l_page || l_pieces(i);
 13          
 14            --DBMS_OUTPUT.PUT_LINE(l_pieces(i) );
 15       end loop;
 16       
 17        -- do whatever needs to be done to convert the html to valid xml:
 18          l_page := substr (l_page, instr (l_page, 'základné údaje'));
 19          l_page := substr (l_page, 1, instr (l_page, 'podpora WCSS') + 7);
 20     
 21          DBMS_OUTPUT.PUT_LINE(l_page);
 22  
 23   -- free temporary clob:
 24          dbms_lob.freetemporary (l_page);
 25    -- end loop;
 26   end;
 27  /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 21
Re: from html to xml and insert to table [message #478956 is a reply to message #478954] Wed, 13 October 2010 14:16 Go to previous messageGo to next message
Michel Cadot
Messages: 57600
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DBMS_OUTPUT.PUT_LINE cannot display a CLOB and this is why there is a "print" function in my procedure.

Regards
Michel

[Updated on: Wed, 13 October 2010 14:17]

Report message to a moderator

Re: from html to xml and insert to table [message #478959 is a reply to message #478956] Wed, 13 October 2010 14:23 Go to previous messageGo to next message
mape
Messages: 227
Registered: July 2006
Location: Slovakia
Senior Member
I dont get it "print" in my procedure.

DBMS_OUTPUT.PUT_LINE is there just for see html parse.

I can remove this dbms_output from plsq
but the problem is still how to insert records into the table.
Green check sign means YES and other one red cross means NO.

[Updated on: Wed, 13 October 2010 14:28]

Report message to a moderator

Re: from html to xml and insert to table [message #478961 is a reply to message #478959] Wed, 13 October 2010 14:25 Go to previous messageGo to next message
Michel Cadot
Messages: 57600
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read what I posted?

Regards
Michel
Re: from html to xml and insert to table [message #478963 is a reply to message #478961] Wed, 13 October 2010 14:29 Go to previous messageGo to next message
mape
Messages: 227
Registered: July 2006
Location: Slovakia
Senior Member
I can remove this dbms_output from plsq
but the problem is still how to insert records into the table.
Re: from html to xml and insert to table [message #478964 is a reply to message #478963] Wed, 13 October 2010 14:36 Go to previous messageGo to next message
Michel Cadot
Messages: 57600
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't post the code you execute and have an error you can't know what error you have.

Regards
Michel
Re: from html to xml and insert to table [message #478969 is a reply to message #478964] Wed, 13 October 2010 14:50 Go to previous messageGo to next message
mape
Messages: 227
Registered: July 2006
Location: Slovakia
Senior Member
Im used to use DBMS_OUTPUT.PUT_LINE(l_page) to see html code
and then work on it.
And when I get these ORA errors I dont know to figure it out.

[Updated on: Wed, 13 October 2010 14:52]

Report message to a moderator

Re: from html to xml and insert to table [message #478971 is a reply to message #478969] Wed, 13 October 2010 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 57600
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what can we say?
You have errors, you don't share them with us, you don't share the code, you don't share anything.
So you have errors, try to fix them.

Regards
Mochel
Re: from html to xml and insert to table [message #478973 is a reply to message #478969] Wed, 13 October 2010 15:01 Go to previous message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
If you remove the dbms_output, then there aren't any errors. What I frequently do when I need to view long things to debug them is instead of:

declare
  ...
begin
  ...
  dbms_output.put_line (l_page);
  ...
end;
/


I use:

variable g_ref refcursor
declare
  ...
begin
  ...
  open :g_ref for select l_page from dual;
  ...
end;
/
print g_ref


as shown below:

SCOTT@orcl_11gR2> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11gR2> DECLARE
  2  	  l_pieces  UTL_HTTP.HTML_PIECES;
  3  	  l_url     VARCHAR2(32767) DEFAULT 'http://www.mobitola.sk/phones/Sony_Ericsson/C702/';
  4  	  l_page    CLOB;
  5  BEGIN
  6  	  -- get html pieces from main url and put page into clob:
  7  	  DBMS_LOB.CREATETEMPORARY (l_page, TRUE);
  8  	  l_pieces := UTL_HTTP.REQUEST_PIECES (l_url);
  9  	  FOR i in 1 .. l_pieces.COUNT LOOP
 10  	     l_page := l_page || l_pieces(i);
 11  	  END LOOP;
 12  
 13  	  -- do whatever needs to be done to convert the html to valid xml:
 14  	  l_page := SUBSTR (l_page, INSTR (l_page, 'základné údaje'));
 15  	  l_page := SUBSTR (l_page, 1, INSTR (l_page, 'podpora WCSS') + 7);
 16  
 17  	  OPEN :g_ref FOR SELECT l_page FROM DUAL;
 18  
 19  	  -- free temporary clob:
 20  	  DBMS_LOB.FREETEMPORARY (l_page);
 21  END;
 22  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11gR2> PRINT g_ref

:B1
--------------------------------------------------------------------------------
základné údaje</h3></td></tr>

... truncated to save space

    <td width="50%"><img src="/images/pu.png"  width=12 height=12> podpora


1 row selected.

SCOTT@orcl_11gR2> 

Previous Topic: display data in grouping format
Next Topic: Left outer join or function (2 merged)
Goto Forum:
  


Current Time: Wed Apr 16 08:50:53 CDT 2014

Total time taken to generate the page: 0.13364 seconds