Home » SQL & PL/SQL » SQL & PL/SQL » pdf from pl/sql (Oracle 10g, 10.2.0.1.0, Windows XP)
pdf from pl/sql [message #447849] Thu, 18 March 2010 04:22 Go to next message
s4.ora
Messages: 71
Registered: March 2010
Member
Hi Everyone...

Can anyone tell me how can i read an HTML file from PLSQL.

Thanx, in advance.

Re: pdf from pl/sql [message #447852 is a reply to message #447849] Thu, 18 March 2010 04:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If it's on disc, use UTL_FILE
Re: pdf from pl/sql [message #447867 is a reply to message #447852] Thu, 18 March 2010 05:01 Go to previous messageGo to next message
s4.ora
Messages: 71
Registered: March 2010
Member

Can you provide me with a sample code?

I know how to use UTL_FILE but how can i read a HTML Document with that Package. To read from a file i can use the Fseek and Get_line functions, but a HTML document contains a lot of things, say for example TABLES.
Re: pdf from pl/sql [message #447879 is a reply to message #447867] Thu, 18 March 2010 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I know how to use UTL_FILE but how can i read a HTML Document with that Package.

Like any other file, HTML is just text.
Oracle does not know HTML tags, YOU have to handle them as you want.
Or use XML instead.

Regards
Michel
Re: pdf from pl/sql [message #447881 is a reply to message #447867] Thu, 18 March 2010 05:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You asked 'How do I read an HTML file using Pl/Sql'

The answer to that question is 'One line at a time, using UTL_FILE'

Now it looks like that's not actually what you want.

Give us a nice clear example, using sample HTML code, of exactly what you want to do.
Re: pdf from pl/sql [message #447882 is a reply to message #447881] Thu, 18 March 2010 05:51 Go to previous messageGo to next message
s4.ora
Messages: 71
Registered: March 2010
Member
OK... now this is what exactly i need to know. Putting data from HTML files into Oracle Tables.

I have created a table using the following script

create table html_tab
(Month varchar2(100),
 Savings number(5));


I have a HTML file which has a table containing the data required for the Oracle Table html_tab, i need to read data
from the HTML Table and populate that data into html_tab table inside the DB Server.

Reading data from html file using utl_file is definitely a way treating it as a text file.. i just want to know that is there any other way to perform the task as using utl_file for the perpouse will need lot of condition statements to check each line received for appropriate tags.

The HTML Code is as follows

<html>
<body>
<table border="1">
<tr>
<th>Month</th>
<th>Savings</th>
</tr>
<tr>
<td>January</td>
<td>100</td>
</tr>
<tr>
<td>February</td>
<td>200</td>
</tr>
<tr>
<td>March</td>
<td>300</td>
</tr>
<tr>
<td>April</td>
<td>400</td>
</tr>
<tr>
<td>May</td>
<td>500</td>
</tr>
<tr>
<td>June</td>
<td>600</td>
</tr>
</table>
</body>
</html>

[Updated on: Thu, 18 March 2010 06:05]

Report message to a moderator

Re: pdf from pl/sql [message #447890 is a reply to message #447882] Thu, 18 March 2010 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
External table is another way but this does not change the fact that HTML is not interpreted and you have to handle it.

Regards
Michel
Re: pdf from pl/sql [message #447898 is a reply to message #447849] Thu, 18 March 2010 06:38 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
@ sen.suvro

Title of your post says Quote:
pdf from pl/sql
and in Message you are talking about HTML ..what happened to Pdf
Re: pdf from pl/sql [message #447899 is a reply to message #447882] Thu, 18 March 2010 06:43 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
If your HTML is wellformed XML (it is!) you can use the XML capabilities of ORACLE:
WITH data
  AS (SELECT XMLTYPE
  ('
<html>
<body>
<table border="1">
<tr>
<th>Month</th>
<th>Savings</th>
</tr>
<tr>
<td>January</td>
<td>100</td>
</tr>
<tr>
<td>February</td>
<td>200</td>
</tr>
<tr>
<td>March</td>
<td>300</td>
</tr>
<tr>
<td>April</td>
<td>400</td>
</tr>
<tr>
<td>May</td>
<td>500</td>
</tr>
<tr>
<td>June</td>
<td>600</td>
</tr>
</table>
</body>
</html>
') xml_data FROM dual) 
 SELECT 
  EXTRACTVALUE(column_value,'//td[1]') month,
  EXTRACTVALUE(column_value,'//td[2]') cnt
    FROM data, 
    TABLE (XMLSEQUENCE (EXTRACT (xml_data, '//tr')));

 
MONTH	  CNT
-------------------
January	  100
February  200
March	  300
April	  400
May	  500
June	  600


Re: pdf from pl/sql [message #447900 is a reply to message #447898] Thu, 18 March 2010 06:44 Go to previous messageGo to next message
s4.ora
Messages: 71
Registered: March 2010
Member
Sorry for that.. i made a mistake in typing.. i meant HTML.
Re: pdf from pl/sql [message #447905 is a reply to message #447899] Thu, 18 March 2010 06:56 Go to previous messageGo to next message
s4.ora
Messages: 71
Registered: March 2010
Member

Thank You, Thanks for providing me with the solution.. it is working perfectly as i wanted.
Re: pdf from pl/sql [message #447933 is a reply to message #447905] Thu, 18 March 2010 09:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The problem is that HTML doesn't have to be welformed XML.

HTML supports tags like <BR> that have no closing tag.
XML doesn't like this.
Re: pdf from pl/sql [message #447958 is a reply to message #447849] Thu, 18 March 2010 12:50 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
as an alternative, you could try using third party tools instead of trying to write your own code. For example:

1) open this file in Excel
2) then save it as a tab delimited file

Now you can do whatever you want with it without having to write non-typical code that could be buggy.

If you are not getting my drift then here are some steps;

1) cut/paste the html data you posted into a file named a.htm
2) lauchn Excel
3) use file/open a.htm to get the file into Excel
4) use file/save (select tab delimited) to save the file as a.txt

I am not suggesting this is a better way to do things than was previously offered, only that it is an alternative. As an alternative option, it has different strenghts and weaknesses. On of its strenghts is that it uses someone else's intelligence to deal with the sticky problem of unpacking html.

Kevin
Previous Topic: how to use analytic functions in this case
Next Topic: decision tree in sql
Goto Forum:
  


Current Time: Tue Feb 11 19:00:49 CST 2025