Jornica
Get an eBook for free!
Database upgrade apex.oracle.com
Enabling SNMP traps in the Oracle Service Bus
Screen scraping using Google Documents in a minute or less…
SOA Suite 11g advanced training experiences
Google Maps in APEX on XE with spatial data
A picture says more than thousand words is well known saying, well let's have a look.
Choose your favorite country from the list, and the Google Map will pan to your country. As a bonus the selected country is colored red, the country shape is based on spatial data. You can zoom and move the map, or change the map type.
What do you need to build this application:
An Oracle XE database or better. The good news is that XE already contains the
MDSYSschema full of spatial utilities. The bad news is that XE has no spatial data included. But ...NAVTEQ provides sample data for Geocoder. Download, import and use it.
Apex, included with XE.
A Google Maps API key.
And it is all free, as in beer.
Just a remark about using the term spatial. As you can read here XE does not have the Spatial option included nor does it include Oracle Locator with or without Oracle Mapviewer, but XE contains the MDSYS schema and that is all we need.
Sign up for a Google Map API key here. For XE use the following URL http://127.0.0.1:8080/. to sign up. Take a look at the sample page shown after generation of your key.
Now create in APEX a page (in a new or existing application) and navigate to the page attributes.
In the Display Attributes section set the cursor focus to Do not focus cursor. In the HTML Header section insert the following code, replace ... with your Google maps API key.
<script src=http://maps.google.com/maps?file=api&v=2&key=...&sensor=false
type="text/javascript"></script>
In the HTML Body Header (section Header and Footer) insert the following code:
<script type="text/javascript">
//<![CDATA[
function load() {
if (GBrowserIsCompatible()) {
var map = new GMap2(document.getElementById("map"));
map.setCenter(new GLatLng(37.4419, -122.1419), 13);
}
}
//]]>
</script>
In the Page HTML Body attribute (section HTML Body attribute) insert the following code:
onload="load()" onunload="Gunload()"
Add a HTML Text region to the page with the following region source:
<div id="map" style="width: 500px; height: 300px"></div>
For Internet Explorer a small change of the page template is needed otherwise Internet Explorer stops responding. The first line of header (section Definition) should look like this:
<html lang="&BROWSER_LANGUAGE." xmlns:htmldb="http://htmldb.oracle.com" xmlns:v="urn:schemas-microsoft-com:vml">
Run the page and if everything is alright you will see a map of Palo Alto. This is the 'hello world' example of Google Maps. Note that it is required to issue the SetCenter statement otherwise only a grey pane is shown on the place of the map. You can also add some bells and whistles: map controls and polylgons (points connected by lines with a fill color as well).
Let us finish the web front end first before retrieving the geodata from the database. Modify the page created in the previous section.
Change the pagetype in PL/SQL (anonymous block). Insert the following in Region Source (section Source):
p_gis_data.draw_map(p_country=>:P1_COUNTRY);
The package p_gis_data is discussed later.
Add two items to the page: P1_COUNTRY (select list with submit) with the following dynamic list of values query:
SELECT initcap(country) display_value,
country return_value
FROM m_world
ORDER BY 1
And add a placeholder for the map P1_DIV (Display as text, does save state) with the following value for Source value of expression (section Source):
<div id="map" style="width: 500px; height: 300px"></div>
In order to initialize the Google Map add a before region process. This process sets the default value of the select list when there is no item selected.
IF :P1_COUNTRY IS NULLLoad sample data
THEN
:P1_COUNTRY := 'Netherlands';
END IF;
Download the sample data and unzip. Create a separate user and tablespace for the sample data:
SQL> CREATE TABLESPACE gis_data DATAFILE 'C:\ORACLEXE\ORADATA\XE\GIS_DATA.DBF' SIZE 600M ONLINE;
SQL> CREATE USER gis_data IDENTIFIED BY gis_data DEFAULT TABLESPACE gis_data TEMPORARY TABLESPACE temp;
SQL> GRANT CONNECT, RESOURCE TO gis_data;
Only execute steps 1 and 2 from the README. You do not need to install Mapviewer. After some time all the data is loaded.
It's time to retrieve spatial data from the database. Take a look at the M_WORLD table, there is a column named GEOMETRY with datatype SDO_GEOMETRY which stores all the spatial data. The GEOMETRY column stores the position (property GEOMETRY.SDO_POINT), more or less the center, of a country and one of more polygons. A polygon consists of points connected by (straight) lines. Remember the Google polygons?
Our task is to retrieve all polygons from the GEOMETRY column. Property GEOMETRY.SDO_ELEM_INFO tells us how many polygons the property GEOMETRY.SDO_ORDINATES stores and of which kind they are. The function SDO_UTIL.GETNUMELEM retrieves the number of elements for a given geometry, in our case the number of polygons. The SDO_UTIL.EXTRACT function extracts the nth element from a geometry returning a geometry object as well. And the function SDO_UTIL.GET_VERTICES retrieves all points of geometry. Combining these two functions will result in a number of geometry objects, each object corresponds with one polygon.
We can retrieve this information in one SQL statement:
SELECT country.geometry.sdo_point.x x,
country.geometry.sdo_point.y y,
country.element,
CURSOR
(SELECT x,
y
FROM TABLE(sdo_util.getvertices(sdo_util.EXTRACT(country.geometry, country.element)))
ORDER BY id) area
FROM
(SELECT iv.*,
LEVEL element
FROM
(SELECT *
FROM m_world
WHERE country = cp_name) iv
CONNECT BY LEVEL <= sdo_util.getnumelem(geometry)) country
/
The inline view iv selects one row from the M_WORLD table and for each element of the corresponding geometry a copy of this row is generated. In the country select each element is stored in a cursor expression. The next thing is to write some PL/SQL code to generate javascript based on this query i.e. p_gis_data.
The idea for this blog entry came after reading Creating Thematic Google Mapping Applications (for Business Intelligence) using Oracle Locator/Spatial and Application Express and Auf den Ort kommt es an: Geodaten in Application Express-Anwendungen nutzen (in german) and of course Oracle Spatial User's Guide and Reference.
The EMP mindmap
By accident, I stumbled upon a Java script library for generating mind maps. But instead of having a static diagram, this Java script enabled one is dynamic, you can move nodes, you can zoom in or out to another level of nodes. A very nice graphical way of presenting hierarchical data. For example, who's managing who in Oracle's EMP table as shown below. Come on, grab your mouse and start moving and clicking, it's alive!
Here's the recipe to build such an application.
Start with downloading the source of the Google code project js-mindmap. In the sources you will several Java script libraries, and an example file
js-mindmap.html.In APEX upload the Java script libraries add
references from the header section to these libraries.Add a PL/SQL region, which generates the hierarchy and will be converted in a diagram:
<canvas id="cv"></canvas>
<div id="js-mindmap">
<a>KING</a>
<ul>
<li>BLAKE
<ul>
<li>ALLEN</li>
...
<li>JAMES</li>
</ul>
...
</ul>
</li>
</div>For example, the function
show_emp_treereturns the html code for the<div>element.
Note that when you replace the EMP table by OEHR_EMPLOYEES table the animation will become very slow due to the increase of nodes and links. Also the js-mindmap project is still in development. But anyway it's fun!
To b:\ or not to B:\
Recently, I ran into a small issue with Java permissions. Starting point is the DirList Java procedure to list the content of an OS directory. Here is the code to get started (executed as SCOTT):
CREATE global TEMPORARY TABLE DIR_LIST ( filename VARCHAR2(255) ) ON
COMMIT
DELETE rows
/
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "DirList"
AS
import java.io.*;
import java.sql.*;
public class DirList {
public static void getList(String directory) throws SQLException {
File path = NEW File( directory );
String[]
list = path.list();
String element;
for(INT i = 0; i < list.length; i++) {
element = list[i];
#sql { INSERT INTO DIR_LIST (FILENAME) VALUES (:element) };
}
}
}
/
CREATE OR REPLACE PROCEDURE get_dir_list ( p_directory IN VARCHAR2 )
AS language java name 'DirList.getList( java.lang.String )';
/
Don't grant the role JAVAUSERPRIV but use a more granular option. Grant read permission to SCOTT on the d: drive:
EXECUTE dbms_java.grant_permission( 'SCOTT', 'java.io.FilePermission','d:\','read' );
Everything is in place now, time to run the procedure:
EXECUTE get_dir_list('D:\');
ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException:
the Permission (java.io.FilePermission D:\ read)
has not been granted by dbms_java.grant_permission to SchemaProtectionDomain(SCOTT|PolicyTableProxy(SCOTT))
ORA-06512: at "SCOTT.GET_DIR_LIST", line 0
ORA-06512: at line 2An error occurred, perhaps the directory did not exists? Executing a dir D:\ at the command prompt on the database server lists the files. The command prompt is not case sensitive, executing a dir d:\ returns the same listing. Perhaps but DBMS_JAVA.GRANT_PERMISSION is case sensitive?
EXECUTE get_dir_list( 'd:\' );
PL/SQL procedure successfully completed
SELECT * FROM DIR_LIST
/
FILENAME
--------------------------------------------------------------------------------
oracle
RECYCLER
System Volume Information
APEX 3.0.1 on XE
On 29 Februari 2008 Oracle released Application Express 3.1 . With this release you can upgrade the Application Express within Oracle XE to 3.1. For more information on this topic see Oracle Database XE and Application Express 3.1 and APEX 3.1 on XE .
Oracle released Application Express 3.0.1 recently. With this release you can upgrade APEX within Oracle XE from 2.1 to 3.0.1 (see Oracle Database XE and Application Express 3.0.1). But how to upgrade when you already installed APEX 3.0 on XE?
There is a patch available on Metalink to upgrade from APEX 3.0 to 3.0.1. According to the Patch Set Notes Oracle9i release 2 and higher support this patch if you have APEX 3.0.0.00.20 already installed (the Patch Set Notes also includes a list of fixed bugs). In order to download the patch from Metalink, you will need a support contract (which is not included with XE).
Another option is to remove APEX 3.0 completely. After the removal, XE has APEX 2.1. (still) installed and the next step is of course to install APEX 3.0.1. The big advantage of this option is you following the main route of upgrading XE/APEX as proposed by Oracle. However there are some things to keep in mind.
As always start with making a backup of your database.
Export all applications you would like to see again in APEX 3.0.1. If you remove APEX 3.0 completely, you also remove all applications stored in the
FLOWS_030000(APEX 3.0) schema! Note that applications stored in theFLOWS_020100schema will be migrated toFLOWS_030000(APEX 3.0.1).Make a backup of changed or added cascading style sheets (CSS), images and java scripts separately.
Run the
apex_3.0.1\apxremov.sqlscript (the script is the same for both versions).Run the
apex_3.0.1\apxins.sqlscript. Check the installation log for errors.Copy the
apex_3.0.1\imagesdirectory to the/i/directory. Also copy the previous backed up CSS, images and java scripts to the/i/directory.Import all applications exported in the first step. Note that existing applications in APEX 2.1 are migrated to APEX 3.0.1 as part of the upgrade process.
Time to make a backup again.
Have a look at the APEX reports in SQL Developer, no errors any more.
APEX 3.0 on XE part 2
On 29 Februari 2008 Oracle released Application Express 3.1 . With this release you can upgrade the Application Express within Oracle XE to 3.1. For more information on this topic see Oracle Database XE and Application Express 3.1 and APEX 3.1 on XE .
If you followed the guidelines of APEX 3.0 on XE, you have a working APEX 3.0 interface. However the administrative interface of APEX is not installed on XE by default. Using this interface you can manage all aspects of the service including creating, removing, and managing workspaces. Barney Mattox discovered a workaround to install this interface as well.
In order to install the administrative interface perform the following steps:
Start SQL*Plus. Make sure your working folder is the APEX installation folder (where
apexins.sqlis located).Connect as user
SYS.ALTER SESSION SET CURRENT_SCHEMA = FLOWS_030000
/The following command imports the administrative interface:
@script\f4050.sql- The following command imports the request for workspace interface:
@script\f4700.sql Change the password for the
ADMINuser in order to login in the administrative interface:@apxxepwd.sqlUse the following URL to access the administrative interface
http://server:port/apex/f?p=4550:10i.e.http://127.0.0.1:8080/apex/f?p=4550:10.
Additonal notes:
Issue a
spool install.logafter logging in to SQL* Plus. If there occurs an error SQL*Plus will exit. You can use the logfile to find out what went wrong.I experienced problems with step 3: Illegal security group id value for this schema. A workaround is to login as
FLOWS_030000and run the scripts. First you need to unlock userFLOWS_030000:ALTER USER FLOWS_030000 IDENTIFIED BY password ACCOUNT UNLOCK
/
APEX 3.1 On XE
On 29 Februari 2008, Oracle released Application Express 3.1 . The page Oracle Database XE and Application Express 3.1 provides upgrade instructions .
APEX_GLOBAL_ARRAYS
When you are working with global package arrays (apex_application.g_f01 ... apex_application.g_f50) in Oracle Application Express (APEX), the following package procedure apex_global_arrays can help in reducing development time especially time spent in the bugs and features department.
PACKAGE apex_global_arrays
IS
PROCEDURE print(maxlength NUMBER DEFAULT 20);
END;
The procedure print prints a HTML table with the contents of the arrays only if the page is run in debug mode. The parameter maxlength truncates the value of each table cell at the given number. You can call the procedure in a separate On Submit application process. In this way the table is rendered even when succeeding On Submit processes fail. Define once, execute everywhere. And of course, you can call it in page process as well.
A picture says more than a thousand words. Below is an example of an updateable report based on the emp table.
After pressing submit, the following output is shown. Compare the g_f01 array with the checkboxes above. The g_f01 array is not sparse like the g_f08 (commission) array. Also, when an array value does not exists, no table cell is printed, i.e. g_f05(9); King does not have a manager.
The source can be found here.
NotesDue to the name change of HTMLDB into APEX the public synonyms
apex_applicationandhtmldb_applicationpoint both to thewwv_flowpackage where the associative arrays are declared. The global package arrays are also known as referencing arrays in APEX documentation or associative arrays in the PL/SQL language.On apex.oracle.com you have to enter the debug mode after the page is submitted in order to render the table. On Oracle XE, you have to enter debug mode before submitting the page.
- You can install APEX_GLOBAL_ARRAYS in the parsing schema or in a separate schema. You can find the parsing schema of your application on application definition page. When you choose for a separate schema you have to grant execute right to the parsing schema and create a (public) synonym for APEX_GLOBAL_ARRAYS.
Introducing RuleGen
I've been working with CDM Ruleframe for a few years now. Recently I've attended a presentation about another framework focusing on business rules called RuleGen.
RuleGen is a framework written in PL/SQL that generates code to maintain data integrity constraints. Right now RuleGen implements table constraints, i.e. at most one president allowed in EMP, and database constraints, i.e. every department has at least two employees. Enforcing a data integrity constraint is done in two steps. The first step is about administering the affected rows of a transaction (inserts, updates and deletes). The second step is validating the constraint against the affected rows. If the constraint is violated an exception is raised. You can also say the first step is about WHEN the constraint is validated and the second step is HOW the constraint is validated.
There are switches to influence the runtime behavior of RuleGen like the execution model: stop on the first constraint violation or continue after the first constraint violation in order to collect a list of constraint violations (like the message stack in CDM Ruleframe). It is also possible to defer checking (in contrary to immediate checking).
A difference between CDM Ruleframe and RuleGen is the relationship with Oracle Designer. RuleGen is not integrated with Oracle Designer where CDM Ruleframe is. The definition (remember HOW and WHEN) of data integrity constraints is either done with SQL*Plus or with a small APEX application. Another difference between RuleGen and CDM Ruleframe is there is no PL/SQL coding required with RuleGen. The definition of data integrity constraints is done in SQL queries completely.
In my opinion, the functionality of RuleGen looks very promising. Keep an eye on it!
Tagged!
Gareth Roberts tagged me. Thanks for the invitation. And here are 8 things you did not know about me.
- My nickname Jornica is derived from Jorrit Nijssen. However five characters is often not enough for an username. By adding some extra random chosen characters ca it is long enough...
- I maintain another non Oracle related blog as well: Etc.
- Keywords of my favorite holidays: sea, sun and hills. Did I mention volcanoes?
- My roots are in the southern part of the Netherlands; I like music from artists like De Janse Bagge Bend, Gé Reinders and Rowwen Hèze.
- I like to watch fire engine movies on You Tube with my son.
- My family believes I'm a good cook.
- My favorite Linux distribution is homemade Linux From Scratch. It's about building your own Linux system from source code.
- I use Lifehacker as a source for new software: Windows tools and utilities.
And now, who's next (sorry 3 out of 8).
- Patch
- Ome-b (dutch)
- Software Engineer Thoughts
Integrating the Google Chart API in APEX
A few days ago, Google released the Google Chart API. By calling a special crafted URL Google returns a image containing a chart derived from the parameters included in the URL. For instance
http://chart.apis.google.com/chart?cht=p3&chd=t:3,5,6,0&chs=200x100&chl=10|20|30|40
&chco=ff0000,00ff00,0000ff,000000
will give the image shown left. At the right the Flash equivalent generated by APEX is shown.
To include this dynamic Google chart based on a query on a page, define a PL/SQL dynamic content region with the following PL/SQL source:
DECLARE
l_url VARCHAR2(2000) := 'http://chart.apis.google.com/chart?cht=p3&chs=200x100&chco=ff0000,00ff00,0000ff,000000';
l_chd VARCHAR2(2000);
l_chl VARCHAR2(2000);
BEGIN
FOR rec IN
(SELECT d.deptno LABEL,
COUNT(e.empno) VALUE
FROM dept d,
emp e
WHERE d.deptno = e.deptno(+)
GROUP BY d.deptno)
LOOP
l_chd := l_chd || ',' || rec.VALUE;
l_chl := l_chl || '|' || rec.LABEL;
END LOOP;
l_url := l_url || 'chd=t:' || SUBSTR(l_chd, 2) || '&chl=' || SUBSTR(l_chl, 2);
htp.p('<img src="' || l_url || '"/>');
END;
If you do not want to use the built-in Flash chart functionality, the Google Chart API is a simple alternative.
Mail from Application Express with Access Control Enabled
One of the post installation task after installing Oracle 11 is the creation of Access Control List (ACL). Oracle provides a few scripts in order to allow flows_030000 to connect any host. What if you do not allow the database to connect any host but only one host. For instance, you want to send mail from Application Express (APEX) ?
First, set up the email settings in the administrative interface (Home>Manage Service>Instance Settings). Secondly, run the following statements assystem.
-- Create an access control list
BEGIN
dbms_network_acl_admin.create_acl(acl => 'apexmail.xml',
description => 'Mail from Apex', principal => 'FLOWS_030000',
is_grant => TRUE, privilege => 'connect', start_date => NULL,
end_date => NULL);
END;
/
-- Assign the list to your SMTP host, i.e. smtp.yyy.xx
BEGIN
dbms_network_acl_admin.assign_acl(acl => 'apexmail.xml',
host => 'smtp.yyy.xx', lower_port => 25, upper_port => 25);
END;
/
-- Uncomment to remove access control list
--BEGIN
-- dbms_network_acl_admin.drop_acl(acl => 'apexmail.xml');
--END;
--/
-- Please, do commit work.
COMMIT;
You can find more information about the usage of
dbms_network_acl_admin in the Database Security Guide. For all the remaining network services you can apply the same technique.Where is the Scott schema in APEX?
I decided to install Oracle 11g. And with Oracle 11g Application Express (APEX)is installed by default. Before importing an application based on the emp table, I decided to create a workspace based on the existing schema Scott. However the administrative interface did not allow me to select Scott. By not using the LOV but typing the schema name, APEX gave the following error: Schema is reserved or restricted. What is going on?
APEX uses the following query to populate the list of values:
SELECT name n,
name r
FROM sys.user$ u
WHERE type# = 1
AND name <> 'FLOWS_030000'
AND name NOT IN('HTMLDB_PUBLIC_USER', 'PUBLIC_USER', 'FLOWS_FILES',
'SCHEDULER', 'PEOPLE', 'APEX_PUBLIC_USER')
AND name NOT IN
(SELECT SCHEMA
FROM wwv_flow_restricted_schemas
WHERE NOT EXISTS
(SELECT 1
FROM wwv_flow_rschema_exceptions
WHERE schema_id =
(SELECT id
FROM wwv_flow_restricted_schemas
WHERE SCHEMA = u.name)
)
)
ORDER BY 1
/
Beside some hard coded schema names, there are two flows_030000 tables used in the query, wwv_flow_restricted_schemas and wwv_flows_rschema_exception.
SQL>SELECT * FROM flows_030000.wwv_flow_restricted_schemas t
/
ID SCHEMA CREATED_BY CREATED_ON LAST_UPDATED_BY LAST_UPDATED_ON
-- ------ ---------- ---------------- --------------- ---------------
...
38 SCOTT SYS 3-8-2007 2:12:53
...
The wwv_flows_rschema_exception contains no rows.
Both tables are used by the package htmldb_site_admin_privs, which serves as an interface packages. Now remove the restriction on Scott by executing the following statements (as Sys).
SQL>exec flows_030000.htmldb_site_admin_privs.unrestrict_schema(p_schema => 'SCOTT');And of course, this topic is covered by the Application Express User's Guide.
SQL>commit;