Home » SQL & PL/SQL » SQL & PL/SQL » Getting Data From the web without PL/SQL
Getting Data From the web without PL/SQL [message #385130] Fri, 06 February 2009 09:19 Go to next message
Folly
Messages: 5
Registered: February 2009
Junior Member
Hi All,

I'm currently working in a system that allows me to run SQL but won't let me use any PL/SQL (which is a real pain!?!).

I'm tryng to take data from a web site and then display it within the system I use.

To do this I have used the following code:

Select
utl_http.request(
'[URL TO THE WEB SITE]'
)
From
Dual

This works perfectly for me except it will only display the first 2,000 characters.

I was wondering if there was an alternative method of doing this with out using PL/SQL. Any thoughts regarding whether this is possible and/or how to do it would be greatly appriciated.

Even some direction to areas I might want to look at would be useful.

Thanks alot

Folly
Re: Getting Data From the web without PL/SQL [message #385133 is a reply to message #385130] Fri, 06 February 2009 09:22 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
wget
Re: Getting Data From the web without PL/SQL [message #385134 is a reply to message #385130] Fri, 06 February 2009 09:24 Go to previous messageGo to next message
Folly
Messages: 5
Registered: February 2009
Junior Member
sorry if I haven't made it clear,

I need to get this information through an SQL statement.
Re: Getting Data From the web without PL/SQL [message #385137 is a reply to message #385134] Fri, 06 February 2009 09:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The VARCHAR2 datatype is limited to 4000 characters in SQL, so you can't get more than 4000 characters easily with pure SQL, unless you are able to use CLOBs (Character Large Objects) which isn't likely when you can't even use PL/SQL

There is no way to do it completely WITHOUT pl/sql.

One option would be to write a function that returns what you want, if what you actually want in the end is less than 4000 characters.

Maybe have a look at pipelined table functions that would be able to return the data you need in multiple rows.

[Updated on: Fri, 06 February 2009 09:40]

Report message to a moderator

Re: Getting Data From the web without PL/SQL [message #385141 is a reply to message #385137] Fri, 06 February 2009 10:07 Go to previous messageGo to next message
Folly
Messages: 5
Registered: February 2009
Junior Member
this looks liek a great start,

would you be able to link to some information about what function I might need.

Thanks alot

folly
Re: Getting Data From the web without PL/SQL [message #385147 is a reply to message #385141] Fri, 06 February 2009 11:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The basic documentation about functions.

I also had some examples lying around that might be of use to you:

SQL> --- You need a "TABLE OF VARCHAR2" datatype
SQL> CREATE OR REPLACE TYPE vc2_array AS TABLE OF VARCHAR2(4000);
  2  /

Type created.

SQL>
SQL> --- Simple pipeline function example
SQL> CREATE OR REPLACE FUNCTION pipe_test
  2    RETURN vc2_array pipelined
  3  IS
  4
  5  BEGIN
  6   FOR i IN 1..10 LOOP
  7      pipe row('YO ' || i);
  8   END LOOP;
  9   RETURN;
 10  END;
 11  /

Function created.

SQL>
SQL> --- And test it
SQL> SELECT * FROM TABLE(pipe_test());

COLUMN_VALUE
--------------------------------------------------------------------------------

YO 1
YO 2
YO 3
YO 4
YO 5
YO 6
YO 7
YO 8
YO 9
YO 10

10 rows selected.

SQL>
SQL>
SQL> --- More complicated, adjusted to your needs which download a
SQL> --- Page in to an CLOB and then outputs it in chunks (with a size of 
SQL> --- 79 characters, so we don't break the forum width. ;-P
SQL> CREATE OR REPLACE FUNCTION get_page( p_url VARCHAR2 )
  2    RETURN vc2_array pipelined
  3  IS
  4
  5    v_pagedata CLOB;
  6
  7    v_i          NUMBER(12);
  8    v_len        NUMBER(12);
  9    v_part_size  NUMBER(12) := 79;
 10
 11  BEGIN
 12
 13    v_pagedata := utl_http.request(p_url);
 14    v_i   := 1;
 15    v_len := DBMS_LOB.getLength(v_pagedata);
 16    WHILE (v_i < v_len) LOOP
 17      PIPE ROW (DBMS_LOB.SubStr(v_pagedata,v_part_size, v_i));
 18      v_i := v_i + v_part_size;
 19    END LOOP;
 20    RETURN;
 21  END;
 22  /

Function created.

SQL>
SQL> SELECT * FROM TABLE(get_page('http://xx.xx.xx.xx'));

COLUMN_VALUE
--------------------------------------------------------------------------------

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN">
<html>
 <head>
  <title

>Index of /</title>
 </head>
 <body>
<h1>Index of /</h1>
<pre><img src="/icons/


COLUMN_VALUE
--------------------------------------------------------------------------------

blank.gif" alt="Icon " /> <a href="?C=N&amp;O=D">Name</a>                    <a
 href="?C=M&amp;O=A">Last modified</a>      <a href="?C=S&amp;O=A">Size</a>  <a
 href="?C=D&amp;O=A">Description</a><hr /><img src="/icons/folder.gif" alt="[DI
R]" /> <a href="admin/">admin/</a>                  27-Jul-2007 19:32    -
<

img src="/icons/folder.gif" alt="[DIR]" /> <a href="calendar/">calendar/</a>
            08-Dec-2008 10:59    -
<img src="/icons/folder.gif" alt="[DIR]"

/> <a href="cgi-bin/">cgi-bin/</a>                16-Feb-2004 12:19    -

COLUMN_VALUE
--------------------------------------------------------------------------------

<im

g src="/icons/folder.gif" alt="[DIR]" /> <a href="edi_pdf/">edi_pdf/</a>
         05-Feb-2009 19:00    -
<img src="/icons/folder.gif" alt="[DIR]" />

<a href="flags/">flags/</a>                  20-Sep-2004 17:33    -
<img src

="/icons/folder.gif" alt="[DIR]" /> <a href="images/">images/</a>
   15-Aug-2007 18:08    -

COLUMN_VALUE
--------------------------------------------------------------------------------

<img src="/icons/folder.gif" alt="[DIR]" /> <a hre

f="mdefiles/">mdefiles/</a>               17-Nov-2006 08:13    -
<img src="/

icons/folder.gif" alt="[DIR]" /> <a href="statistik/">statistik/</a>
   26-Jan-2005 12:37    -
<img src="/icons/folder.gif" alt="[DIR]" /> <a hre

f="testomat/">testomat/</a>               21-Jun-2006 17:52    -
<hr /></pre

COLUMN_VALUE
--------------------------------------------------------------------------------


>
<address>Apache/2.0.40 Server at xx.xx.xx.xx Port 80</address>
</body></html

>

20 rows selected.

SQL>
Re: Getting Data From the web without PL/SQL [message #385151 is a reply to message #385141] Fri, 06 February 2009 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But is this WITH or WITHOUT PL/SQL?

Regards
Michel
Re: Getting Data From the web without PL/SQL [message #385153 is a reply to message #385151] Fri, 06 February 2009 11:52 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, in the actual code that is executed by whichever application Folly is using that can apparently only use SQL and not PL/SQL it's WITHOUT PL/SQL. Grin

Re: Getting Data From the web without PL/SQL [message #385154 is a reply to message #385147] Fri, 06 February 2009 11:56 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Try utl_http.read_line, it avoids chopping up the html in the middle of tags.


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:285215954607#390995500346399303

create or replace function http2line (p_url in varchar2)
return ROW_TYPE pipelined
is
 req utl_http.req;
 resp utl_http.resp;
 rv VARCHAR2(4000);
BEGIN
  -- utl_http.set_proxy('proxy.my-company.com','corp.my-company.com');
  req := utl_http.begin_request(p_url);
  -- utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
  resp := utl_http.get_response(req);
  LOOP
    utl_http.read_line(resp, rv, TRUE);
    pipe row (rv);
  END LOOP;
  utl_http.end_response(resp);
  return;
EXCEPTION
  WHEN utl_http.end_of_body THEN
  utl_http.end_response(resp);
  return;
END;
/

-- line by line result
select rownum, column_value from table(http2line('http://xxx.yyy.com'));
Re: Getting Data From the web without PL/SQL [message #385157 is a reply to message #385130] Fri, 06 February 2009 12:48 Go to previous messageGo to next message
Folly
Messages: 5
Registered: February 2009
Junior Member
Hey guys,

This info looks fantastic, I'll have a play around and let everyone know how it went.

Thanks again

Folly
Re: Getting Data From the web without PL/SQL [message #385215 is a reply to message #385157] Sat, 07 February 2009 18:02 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If we understand you, you can write all the plsql you want, you just can't let anyone outside the database see it. When faced with this, the best approach is to construct a view that gives you what you want. The view being your interface into the database is usable by anything that speaks sql. You can then make the view do what ever you want using the techniques described by others in this post.

Pipelined functions (eg. table functions will do the trick). Just confirming that you can actually write all the plsql you want, you just can not execute it directly. Is this true?

Good luck Kevin
Re: Getting Data From the web without PL/SQL [message #385395 is a reply to message #385130] Mon, 09 February 2009 06:48 Go to previous messageGo to next message
Folly
Messages: 5
Registered: February 2009
Junior Member
rather annoyingly I am not able to create anything, this means
create or replace function http2line (p_url in varchar2) won't work.

I think I'm well and truely buggered now?!
Re: Getting Data From the web without PL/SQL [message #385398 is a reply to message #385395] Mon, 09 February 2009 06:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Getting Data From the web without PL/SQL [message #385403 is a reply to message #385398] Mon, 09 February 2009 07:02 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course "not able to create anything" is not really an Oracle error, neither is "won't work", so we don't know WHY you are not able to create anything, and so we can't suggest possible solutions.
Previous Topic: Avoid Duplicate
Next Topic: Query Help
Goto Forum:
  


Current Time: Sun Dec 11 00:43:31 CST 2016

Total time taken to generate the page: 0.09017 seconds