Archive for the ‘Oracle’ Tag
Email BLOB Attachments From The Database
Filed under: Oracle, PL/SQL, SQL | Tags: BLOB, email attachment, Oracle, PL/SQL, SMTP
Comments (7) Recently I was developing an oracle database application and had the requirement to send out an email with an attachment. Initially it sounded pretty easy. I’ll just use the APEX_MAIL package and its all handled for me. But then I remembered, this was not an APEX application I was developing. So then I thought, no problem, I’ll just use the UTL_MAIL package. There is an API to add attachments. But wait, you can only add VARCHAR and RAW attachments and there is a max length of 32K on them. Not very useful if you want to send a multi-megabyte document as an attachment ( ie. a blob). So I had to drop down to an even lower API, UTL_SMTP and handle it all myself. Oh, and the attachment needed to be zipped as well (to save space in the tubes of our interwebs). I found the exercise interesting and thought I would share it with you.
For this example we have a table
create table assets( id number primary key, name varchar2(100), content blob mime_type varchar2(100) );
And the code
procedure mail_asset(
p_asset_id number,
p_from varchar2,
p_to varchar2,
p_subject varchar2,
p_message varchar2 ) is
--
l_asset assets%rowtype;
l_blob blob := to_blob('1');
l_conn utl_smtp.connection;
l_raw raw(57);
l_len integer := 0;
l_idx integer := 1;
l_buff_size integer := 57;
l_boundary varchar2(32) := sys_guid();
l_attachment_name long;
begin
-- Connect
l_conn := utl_smtp.open_connection( 'mail.oracle.com' );
utl_smtp.helo( l_conn, 'oracle.com' );
utl_smtp.mail( l_conn, p_from );
utl_smtp.rcpt( l_conn, p_to );
utl_smtp.open_data(l_conn);
-- Header
utl_smtp.write_data( l_conn, 'From: ' || p_from || utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'To: ' || p_to || utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'Subject: ' || p_subject ||
utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'MIME-Version: 1.0' || utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'Content-Type: multipart/mixed; ' ||
utl_tcp.crlf );
utl_smtp.write_data( l_conn, ' boundary= "' || l_boundary || '"' ||
utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf );
-- Body
utl_smtp.write_data( l_conn, '--' || l_boundary || utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'Content-Type: text/plain;' ||
utl_tcp.crlf );
utl_smtp.write_data( l_conn, ' charset=US-ASCII' || utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf );
utl_smtp.write_data( l_conn, l_message || utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf );
-- Fetch the asset info
for c in ( select *
from assets
where asset_id = p_asset_id ) loop
-- Compress if a content and not already zipped.
if ( c.mime_type != 'application/zip' then
utl_compress.lz_compress( src => c.content,
dst => l_blob );
l_attachment_name := c.name || '.gz';
else
l_blob := c.content;
l_attachment_name := c.name;
end if;
-- Attachment
utl_smtp.write_data( l_conn, '--' || l_boundary || utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'Content-Type: application/octet-stream'
|| utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'Content-Disposition: attachment; ' ||
utl_tcp.crlf );
utl_smtp.write_data( l_conn, ' filename="' || l_attachment_name || '"'
|| utl_tcp.crlf );
utl_smtp.write_data( l_conn, 'Content-Transfer-Encoding: base64' ||
utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf );
-- Loop through the blob
-- chuck it up into 57-byte pieces
-- and base64 encode it and write it into the mail buffer
l_len := dbms_lob.getlength(l_blob);
while l_idx < l_len loop
dbms_lob.read( l_blob, l_buff_size, l_idx, l_raw );
utl_smtp.write_raw_data( l_conn, utl_encode.base64_encode(l_raw) );
utl_smtp.write_data( l_conn, utl_tcp.crlf );
l_idx := l_idx + l_buff_size;
end loop;
utl_smtp.write_data( l_conn, utl_tcp.crlf );
end loop;
-- Close Email
utl_smtp.write_data( l_conn, '--' || l_boundary || '--' ||
utl_tcp.crlf );
utl_smtp.write_data( l_conn, utl_tcp.crlf || '.' || utl_tcp.crlf );
utl_smtp.close_data( l_conn );
utl_smtp.quit( l_conn );
exception
-- smtp errors, close connection and reraise
when utl_smtp.transient_error or
utl_smtp.permanent_error then
utl_smtp.quit( l_conn );
raise;
end mail_asset;
Just a few things about the code.
Line 15 – I use sys_guid() to generate a random 32 character string which I use as the boundary marker in the email message. You can use any character string you like.
Lines 20 and 21 – You will need to change to your smtp server.
Line 34 – You will notice that there is a space in front of boundary. That is on purpose. The reason for that is that Content-Type (Line 32) and the boundary could be on the same line like this:
Content-Type: multipart/mixed; boundary=”xxxxxxxxxxxx”
But for formatting sake, I broke it up into two lines, so it needs to be sent to the smtp server like
Content-Type: multipart/mixed;
boundary=”xxxxxxxxxxxx”
^ (single leading space)
with a leading space on boundary. I also did it on lines 42 and 68.
Line 79 – No you can not do larger reads. Each line of the encoded attachment needs to be 57 bytes.
In my example, I will only get at most 1 attachment because I pass in the asset_id and that is defined as the primary key. But if the query at line 48 was changed to possibly return more than a single row, the code would still work and we would have an email with multiple attachments.
Enhanced APEX Shuttle Item
Filed under: APEX, Oracle, PL/SQL, SQL | Tags: APEX, JSON, Oracle, PL/SQL, shuttle
Comments (4) Recently I have been really, really busy building some internal applications for my company. All of them using Oracle and APEX of course. To satisfy some of application’s requirements, and just for usability sake, I had to tweek the default behavior of the APEX shuttle item. The reason was the initial LOV that populated the shuttle contained over 500 values. About 490+ too many as far as I was concerned. The users felt the same way. They complained that it was too hard to find what they were looking for, even though the list was in alphabetical order and I agreed. So, I decided to add the functionality to allow the user to constrain the values in left-hand side of the shuttle. Similar to the Popup LOVs with a filter option, but I wanted to use AJAX to accomplish it to avoid a page submit. After a bunch of playing around and optimization, here is what I came up with:
http://apex.oracle.com/pls/otn/f?p=19903:10
Username/Password: demo/demo
I was surprised that it was not that hard to accomplish and there are very few moving parts. All you need is a javascript function to make the AJAX call, an On Demand process to fetch only those values that meet the filter’s constraint and a way to trigger it.
How It Works.
As the user types in the textbox the shuttle automagically filters itself. I use an onKeyUp event on the textbox P10_SEARCH_BY to trigger the AJAX call.
onKeyUp="populateShuttle( this,'P10_SHUTTLE');"
So everytime a KeyUp event happens in that textbox, a call to the populateShuttle function happens. That function is responsible for making the AJAX call to the On-Demand Process, which fetches the appropriate values, and then parsing the results and repopulating the left hand side of the shuttle.
function populateShuttle(filter,shuttleName)
{
var right = $x(shuttleName+"_RIGHT");
for ( var i=0;i<right .length; i++ )
right.options[i].selected = true;
var req = new htmldb_Get( null, 19903,
'APPLICATION_PROCESS=populateShuttle', 0 );
req.addParam( 'x01', $v(right) );
req.addParam( 'x02', $v(filter) );
var resp = eval(req.get());
var left = $x(shuttleName+"_LEFT");
left.length=0;
if (resp)
for ( var i=0; i<resp.length; i++ )
left.options[i] = new Option(resp[i].data, resp[i].id);
req = null;
}
Although APEX treats the shuttle as a single element, it is in fact made up of many HTML elements, 10 images and 2 multiselect list boxes. With a little poking around the HTML of a generated APEX page with a shuttle item on it, I was able to determine that the actual DOM names of the multiselect list boxes of a shuttle were [SHUTTLE_NAME]_LEFT and [SHUTTLE_NAME]_RIGHT. How convenient. Accessing the actual elements was then trivial. I just used the APEX supplied function $x() function to get the elements.
The first thing I do is get the right textbox and loop over its values, selecting each one. This is so when I fetch its value, I get all the elements in the list. I need this information so when I fetch the values for the left hand side of the shuttle, based on the user’s filter, I do NOT bring back any values that have already been moved to the right hand side of the shuttle. In my example say you had already selected KING and moved him to the right. Then you type a ‘K’ in the filter_by textbox, you will only get back CLARK and BLAKE even though KING also meets the constraint of containing a ‘K’.
Next, I get the value of the right hand side of the shuttle and the value of the filter_by textbox, using the $v() function and send the values to the On Demand process via the APEX global variables g_x01 and g_x02.
The On Demand process is just a simple PL/SQL block that is a query loop.
declare
l_selected long := wwv_flow.g_x01;
l_search long := wwv_flow.g_x02;
begin
htp.prn('[');
for c in (
select ename, empno, rownum r
from emp
where regexp_like( ename, nvl(l_search,ename), 'i' )
and nvl(instr(':'||l_selected||':',':'||empno||':'),0)=0 )
loop
htp.prn( case when c.r>1 then',' else null end ||
'{ id: ' || c.empno ||
', data: "' || c.ename || '"}' );
end loop;
htp.prn(']');
end;
It fetches all the values that match the filter
where regexp_like( ename, nvl(l_search,ename), 'i' )
but not those already be selected by the user.
and nvl(instr(':'||l_selected||':',':'||empno||':'),0)=0)
Now it’s just a simple process of looping over the rows, packaging them up and sending them back. I have become a big fan of JSON ( JavaScript Object Notation http://www.json.org/ ) , so that how I am packaging up the payload to pass back. Basically, I’m making the results into a javascript array of objects. Each object has an id and data element. The payload look something like this
[{id: 7839, data: "KING"}{id: 7698, data: "BLAKE"} ... ]
This makes it super simple easy to work with once it gets back to the calling javascript function. All you need to do is apply an eval() on it and its transformed into a true javascript array of objects that can be easily looped over and referenced.
In one line of code, I call the On Demand process, getting back the JSON packed result and eval() it.
var resp = eval(req.get());
Now the variable resp is an Array of objects with two elements in each object, id and data. Looping over the values and populating the left hand side of the shuttle is trivial now.
if (resp)
for ( var i=0; i<resp.length; i++ )
left.options[i] = new Option(resp[i].data, resp[i].id);
And there you have it. I hope I have shown that with some minor tweeks, you can extend the functionality of the base APEX items to be much more functional and user friendly. As always, comments ( good and bad ) are always welcome. Read more »
Pipelined Functions
Filed under: APEX, Oracle, PL/SQL | Tags: APEX, Oracle, Pipeline Function
Comments (4) Well, it’s been a quite while since I posted (I know, I’m slacking) so I thought I’d just quickly blog about what I just helped someone with yesterday. It required me to use a pipelined function. Not everyone has seen of them so I thought it may be interesting.
Pipelined functions allow you to basically select from a function rather than a database table.
A colleague of mine had to build an APEX report of opportunities. The report was to be constrained by a multiselect listbox of product ids. Show all opportunities that have ANY of the selected product ids. So far it sound pretty easy. The tricky part was how the product ids were stored in the opportunity table.
create table opportunity( name varchar2(100), product_ids varchar2(255) );
The PRODUCT_IDS column was populated with a colon delimited list of all the products that were used in that opportunity. Now before you yell that that is probably not the best way to store that data and that a master-detail table relationship is probably the better way to go, I 100% agree. But unfortunately, when you inherit an application, you many times must make do with what the original developer put in place (and that is the case here).
We all know when you submit a multiselect listbox in APEX, it comes in as a colon delimited list of all the values that were selected. What we need to do is tokenize that selectlist string and compare each value against the table.
I came up with two possible solutions:
1. Build the query by hand.
Using apex_util.string_to_table() on the input, loop over all the values and manually build the report query by hand.
declare
l_ids apex_application_global.vc_arr2;
l_query varchar2(32767) := null;
begin
l_ids := apex_util.string_to_table( :p9_product_ids );
for i in 1 .. l_ids.count loop
l_query := l_query || <insert query here>;
if i <> l_ids.count then
l_query := l_query || ' union all ';
end if';
end loop;
return l_query;
end;
This would have worked just fine (besides having to run the query N times and union all them together).
But I like to use nifty, (and more optimized) options and so I implemented my other solution:
2. Use a pipelined function.
To use this, we need to create a type and a function that returns that type.
create or replace type myTableType as table of varchar2(255) /
create or replace
function to_myTableType( p_string varchar2 )
return myTableType PIPELINED is
l_arr apex_application_global.vc_arr2;
begin
l_arr := apex_util.string_to_table( p_string );
for i in 1 .. l_arr.count loop
PIPE ROW( ':' || l_arr(i) || ':' );
end loop;
return;
end;
/
Notice that the function is declared as PIPELINED in its declaration. Also notice the PIPE ROW() syntax in the loop. That is where the function returns multiple values as rows in a query. Its using the string_to_table() function to tokenize the string and then looping over the tokens, pipes the results out as rows. And finally, this function contains a return clause without returning anything. It had already returned multiple values in the PIPE ROW() line.
Now that we have this we can write a single, simple query in APEX that looks like this:
select o.name, o.product_ids
from opportunity o,
table( to_myTableType( :p1_product_ids )) p
where instr( ':' || o.product_ids || ':', p.COLUMN_VALUE ) > 0;
Check out a working version here: http://apex.oracle.com/pls/otn/f?p=19903:9
WebLogic Server and APEX
A good friend and colleague of mine at Oracle, Mark Greynolds, shared with me his work with WebLogic and APEX and getting them to work together. I thought it was something that everyone would be interested in and asked him if he would be willing to document the steps and allow me to post them here on my blog. He was and so here it is.
WebLogic Server and APEX
When a WebLogic Server (WLS) is the primary Web server, accessing APEX pages though the WLS requires a proxy. The configuration of APEX generally follows one of two configurations – Apache with mod_plsql or the Embedded PL/SQL gateway. When WebLogic (without Apache) is the main HTTP server, getting APEX to surface on the same port as WebLogic requires some form of proxy.
Overall Approach
This solution creates a very simple Web Application that invokes a Java Proxy Servlet when a user tries to access APEX pages. Wrapping this Web Application around the Java Proxy Servlet lets the WLS serve APEX without any port conflicts. The WLS Proxy Servlet is a fully documented out of the box tool. To create and deploy the Web Application simply build the files outlined in this document, deploy the application and then access APEX.
Exploded deployment
For convenience, this solution takes advantage of the exploded deployment feature of the WSL. In addition to the ability to deploy jar, war and ear files, the WLS can deploy Web Applications as an exploded directory that contains the same contents of the archive file. An exploded archive directory contains the same files and directories as a jar archive. However, the files and directories reside directly in the file system instead of as a single archive file (JAR). This example uses the exploded deployment style to create the Web Application for this example.
Default WebLogic application
The default Web Application is what the server sends to browser clients who do not specify a recognized URI (or specify only “/” as the URI). Each server has only one default Web Application and for this solution to work, this application must be set as the default. If there is already a default, this servlet could be added to the existing application by using an exploded deployment of the default with modification to the web.xml to register the APEX proxy.
Pre-requisites
1. An Oracle database successfully serving APEX pages. The APEX instance may be on the same or different machine and served from either Apache or the Embedded PL/SQL gateway. In this example, APEX uses the Embedded PL/SQL Gateway of a database running on the same machine as the WebLogic server and natively appears at the http://localhost:8080/apex URL.
2. An Oracle WebLogic Server 10.3 running a Node Manger, the Administration Console and a Managed server. This example uses a domain created specifically for this exercise named APEXDemo. The WLS Administration console uses port 7001 and the Managed Server uses port 80.
3. There is no other “default” WebLogic application for the Managed Server.
Create the APEX Proxy Servlet
1. Create the following directory structure somewhere on disk. This example assumes the C:\ drive is used. Note: the apexproxy.war directory name mimics the normal J2EE naming convention for Web Application archive (WAR).
2. In the apexproxy.war directory, create the index.html file. The WLS Managed Server renders this page when the server cannot map the browser’s URL to a valid destination. For this example, APEX becomes the default page due to a simple redirect to the full APEX path.
<html>
<head>
<meta http-equiv="refresh" content="0;url=apex">
</head>
<body>
</body>
</html>
3. In the WEB-INF directory, create the web.xml file that defines this simple Web Application.
<?xml version="1.0" encoding="UTF-8"?>
<web-app>
<display-name>APEX Proxy</display-name>
<servlet>
<servlet-name>ProxyServlet</servlet-name>
<servlet-class>
weblogic.servlet.proxy.HttpProxyServlet
</servlet-class>
<init-param>
<param-name>redirectURL</param-name>
<param-value>http://localhost:8080</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>ProxyServlet</servlet-name>
<url-pattern>/apex/*</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>ProxyServlet</servlet-name>
<url-pattern>/i/*</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
</welcome-file-list>
</web-app>
The <servlet-class> tag identifies the proxy servlet class and provides the
redirection URL to use when the container invokes the servlet. The two
<servlet-mapping> tags explicitly identify the two URL patterns used by APEX.
The WLS documentation suggests using a single mapping of “/” but this causes every
unmatched request to forward to the Oracle XML DB default splash page and ignores the
index.html file.
4. In the WEB-INF directory, also create the following weblogic.xml file. At deployment, WLS scans this file for the information on how to configure the deployment.
<?xml version='1.0' encoding='UTF-8'?>
<weblogic-web-app
xmlns="http://www.bea.com/ns/weblogic/weblogic-web-app"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.bea.com/ns/weblogic/weblogic-web-app
http://www.bea.com/ns/weblogic/weblogic-web-app/1.0/weblogic-web-app.xsd">
<session-descriptor></session-descriptor>
<jsp-descriptor></jsp-descriptor>
<container-descriptor></container-descriptor>
<context-root>/</context-root>
<servlet-descriptor>
<servlet-name>ProxyServlet</servlet-name>
</servlet-descriptor>
</weblogic-web-app>
The <context-root> tag is the key for making this example function. This tag
makes this the default application for the Managed Server.
Deploy the Proxy Servlet
5. Begin the deployment by accessing the WLS Administration Console using the http://localhost:7001/console URL. For this example, the Administration Console’s Username and Password are weblogic and weblogic.
6. On the left side navigation panel, click the Deployments link.
7. Click the Install button.
8. Use the Path: field to locate the C:\APEXProxy directory. NOTE: this page can
navigate directories on the drive by entering C:\ and clicking the Next button. The server
returns an error and shows the directories in a navigable list.
Click the radio button next to the apexproxy.war directory name and then click the
Next button. NOTE: do not type in the complete path to the war directory – clicking the
radio button for the WAR automatically completes the Path specification.
9. Click the Next button to accept installing the deployment as an application.
10. Select the desired server and click the Next button.
11. Click the Finish button to accept default values for all the remaining deployment values
and finish the deployment.
12. The Administration Console displays the configuration page for the newly deployed
application with the following messages.
Lower down the page, the server display the status of the new application.
13. Enter the Managed Server URL in to a browser to see the APEX login page.
http://localhost
The browser receives a redirect from the index.html page and displays the APEX login
screen.
Report Hierarchical Data With APEX
Filed under: APEX, SQL | Tags: APEX, CONNECT BY, hierarchy, Oracle, SQL
Comments (4) Anyone familiar with Oracle and hearing the word hierarchical immediately thinks of the sql CONNECT BY clause. And I would bet that when they think about displaying this data, they would use some sort of tree widget. APEX has a built-in tree widget and for many applications, it works fine.
The drawback to the built-in tree widget is that it brings back the entire dataset on the initial page render. Good and Bad. Saves round trips to the server, but could take a lot of initial time depending on the size of the data. Showing/hiding branches of the tree with many elements also can make the application feel sluggish.
On my current project, I needed to build an interface against just such a dataset. So I needed an alternative to the built-in tree. I looked at either writing my own tree using javascript and AJAX or using an already developed one. Neither one appealed to me. They both added development complexity and maintaining them weighed on my final decision.
What I chose to do was build a breadcrumb-like report displaying the path traversed and a second report displaying the elements at the current level. All default APEX functionality. Simple clean interface. Easily developed and easily maintained. You can check it out here.
Let me quickly walk you through how it was built:
1. Create a report on the base table constrained by the hidden item. The query should look something like:
select * from emp where nvl(mgr,-1) = :p5_empno
I then linked the ENAME column back to the same page passing the EMPNO value into 
5_EMPNO.
2. Create a hidden Item to hold the manager’s id. I put it in the step 1’s report region and called it P5_EMPNO and set its default value to -1.
3. Create a PATH report to manage the traversing the data. This is where the magic happens. I make use of the SYS_CONNECT_BY_PATH() function in conjunction with the START WITH…CONNECT BY clause. The query I used was:
select '<a href="f?p=' || :app_id || ':5:' ||
:app_session ||
'::::p5_empno:-1">Top</a> >>> ' ||
substr(
sys_connect_by_path(
'<a href="f?p=' || :app_id || ':5:' ||
:app_session || '::::p5_empno:' || empno ||
'">' || ename || '</a>', ' : ' ), 4 ) path
from emp
where empno = :p5_empno
start with mgr is null
connect by prior empno = mgr
Some other tweeks to this region. No Pagination. Report Template of Value Attribute Pairs. Layout above the first report region. No region template.
4. ???
5. Profit!
Now you can use the main report to drill into the children of the row you selected, all the while maintaining the context of where you are in the hierarchy with the path.
It’s a quick simple technique, and 100% APEX. No additional javascript libraries or custom coding to accomplish a clean and simple interface. And it will be easily maintained, either by you, or by the developer that comes after you.
Let me know what you think.
Regular Expression Searching With APEX
Filed under: APEX | Tags: APEX, Oracle, regexp_like, regular expressions
Comments (1) Ever since Oracle introduced regular expression functions in the database, I have been a big fan of them. They really make certain tasks much easier and give you added functionality. One place I always used them is in my APEX apps where I supply the user with a search box to constrain the results of a query.
The old way I would have written the query constraint:
where upper(COL1) like ‘%’ || upper( 1_SEARCH_TEXT ) || ‘%’
Now using REGEXP_LIKE() you can achieve the same functionality while simplifying the constraint. And you get the added bonus of advance searching for the power user.
where regexp_like( COL1, nvl(:P1_REGEXP,COL1), ‘ix’ )
The first thing you will probably notice is that there is no conditional operator in that expression. None is needed. The regexp_like function is a boolean function and is used as such.
So now, any basic search will work just as before. But now, you can issue regular expression searches as well. If you want all the entries that start with S, search for ^s
End with R, use r$
Start with S and end with H, use ^s.*h$
Start with S or end with R, use ^s|r$
All 4 letter names, use ^….$
Contains B, C, D or K, use [b-d,k]
How about all names with double letters, use (.)\1
There are many more things you can do. If you want to read up about regular expressions in oracle, docs can be found here.
Using regexp_like() opens up the advanced searching for all the regular expression junkies without compromising the simple searching and it add NO complexity to your applications.
I have staged a simple demo on apex.oracle.com, here
More Google Geocoding
Filed under: PL/SQL | Tags: Geocode, Google, Oracle, PL/SQL, Spatial
Comments (4) Ok, for those who liked the prior post on Google geocoding via HTTP, you will enjoy this as well. It builds on what was done in that example but retrieves even more information from google.
Before we used an output type of CSV and that only gave us back the LATITUDE and LONGITUDE. But there is more information that we can derive from the geocoding service if we use a different output type. If we request the result in XML, then we are given the complete address as well as the longitude and latitude. But we have to do a little extra work to parse out the results.
I have written a PL/SQL object type to do just that. Why an object type and not a Package? Personal preference. In this case, it just seemed to feel right to make it an object. With some simple code factoring, it could easily be converted into a package. But since most PL/SQL developers don’t code object types, thought it might be interesting.
Let’s take a look at the object’s specification.
create or replace
type google_geocode_type as object (
-- Attributes
response xmlType,
search varchar2(32767),
-- Constructor Methods
constructor function google_geocode_type(
p_search varchar2 default null ) return self as result,
-- Execute Geocode
member procedure execute(
p_search varchar2 default null ),
-- Getter Routines
member function get_result_count return number,
member function get_geometry(
p_sequence number default 1 ) return sdo_geometry,
member function get_latitude(
p_sequence number default 1 ) return number,
member function get_longitude(
p_sequence number default 1 ) return number,
member function get_address(
p_sequence number default 1 ) return varchar2,
member function get_street(
p_sequence number default 1 ) return varchar2,
member function get_city(
p_sequence number default 1 ) return varchar2,
member function get_state(
p_sequence number default 1 ) return varchar2,
member function get_zipcode(
p_sequence number default 1 ) return varchar2,
member function get_county(
p_sequence number default 1 ) return varchar2,
member function get_country(
p_sequence number default 1 ) return varchar2
);
The object’s body is a bit long and I will not post all of it here but will make it all available for download. But I will post a few of the member functions and talk about what they are doing.
constructor function google_geocode_type( p_search varchar2 default null ) return self as result is begin self.search := p_search; return; end;
For those who are not familiar with PL/SQL object types, a constructor function is the method called when you create a new instance of the object (just like java). Every object has a default constructor, but you can define your own so to manage the object’s creation as well as defaulting certain object attributes. For me, I did not want the use to have to supply a value for the attribute RESPONSE so I defined my own.
The EXECUTE() method is very similar to the the prior example. I made a minor change to handle the case where we get back more than 32K of data.
member procedure execute( p_search varchar2 default null ) is
l_http_req utl_http.req;
l_http_resp utl_http.resp;
l_response long;
l_clob clob;
begin
if p_search is not null then
self.search := p_search;
end if;
l_http_req := utl_http.begin_request(
url => 'http://maps.google.com/maps/geo' ||
'?q=' || utl_url.escape( self.search ) || -- address to geocode
'&output=xml' || -- XML return type
'&key=abcdef' ); -- site key
l_http_resp := utl_http.get_response( l_http_req );
begin
loop
utl_http.read_text( l_http_resp, l_response );
l_clob := l_clob || l_response;
end loop;
exception
when utl_http.end_of_body then
null;
end;
utl_http.end_response( l_http_resp );
self.response := sys.xmlType.createXML( l_clob );
end execute;
Now depending on how specific the address you supply, you may get more than one answer. I sent in a value of “1600 Pennsylvania Ave” and it returned 10 results. If I made that search more specific, say “1600 Pennsylvania Ave, DC”, then I get just a single result. But since we don’t know how many results we will get, we need to parse out all the results. So the first thing we need to know is how many results came back.
member function get_result_count return number is
l_count number;
begin
select count(*)
into l_count
from table( XMLSequence( extract(
self.response,
'//Placemark',
'xmlns="http://earth.google.com/kml/2.0"' ) ) );
return l_count;
end get_result_count;
This method calculates how many results are in the XML returned by finding each <Placemark> section, and then counting them using the XMLSequence and Table functions in SQL.
Now we know how many results, we can begin to ask for the individual values.
member function get_zipcode(
p_sequence number default 1 ) return varchar2 is
begin
return self.response.extract(
'//Placemark[@id="p' || p_sequence || '"]',
'xmlns="http://earth.google.com/kml/2.0"' ).extract(
'//PostalCodeNumber/text()',
'xmlns="urn:oasis:names:tc:ciq:xsdschema:xAL:2.0"' ).getStringVal();
end get_zipcode;
Here in the GET_ZIPCODE() method, we use the EXTRACT() method on the XMLType variable RESPONSE and XPATH searching to retrieve the proper zipcode. I had to do and extract() of an extract() because the relavant peices of data were in different namespaces in the XML. (My XPATH searching ability is not that strong so there may be an easier way? If you know of one, let me know.)
All the other get methods are similiar to this example.
Now let’s see how we would use this.
1 declare 2 g google_geocode_type := new google_geocode_type(); 3 begin 4 g.execute( '1910 Oracle way, reston' ); 5 dbms_output.put_line( 'lat/lon: ' || 6 g.get_latitude()||'/'|| 7 g.get_longitude() ); 8 dbms_output.put_line( 'Address: ' || g.get_address() ); 9 dbms_output.put_line( 'County: ' || g.get_county() ); 10* end; SQL> / lat/lon: -77.351976/38.954872 Address: 1910 Oracle Way, Reston, VA 20190, USA County: Fairfax County PL/SQL procedure successfully completed.
Given just the street and the city, google was able to determine the full address and the latitude and longitude.
Now let’s see what happens when we supply even less information
1 declare 2 g google_geocode_type := new google_geocode_type(); 3 begin 4 g.execute( '1600 Pennsylvania Ave' ); 5 for i in 1 .. g.get_result_count() loop 6 dbms_output.put_line( 'lat/lon: ' || 7 g.get_latitude(i)||'/'|| 8 g.get_longitude(i) ); 9 dbms_output.put_line( 'Address: ' || g.get_address(i) ); 10 end loop; 11* end; SQL> / lat/lon: -90.229033/38.617594 Address: 1600 Pennsylvania Ave, St Louis, MO 63104, USA lat/lon: -76.880242/42.031789 Address: 1600 Pennsylvania Ave, Pine City, NY 14871, USA lat/lon: -82.984848/42.36331 Address: 1600 Pennsylvania St, Detroit, MI 48214, USA lat/lon: -76.634388/39.30307 Address: 1600 Pennsylvania Ave, Baltimore, MD 21217, USA lat/lon: -96.77534/32.759033 Address: 1600 Pennsylvania Ave, Dallas, TX 75215, USA lat/lon: -81.620803/38.360844 Address: 1600 Pennsylvania Ave, Charleston, WV 25302, USA lat/lon: -80.27183/40.687529 Address: 1600 Pennsylvania Ave, Monaca, PA 15061, USA lat/lon: -79.8573/40.362383 Address: 1600 Pennsylvania Ave, West Mifflin, PA 15122, USA lat/lon: -117.32709/34.084866 Address: 1600 Pennsylvania Ave, Colton, CA 92324, USA lat/lon: -75.185584/40.121061 Address: 1600 Pennsylvania Ave, Oreland, PA 19075, USA PL/SQL procedure successfully completed.
There sure are a lot of 1600 Pennsylvania Ave’s. But if you notice, the most famous one is not even in the list? Why? I have NO idea??? But if we make our search a bit more specific…
1 declare 2 g google_geocode_type := new google_geocode_type(); 3 begin 4 g.execute( '1600 Pennsylvania Ave, dc' ); 5 for i in 1 .. g.get_result_count() loop 6 dbms_output.put_line( 'lat/lon: ' || 7 g.get_latitude(i)||'/'|| 8 g.get_longitude(i) ); 9 dbms_output.put_line( 'Address: ' || g.get_address(i) ); 10 end loop; 11* end; SQL> / lat/lon: -77.036698/38.897102 Address: 1600 Pennsylvania Ave NW, Washington, DC 20006, USA PL/SQL procedure successfully completed.
… we find that famous one that will be getting a new family moving in this January.
I hope this was interesting and helpful. Again, if you want to get the code and try it out yourself, its right here. And as always, if you have any questions, just ask.
Quick Geocoding Using Google
Location based services are all the rage these days. Everyone is including it into their applications and there are many ways to accomplish this. Oracle supports geocoding in the database but you need to own the geocoding dataset. To get that dataset, you would probably need to purchase it from some provider like NAVTEQ.
But there are services out there, like Google, that allows us common folk to use its data, but you have to know how to ask for it. Google offers a ton of Javascript APIs for mapping and geocoding, but that does nothing for us working in the database with PL/SQL. I routinely want to geocode the addresses in a table on insert and update via a trigger. Javascript is not going to help you here.
Luckily, Google supports geocoding via HTTP and I have written a simple function to access it. ( Oracle also supports a geocoding solution via HTTP and has a hosted site to support it. I will blog about it in the future. )
create or replace
function google_geocode( p_address varchar2 ) return sdo_geometry is
l_http_req utl_http.req;
l_http_resp utl_http.resp;
l_response long;
l_latlon long;
begin
l_http_req := utl_http.begin_request(
url => 'http://maps.google.com/maps/geo' ||
'?q=' || utl_url.escape( p_address ) || -- address to geocode
'&output=csv' || -- simplest return type
'&key=abcdef' ); -- Google API site key
l_http_resp := utl_http.get_response( l_http_req );
utl_http.read_text( l_http_resp, l_response );
utl_http.end_response( l_http_resp );
l_latlon := substr( l_response, instr( l_response, ',', 1, 2 ) + 1 );
return sdo_geometry(
2001, 8307,
sdo_point_type( to_number( substr( l_latlon, instr( l_latlon, ',' )+1 )),
to_number( substr( l_latlon, 1, instr( l_latlon, ',' )-1 )),
null ),
null, null );
end google_geocode;
That’s it. It’s not that complicated. You just need to send via HTTP the address and you will get back a comma separated string which we parse out the latitude and longitude and stuff into a sdo_geometry type.
I’ll quickly dissect the code.
Line 9 – Initializing the HTTP call. The URL includes 3 parameters ( q, output and key )
- q is the address string to be geocoded.
- output is the format that we want the out returned in. I chose csv. It’s the simplest for this example.
- key is the your Google API key. ( Read more about getting a Google API key ).
Lines 15-17 – Sending the request and fetching the result via HTTP. The result that the comes back is in the form of:
- HTTP_return_code,accuracy,latitude,longitude
Line 18 – Substring the latitude and longitude.
Line 20 – Creating and returning the SDO_GEOMETRY
Now you can call this from your pl/sql code in the database, or code in your ApEx app ( since that too is stored and run in the database ) and geocode any address. Just like this:
SQL> select google_geocode( '1910 Oracle Way, Reston' ) g 2 from dual; G(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-77.351976, 38.954872, NULL), NULL, NULL)
I tried to set up a quick demo of this on apex.oracle.com here, but I need to get the access to run UTL_HTTP via setting up an ACL. I am trying to see if I can get the admins to allow that for me.
If you want to read more about the options Google offers for geocoding via HTTP, you can read about it here.
As always, if you have any questions, just ask.
Multiple Date Selector
I am currently working on a project using ApEx that requires the user to have the ability to select multiple dates. So I came up with this pretty cool modified monthly calendar to accomplish that task. And I thought I’d share it with everyone else.
First create a new page. On that page create a calendar based on the following query.
For the Calendar Attributes, the date column will be based on sysdate, and the display column will be based on dummy. This is just for sample purposes. In a real implementation, you would want to based this on a table with some meaningful dates.
Next we need a MultiSelect list box to store all the selections. Create a new region in the second column of the page and then create a MultiSelect Item in that region. I based my LOV on another query that returns no rows since this is just a demo with no initial date to populate. The query I used was
select dummy a, dummy b from dual where 1=0
I called my element P2_DATES and set the height to 25. When you are done, your page will look something like this…
null
.
Ok, now for the magic. We want to be able to select a day, have it populate the list on the right and also give a visual indication that we have selected it. We will need to slightly modify the calendar template so we can locate each individual calendar cell. ( Or if you want to, copy the existing template and then use and modify that. Just remember, if you do do that, you need to set the calendar on the page to use the new copied template. ) From the ApEx development you will see a list of all the templates being used on the page
null
Click on the calendar template and then select the monthly template. We need a way for the javascript that we will write to locate the specific <TD></TD> that is the individual date we click on. We will give each date cell a unique ID.
In the Weekday format section, modify the Day Open Format to include the following ID attribute:
<td class=”t13Day” valign=”top” id=”CELL#YYYY##MM##DD#”>
And add the same ID attribute to both the Today Open Format:
<td valign=”top” class=”t13Today” id=”CELL#YYYY##MM##DD#”>
and the Weekend Open Format.
<td valign=”top” class=”t13WeekendDay” id=”CELL#YYYY##MM##DD#”>
Note: I am using the default template #13. If you chose a different default template, then your class attribute will be different. The important thing is that you add the correct ID attribute to the <TD> tag.
Now all that is left is to add the javascript that does the real magic. Go back edit the page and in the page section, add the following javascript to the Header Text
<script>
var selectedColor = '#CC3333';
function addToSelectList( pList, pDisplay, pValue )
{
var s = document.getElementById( pList );
s.length++;
s.options[s.length-1].text = pDisplay;
s.options[s.length-1].value = pValue;
}
function removeFromSelectList( pList, pValue )
{
var s = document.getElementById( pList );
for ( i=0; i<s.length; i++ )
{
if ( s.options[i].value == pValue )
{
s.remove(i);
return;
}
}
}
function toggleEvent( pCell, pList, pDisplay, pValue )
{
var cell = document.getElementById( pCell );
if ( cell.style.backgroundColor == "" )
{
addToSelectList( pList, pDisplay, pValue );
cell.style.backgroundColor = selectedColor;
}
else
{
removeFromSelectList( pList, pValue );
cell.style.backgroundColor = null;
}
}
</script>
Last step. We need a way to trigger the toggleEvent function. We will attach that to the calendar dates. For that, you will need to edit the Calendar Attributes and define the Day Link like this:
Click to get the full size image.
You are passing 4 parameters to the toggleEvent function. The first is the ID that we set up in the template, the second is the ApEx name of the multiselect list, the third is the display value in the list and the forth is the actual value that will be submitted from the list.
Now we are ready to test. Run the page and select any day
Now as you select the days, they turn red and the date is populated in the multiselect list. Select it again and it will toggle off.
I have set up a quick working demo here ( http://apex.oracle.com/pls/otn/f?p=19903:2 )
There is still much work to be done to make this a functional page. The submit processess need to be written to take the the dates from the select list and do something with them. You may also want to modify the query that the calendar is based on and prepopulate the calendar selected dates. But I will leave those exercises to the reader at home. (Boy did I hate it when my college professors said that in class.) I have actually done all of that but its a bit more than I wanted to blog about in my first post ( well second, my iPhone post was my first ). But if people are interested in enhancing this and have questions, just ask. And if enough people are interested, I will do a part 2.
Well I hope you found this useful. Check back as I will be adding more cool tips and tricks like this in the future.
