How to Display PHP Output as Portlet in HTML Region using utl_http.request
I wanted to be able to parse PHP functions to add more functionality to my Page. nothing fantasy, something simple like: http://phpsysinfo.sourceforge.net/phpsysinfo-dev/?template=classic
Where the realtime values are retrieved from the OS. this represent some kind of problem to me, I know how to do it from Shell Script, Perl or PHP, but not from PL/SQL.
Based on my experience using Oracle Portal, Portlets, I think we really can make use of the feature used in Portal called Web Clipping. [ This feature basically goes to some Site a retrieve part of the site to show in the current page]. Well the PL/SQL utility utl_http.request does something similar.
Make sure your request page does not retrieve some headers because that would cause a problem in your page and will not show up.
This is what I did:
<b> 1.- </b> Grant Privileges to execute [ <b> utl_http.request </b> ] to the owner of your schema.
login into the OracleXE / APEX as SYS then go to the Object Navigator, then click on packages, find the utl_http.request then click on it, later click on grant and select your schema owner for your applicaion.
<b> 2.- </b> Logout as SYS then login as your application schema user. just to test the functionality go to the sql command then enter the following command and then click run:
<b> select substr(utl_http.request('http://www.oracle.com/'),1, 255) from dual; </b>
If your output is:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html> <head> <title>Oracle 10g, Siebel, PeopleSoft | Oracle, The World's Largest Enterprise Software Company</title> <meta name="title" content="Enterprise Applications | Database | Fusio
<font color='blue'> Wow It worked </font>, this really could be useful !!, If not go back to step 1 and check your permissions to execute, or perhaps your Internet connectivity.
3.- Once we know the package works and we can retrieve content from the Internet. lets go to some of our pages add some HTML Region.
4.- Create an item and put into the new region. the source will be SQL, For the purposes of my test the query to the Oracle Page failed, so I decided to give a shot with a simple PHP script with no headers.
The internal server is: http://mytest.com/hello_world.php
Where the content of the hello_world.php is:
<?
echo '"<b> Hello World </b> ";
?>
This PHP works fine and display the basic Hello World. can be the same server or not, also noticed this can be another port perhaps using something like Ruby,Mason or Java.
<b> 5.- </b> Finally in my Item the source type is SQL and looks like this:
<b> select substr(utl_http.request('http://mytest.com/hello_world.php'),1, 255) from dual; </b>
Apply the changes and run the page !!!
Excelent It worked as expected. now you know how use utl_http.request to create webclipping in your OracleXE / APEX from remote pages, or how to use it to parse the output from CGI, Perl, PHP, Ruby or Java pages.
Note: [ Just make sure to strip the headers or parse into a PL/SQL Procedure to clean then show ]
Best Regards <b> Dino </b>.
Brains R Like Books only work when they R Open.
http://www.htmldbhosting.com/