Development
Load balancing with AS 10.1.3 and with Web Cache
I first used Oracle Web Cache which is a 10.1.2 Application Server product. This was pretty easy but very stupid in 10.1.3. ( the second part of this blog I will show you how easy this is in 10.1.3
The first step in Web Cache is to configure the listening ports for the load balancer.
In my case Web cache will listen on port 80 and 7778.
Now we can add the application servers ( original servers)
Define a website url for which you want to load balance, I will use the Enterprise Manager Website /em
The site is /em/
Add a site to server mapping where we connect the site em to the availible AS server
Now we have to make sure that a new web session is always run on the same original server else we cannot log in.
Use for the em website as session JSESSIONID and OC4J-based as binding mechanism. We are now ready with Web Cache. The only thing still to do is to add a virtual hsot to the httpd.conf of the Application Servers. We have to do this else the em website is redirected to one of the application server instead of the load balancer adress.
XPCND7010XMP.work.local:7777 is the application server on port 7777.
NameVirtualHost XPCND7010XMP.work.local:7777
<VirtualHost XPCND7010XMP.work.local:7777>
ServerName XPCND7010XMP.work.local
Port 80
</VirtualHost>
inside VirtualHost we use the adress of the load balancer.
That's all in webcache.
You don't have to do this in 10.1.3. Just install on the load balancer a http server ( custom install in the application server installer ).
Add in every AS Home the following topology lines to the opmn.xml and you have a cluster.
On the application servers you don't need the http servers anymore so you can disable.
<notification-server interface="ipv4">
<port local="6100" remote="6200" request="5003"/>
<ssl enabled="true" wallet-file="$ORACLE_HOME/opmn/conf/ssl.wlt/default"/>
<topology>
<discover list="*233.0.0.2:1500"/>
</topology>
</notification-server>
In this cluster you can have only one Enterprise Manager application so you have to disable it on all the application servers except one. ( server.xml and default-web-site.xml )
That's all in 10.1.3
How to post your code in the Oracle Forum
http://forums.oracle.com
use the following tags and put your code between them
[code]
[/code]
This will show your code formated and readable like this
SELECT CASE
WHEN cnt > 20
THEN '<font color="red">' || cnt || '</font>'
WHEN cnt <= 20
THEN '<font color="green">' || cnt || '</font>'
END AS x
FROM "MYTABLE"
WHERE TO_CHAR ("MYTABLE"."DATE", 'YYYY-MM-DD') >= '2007-04-01'
AND "MYTABLE"."SERVCODE" <> 'OBS'
instead of something like this
SELECT CASE WHEN COUNT > 20 THEN '' || cnt || '' WHEN COUNT <= 20 THEN '' || cnt || '' END AS X from "MYTABLE" where TO_CHAR ("MYTABLE"."DATE",'YYYY-MM-DD') >='2007-04-01' and "MYTABLE"."SERVCODE" <>'OBS'
ApEx - Shopping Cart
You will find the example here:
http://apex.oracle.com/pls/otn/f?p=31517:159
Enjoy digging into it.
I walked 1.27 Miles to My Gate at Dulles
Over 500 named users of my Workspace
Within these five to six moths I answered arround 1200 emails regarding account and also many other questions related to ApEx. The number of emails I am getting per day is quite constant and I still haven't noticed a trend of those emails decreasing.
OOW08 Mix presentations and ADF & SOA
The ADF developer community is not so large, it has to be a lot bigger to survive. To make this happen, Oracle has to promote and invest into JHeadstart ( it has to be more like APEX ) so the customer will make the step to go from APEX or Forms to Jheadstart. The second step Oracle has to do is to make sure that the BEA java developers will use ADF. So the ADF community will be large and important else ADF will only be used in the Oracle Apps.
By the way here are the SOA or Portal presentations which are selected by the Oracle mix people for OOW08.
BEA Aqualogic versus Oracle Fusion Middleware shoot out by Lonneke Dikmans
Oracle Portal, WebCenter and Stellent – which one should you use ? by by Eric Marcoux
How to Effectively use Web 2.0 Technologies within a Portal by Howard Block.
Managing and Changing the HTTP ports for ORACLE
This also helps on how to change the ports to 80 on UNIX as it requires root permission on UNIX systems for anything running on port below < 1024.
9.0.4
http://download.oracle.com/docs/cd/B10464_01/core.904/b10376/ports.htm
Changing the Oracle HTTP Server Non-SSL Listen Port (with Web Cache)
Changing the Oracle HTTP Server SSL Listen Port (with Web Cache)
Changing the Oracle HTTP Server Non-SSL Listen Port (No Web Cache)
Changing the Oracle HTTP Server SSL Listen Port (No Web Cache)
10.1.2
http://download.oracle.com/docs/cd/B14099_19/core.1012/b13995/ports.htm
My Oracle Open World Schedule
For anyone attending Oracle Open World this year, here is my schedule of presentations:
Session ID: S300184 Session Title: Weird PL/SQL Track: Oracle Develop: Database Room: Golden Gate C3 Date: 2008-09-21 Start Time: 15:45
Session ID: S300183 Session Title: Break Your Addiction to SQL! Track: Oracle Develop: Database Room: Salon 02 Date: 2008-09-22 Start Time: 13:00
Session ID: S300185 Session Title: Why You Should Care About Oracle 11g PL/SQL Now Track: Oracle Develop: Database Room: Salon 02 Date: 2008-09-23 Start Time: 11:30
Hope to see you there!
JAXB 1.0 vs. 2.0 in JDeveloper 10g
- if you already know JDeveloper's built-in JAXB compiler you can jump here
- Click on pictures to enlarge them
- Download this complete test case here
First of all, let's create a new application in JDeveloper with one empty project called "JAXB 1.0" adding only "XML" to its technology scope:
We must have a valid DTD or XML Schema so the JAXB compiler can work. Let's consider this small and simple XML Schema borrowed from a W3C tutorial:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<!-- definition of simple elements -->
<xs:element name="orderperson" type="xs:string"/>
<xs:element name="name" type="xs:string"/>
<xs:element name="address" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="country" type="xs:string"/>
<xs:element name="title" type="xs:string"/>
<xs:element name="note" type="xs:string"/>
<xs:element name="quantity" type="xs:positiveInteger"/>
<xs:element name="price" type="xs:decimal"/>
<!-- definition of attributes -->
<xs:attribute name="orderid" type="xs:string"/>
<!-- definition of complex elements -->
<xs:element name="shipto">
<xs:complexType>
<xs:sequence>
<xs:element ref="name"/>
<xs:element ref="address"/>
<xs:element ref="city"/>
<xs:element ref="country"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="item">
<xs:complexType>
<xs:sequence>
<xs:element ref="title"/>
<xs:element ref="note" minOccurs="0"/>
<xs:element ref="quantity"/>
<xs:element ref="price"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="shiporder">
<xs:complexType>
<xs:sequence>
<xs:element ref="orderperson"/>
<xs:element ref="shipto"/>
<xs:element ref="item" maxOccurs="unbounded"/>
</xs:sequence>
<xs:attribute ref="orderid" use="required"/>
</xs:complexType>
</xs:element>
</xs:schema>Just copy the code above to a file named "shiporder.xsd" in the project's root directory so it will automatically show in project's "Resources" folder:
Now select file "shiporder.xsd" in the application navigator, click the menu item "Tools -> JAXB Compilation..." and fill up the pop-up shown as the screenshot bellow:
After pressing the "OK" button, you should see the following scenario:
As you can see, the JAXB compiler generates a bunch of classes and interfaces reflecting the structure defined by the compiled XML Schema. If you look closer, you'll notice that some of the classes extend "oracle.xml.jaxb.JaxbNode" (like class ShiporderTypeImpl, for instance). That's because the built-in JAXB compilation feature we just showed relies on a JAXB 1.0 proprietary implementation provided by Oracle in its XML Developer Kit which comes with JDeveloper. You may get more details on this at Oracle XDK Home site.
Continuing with our test case, let's create a sample "testorder.xml" based on our shiporder.xsd schema:
<?xml version="1.0" encoding="ISO-8859-1" ?>
<shiporder orderid="0001" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="shiporder.xsd">
<orderperson>Eduardo Rodrigues</orderperson>
<shipto>
<name>John Doe</name>
<address>#100, Oracle Parkway</address>
<city>Redwood City</city>
<country>USA</country>
</shipto>
<item>
<title>DVD+RW</title>
<quantity>50</quantity>
<price>.50</price>
</item>
<item>
<title>17" LCD Monitor</title>
<quantity>5</quantity>
<price>150.00</price>
</item>
<item>
<title>Bluetooth Mouse</title>
<quantity>10</quantity>
<price>40.00</price>
</item>
</shiporder>To read in and process this sample XML using the package generated by Oracle's JAXB compiler, we must use the JAXB feature called "Unmarshal". To do that, I'll modify class test.jaxb1.TestJAXB1 like this:
package test.jaxb1;
import java.io.File;
import java.util.List;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.JAXBException;
import javax.xml.bind.Marshaller;
import javax.xml.bind.Unmarshaller;
import test.jaxb1.shiporder.*;
public class TestJAXB1 {
static JAXBContext context = null;
public static void main(String[] args) {
try {
context = JAXBContext.newInstance("test.jaxb1.shiporder");
Unmarshaller unmarshaller = context.createUnmarshaller();
Shiporder order = (Shiporder)unmarshaller.unmarshal(new File("testorder.xml"));
System.out.println("Items included in order #"+order.getOrderid()+" are:");
for (ItemTypeImpl item : (List<ItemTypeImpl>)order.getItem()) {
System.out.println("\t:. "+item.getTitle()+" - "+
item.getQuantity()+" item(s) at $"+item.getPrice()+" each");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}Making a long story short, the "magic" performed by the Unmarshaller is basically to parse the XML using the classes generated by the JAXB compiler to map its entire content into memory. It's just like using a DOM parser to load the XML's DOM tree into memory. The advantage here is that the names, attributes and methods in JAXB generated objects strictly reflect the XML's structure defined by its schema making it much easier and more intuitive to work with them instead of a pure DOM tree. Running the test above, the outcome should be:
Quite easy. You'd probably think: Well... I'm ok with that. It's easy enough already. The question is: is that so??? NOT!!!
Now I will repeat the same test case described above only upgrading from JAXB 1.0 to JAXB 2.0. First, we'll need to get a working implementation of JAXB 2.0. I recommend Sun's reference implementation which can be downloaded here (at the time I was writing this post, version 2.1.6 was the latest one). Just follow the instructions in that page to install it correctly and notice the bin subdirectory containing some scripts, including "xjc.bat" (for Windows; "xjc.sh" for Unix). This is the script supplied to execute JAXB 2.0 compilation. The trick here is to embed it in JDeveloper as an "External Tool". I'll show you how to do that step-by-step:
- Click the menu item "Tools -> External Tools..."
- Press the "New..." button to start the "Create External Tool" wizard
- Select "External Program" as the "Tool Type" and press "Next"
- Fill the 3 displayed fields as follows:
- Program Executable:
full path to the script "xjc.bat" which sould be <Sun_JAXB_2.1.6_install_dir>/bin/xjc.bat (or "xjc.sh" on Unix) - Arguments:
-d "${project.first.sourcepath}" -p ${promptl:label=Target Package Name} "${file.path}".
Press button "Insert..." to see all macros available (i.e.: ${file.path}).
The arguments above will be passed to the script "xjc.bat". Execute it with no arguments in a command prompt to see a help on all possible arguments. The default is to compile a XML Schema but you can use -dtd to compile a DTD instead, although it's an experimental feature. - Run Directory:
<Sun_JAXB_2.1.6_install_dir>/bin - Press "Next" to advance to "Display" step and fill the 3 fields as you like. I suggest the following:
- Caption for Menu Items:
Compile XML Schema with JAXB 2.0 - ToolTip Text:
Compile a XML Schema with JAXB 2.0 Reference Implementation by Sun Microsystems - ToolTip Text:
ide.fileicon:<SUN_JAXB_2.1.6_install_dir>/bin/xjc.bat - Press "Next" to advance to "Integration" step where I recommend selecting "Tools Menu" and "Navigator Context Menu"
- Press "Next" to advance to the final step "Availability". Here, I recommend choosing the option "When Specific File Types are Selected" and then move only the item "XML Schema Type" from list "Available Types" to list "Selected Types"
- Press "Finish" and done!
First, create a new empty project called "JAXB 2.0" also adding only "XML" to its technology scope.
We'll need to create a JAXB 2.0 library and add it to the project:
The important here is to add the following archives, located in directory <Sun_JAXB_2.0_install_dir>/lib, to the library's class path: jaxb-api.jar, jaxb-impl.jar and jsr173_1.0_api.jar.
Now just copy files "shiporder.xsd" and "testorder.xml" from project "JAXB 1.0" to this new project's root directory. Also be sure that the sources directory configured for the project exists and, if it doesn't, create it manually. That's needed because Sun's JAXB 2.1.6 compiler won't create its output directory automatically and the process will fail if that directory doesn't exist. Finally, select file "shiporder.xsd" in the Applications Navigator and execute our newly created menu item "Tools -> Compile XML Schema with JAXB 2.0"
Define "test.jaxb2.shiporder" as the "Target Package Name" when prompted. After execution, you should see the following output:
Just click the "Refresh" button on the Applications Navigator and the new package "test.jaxb2.shiporder" will appear. At this point we can already make a simple comparison:
As you can see, instead of generating 16 interfaces, 15 classes and 1 property file, JAXB 2.0 generated only 4 classes! And like it was not enough, if you look inside those 4 classes you'll notice that they are all very simple POJOs!!!
Take class "Shiporder.java" for example:
//
// This file was generated by the JavaTM Architecture for XML Binding(JAXB) Reference Implementation, v2.1.5-b01-fcs
// See <a href="http://java.sun.com/xml/jaxb">http://java.sun.com/xml/jaxb</a>
// Any modifications to this file will be lost upon recompilation of the source schema.
// Generated on: 2008.01.09 at 12:41:26 PM BRST
//
package test.jaxb2.shiporder;
import java.util.ArrayList;
import java.util.List;
import javax.xml.bind.annotation.XmlAccessType;
import javax.xml.bind.annotation.XmlAccessorType;
import javax.xml.bind.annotation.XmlAttribute;
import javax.xml.bind.annotation.XmlElement;
import javax.xml.bind.annotation.XmlRootElement;
import javax.xml.bind.annotation.XmlType;
/**
* <p>Java class for anonymous complex type.
*
* <p>The following schema fragment specifies the expected content contained within this class.
*
* <pre>
* <complexType>
* <complexContent>
* <restriction base="{http://www.w3.org/2001/XMLSchema}anyType">
* <sequence>
* <element ref="{}orderperson"/>
* <element ref="{}shipto"/>
* <element ref="{}item" maxOccurs="unbounded"/>
* </sequence>
* <attribute ref="{}orderid use="required""/>
* </restriction>
* </complexContent>
* </complexType>
* </pre>
*
*
*/
@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "", propOrder = {
"orderperson",
"shipto",
"item"
})
@XmlRootElement(name = "shiporder")
public class Shiporder {
@XmlElement(required = true)
protected String orderperson;
@XmlElement(required = true)
protected Shipto shipto;
@XmlElement(required = true)
protected List<Item> item;
@XmlAttribute(required = true)
protected String orderid;
/**
* Gets the value of the orderperson property.
*
* @return
* possible object is
* {@link String }
*
*/
public String getOrderperson() {
return orderperson;
}
/**
* Sets the value of the orderperson property.
*
* @param value
* allowed object is
* {@link String }
*
*/
public void setOrderperson(String value) {
this.orderperson = value;
}
/**
* Gets the value of the shipto property.
*
* @return
* possible object is
* {@link Shipto }
*
*/
public Shipto getShipto() {
return shipto;
}
/**
* Sets the value of the shipto property.
*
* @param value
* allowed object is
* {@link Shipto }
*
*/
public void setShipto(Shipto value) {
this.shipto = value;
}
/**
* Gets the value of the item property.
*
* <p>
* This accessor method returns a reference to the live list,
* not a snapshot. Therefore any modification you make to the
* returned list will be present inside the JAXB object.
* This is why there is not a <CODE>set</CODE> method for the item property.
*
* <p>
* For example, to add a new item, do as follows:
* <pre>
* getItem().add(newItem);
* </pre>
*
*
* <p>
* Objects of the following type(s) are allowed in the list
* {@link Item }
*
*
*/
public List<Item> getItem() {
if (item == null) {
item = new ArrayList<Item>();
}
return this.item;
}
/**
* Gets the value of the orderid property.
*
* @return
* possible object is
* {@link String }
*
*/
public String getOrderid() {
return orderid;
}
/**
* Sets the value of the orderid property.
*
* @param value
* allowed object is
* {@link String }
*
*/
public void setOrderid(String value) {
this.orderid = value;
}
}"public class Shiporder {". No interfaces being implemented, no classes being extended at all. Only a simple JavaBean with its attributes and their respective accessors. The "magic" here is done thanks to the annotations. They perform the actual mapping between the XML's structure defined by the compiled XML Schema and the classes derived from that compilation process.
I think it's pretty obvious to see the huge advantages of JAXB 2.0 when compared to version 1.0. The compilation of a XML Schema produces much fewer classes and those classes are simply annotated POJOs. This certainly implies a much faster, lighter and easier to understand XML binding.
Besides, there are some other details that makes JAXB 2.0 even more beneficial. Javadocs are automatically generated, it relies on cutting-edge XML parsing technology called "Streaming API for XML" which basically gives much more precise control over XML document processing. For further info on this, read "Oracle StAX Pull Parser Preview" and "JSR#173 homepage". Finally, as opposed to the known limitations of JDeveloper's built-in JAXB 1.0 compiler, Sun's JAXB 2.1.6 supports:
- Javadoc generation
- The key and keyref features of XML Schema
- The List and Union features of XML Schema
- SimpleType mapping to TypeSafe Enum class and IsSet property modifier
- XML Schema component "any" and substitution groups
- Customization of XML Schema to override the default binding of XML Schema components
- On-demand validation of content based on a given Schema
To run the same unmarshalling test, just create class "test.jaxb2.TestJAXB2" as follows:
package test.jaxb2;
import java.io.File;
import javax.xml.bind.JAXBContext;
import javax.xml.bind.Unmarshaller;
import test.jaxb2.shiporder.Item;
import test.jaxb2.shiporder.Shiporder;
public class TestJAXB2 {
public static void main(String[] args) {
try {
JAXBContext context = JAXBContext.newInstance("test.jaxb2.shiporder");
Unmarshaller unmarshaller = context.createUnmarshaller();
Shiporder order = (Shiporder)unmarshaller.unmarshal(new File("testorder.xml"));
System.out.println("Items included in order #"+order.getOrderid()+" are:");
for (Item item : order.getItem()) {
System.out.println("\t:. "+item.getTitle()+" - "+
item.getQuantity()+" item(s) at $"+item.getPrice()+" each");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}This is slightly different from class "test.jaxb1.TestJAXB1" but, after running it, you should see exactly the same outcome:
Well, that's it. I think I've made my point. So good luck in your JAXB 2.0 adventures!
The future of Oracle APEX - aka Oracle APEX 4.0
Get the details about how to join the WebCast at the German Oracle APEX Community web site.
Note: This WebCast will be in German.
History Tables
A history table allows you to use one table to track changes in another table. While the basic idea is simple, a naive implementation will lead to bloat and will be difficult to query. A more sophisticated approach allows easier queries and can produce not just information about single rows, but can also support aggregrate company-wide queries.
This week in the Database Programmer Blog we return to table design patterns with an essay on history tables. The basic premise of this blog is that good coding skills do not lead magically to good database skills -- you can only make optimal use of a database by understanding it on its own terms. There is a new essay each Monday, and there is a Complete Table of Contents and a List of Table Design Patterns. What to Put Into A History Table
Naive approaches to history tables usually involve making a complete copy of the original (or new) row when something changes in the source table. This turns out to be of little use, for reasons I will explain below. A much more useful approach is to track only a few columns and to store any combination of old values, new values, and differences. A history table designed this way can be tremendously useful.
We will start with the example of a sales order table, called ORDERS. The columns we are interested in might look like this:
ORDER | CUSTOMER | DATE | LINES | TAX | TOTAL | PAID | BALANCE ------+----------+----------+--------+-------+--------+--------+--------- 1234 | 9876 | 5/1/08 | 48.00 | 5.00 | 53.00 | 0 | 53.00 2345 | 9876 | 5/3/08 | 150.00 | 0 | 150.00 | 150.00 | 0 3456 | 5544 | 6/8/08 | 25.00 | 2.60 | 27.60 | 15.00 | 12.60 4567 | 3377 | 7/3/08 | 125.00 | 7.00 | 132.00 | 50.00 | 82.00
We first have to ask which columns must be copied into history so that we can link the history table back to the ORDERS table. The only column we need for tracking is ORDER (the order number), so the history table will always have an ORDER column.
We should also assume that the history table will contain at least a timestamp and a column to track the user who made the change, which brings us to a minimum of three columns.
Finally, it tends to be very useful to track what action caused the history entry, be it an INSERT, UPDATE, or DELETE. This brings us up to four minimum columns.
Next we ask which columns we will definitely not need. There are two groups of columns we will not need, which are 1) the columns that never change and 2) the columns we do not care about. Columns that do not change are likely to be the CUSTOMER and the DATE column. There is no need to bloat the history table with these valus because we can just get them out of the ORDERS table. The second group, columns we do not care about, are are usually things like ship-to address, maybe an email, and other information. Naturally there is no hard-and-fast rule here, it depends entirely upon the needs of the application.
So now we know what we definitely need and what we definitely do not need, and we are ready to begin work considering the columns that will change. Not surprisingly, these are usually all about the numbers. Next we will see how to track the numbers.
Tracking Changes to NumbersWhile it is certainly useful to store one or both of the old and new values for a number, it far more useful to store the change in the value, or the delta. Having this number in the history table makes for some really nifty abilities. If you store all three of the old, new, and delta, then you can more or less find out anything about the ORDER's history with very simple queries.
So we are now ready to consider what the history table might look like. We will take the case of an order that was entered by user 'sax', updated twice by two other users, and in the end it was deleted by user 'anne'. Our first stab at the history table might look like this:
ORDER | USER_ID | ACTION | DATE | LINES_OLD | LINES_NEW | LINES_DELTA ------+----------+--------+---------+-----------+-----------+------------- 1234 | sax | UPD | 5/1/08 | 0.00 | 48.00 | 48.00 1234 | arkady | UPD | 5/7/08 | 48.00 | 58.00 | 10.00 1234 | ralph | UPD | 6/1/08 | 58.00 | 25.00 | -33.00 1234 | anne | DEL | 6/4/08 | 25.00 | 0.00 | -25.00
I should note that if you keep LINES_OLD and LINES_NEW, then strictly speaking you do not need the LINES_DELTA columns. Whether or not you put it in depends on your approach to table design. If you framework allows you to guarantee that it will be correct, then your queries will be that much simpler with the LINES_DELTA column present.
You may wonder why there is no entry for the original INSERT. This is because you must enter an order before you can enter the lines, so the original value will always be zero. Only when lines start going in does the ORDER get any numbers. This is true for header tables, but it would not be true for detail tables like ORDER_LINES_HISTORY.
Some of the Obvious QueriesThere are few obvious queries that we can pull from the history table right away. These include the following:
-- Find the value of of the line items of an -- order as of June 1st SELECT LINES_NEW FROM ORDERS_HISTORY WHERE ORDER = 1234 AND DATE <= '2008-06-01' ORDER BY DATE DESC LIMIT 1; -- Find the original value of the line items, -- and the user who entered it. SELECT LINES_NEW, USER_ID FROM ORDERS_HISTORY WHERE ORDER = 1234 ORDER BY date LIMIT 1; -- Find the users who have worked on an order SELECT DISTINCT USER_ID FROM ORDERS_HISTORY WHERE ORDER = 1234;
Most of queries should be pretty obvious, and there are plenty more that will suggest themselves once you start working with the history tables.
Queries Involving the DeltaThe real power of the DELTA column comes into play when you are trying to compute back-dated values such as the company's total open balance on June 1, 2008. If you have a naive history table that stores only the old value or only the new value, this is truly a tortuous query to write, but if you have both then it is really quite easy.
-- Query to calculate the total open balance of all -- orders as of a given date SELECT SUM(BALANCE_DELTA) FROM ORDERS_HISTORY WHERE DATE <= '2008-06-01';
This magical little query works because paid orders will "wash out" of the total. Consider an order that is entered on May 20 for $200.00, and is then paid on May 23rd. It will have +200 entry in the BALANCE_DELTA column, and then it will have a -200.00 entry 3 days later. It will contribute the grand sum of zero to the total.
But an order entered on May 25th that has not been paid by June 1st will have only a +200 entry in the BALANCE_DELTA column, so it will contribute the correct amount of $200.00 to the balance as of June 1st.
If the company owner wants a report of his total open balances on each of the past 30 days, you can retrieve two queries and build his report on the client:
-- Get begin balance at the beginning of the period SELECT SUM(BALANCE_DELTA) as BEGIN_BALANCE FROM ORDERS_HISTORY WHERE DATE < '2008-06-01'; -- Get the total changes for each day. When you -- build the report on the client, add each day's -- change amount to the prior day's balance SELECT SUM(BALANCE_DELTA) AS BALANCE_DELTA FROM ORDERS_HISTORY WHERE DATE BETWEEN '2008-06-01' AND '2008-06-30' GROUP BY DATE;Keeping History Tables Clean
A clean history table is one that contains no unnecessary information. You normally do not want entries going into the history table if nothing relevant changed. So your history table mechanism should examine the columns it is tracking, and only make an entry to the history table if one of the columns of interest actually changed.
Problems With The Naive History TableA very basic history table will usually copy the entire original row from the source table into the history table whenever an INSERT, UPDATE or DELETE occurs. One simple problem is that you end up with bloated history tables. Because they are cluttered with unnecessary repititions, they are difficult to work with by inspection.
A much more serious technical problem with the naive approach is that it is horribly difficult to produce the queries demonstrated above. You must reproduce the concept of a delta by either running through all of the rows on the client, or you must make a difficult (and often impossible) JOIN of the history table to itself in which you connect each row to the row that came just before it. All I can say is, no thanks, I'll go with the delta.
History Table SecurityHistory tables always involve some concept of auditing, that is, keeping track of user actions. This means we need to protect against deliberate falsification of the history tables, which leads to two rules. First, a user must have no ability to directly DELETE rows from the history table, or they could erase the record of changes. Second, the user must have no ability to directly INSERT or UPDATE existing rows, because if they could they can falsify the history. These rules apply to both regular users and system administrators, the administrator must have no privelege to subvert or manipulate the history.
Since history tables have a tendency to become seriously bloated, there must be some priveleged group that can DELETE from the history tables, which they would do as a periodic purge operation. This group should have no ability to UPDATE the tables, because such priveleges would open a potential hole for subverting the history. Regular system administrators should not be in this group, this should be a special group whose only purpose is to DELETE out of the history tables.
If you are making use of DELTA columns, then stricly speaking you do not want to purge, but compress history tables. If you want to purge out all entries in 2005, you must replace them with a single entry that contains a SUM of the DELTA columns for all of 2005.
So to sum up, we have the following security rules for a history table:
- No system user should be able to DELETE from the history table.
- No system user should be able to UPDATE the history table.
- No system user should be able to directly control the INSERT into the history table.
- A special group must be defined whose only ability is to DELETE from the history table, so that the tables can be purged (or compressed) from time to time.
As always, you have your choice of implementing the history mechanism in the client code or in the database itself.
The best performing and most secure method is to implement history tables with triggers on the source table. This is the best way to implement both security and the actual business rules in one encapsulated object (the table). However, if you have no current practices for coding server-side routines, or you do not have a data dictionary system that will generate the code for you, then it may not be practical to go server-side for a single feature.
Implementing history tables in code has the usual benefit of keeping you in the language and habits you are most familiar with, but it means that you cannot allow access to your database except through your application. I cannot of course make a general rule here, this decision is best made by the design team based on the situation at hand and anticipated future needs.
ConclusionHistory tables have many uses. Beyond the obvious first use of finding indidivual values at some point in the past, well crafted tables can produce company-wide aggregations like total open balances on a given day, changes in booked orders on a day or in a range of days, and many other queries along those lines. Security is very important to prevent history tables from being subverted.
CRUD operations in Flex with ADF BC
The great thing is that LifeCycle keeps all the clients in sync which are using the same data service, even in different flex projects ( Data Push).
First you have to setup a jdeveloper LifeCycle project, see my previous post
For my example I am using the employee table in the HR schema. Make sure you the java types instead the Oracle java types else you have to do some extra casting
To get this working in LifeCycle we have to create three classes and do some configuration work.
Create an employee object class, I already tried the use the RowImpl of the employee viewobject but this does not work in all cases. When I update a record in Flex the updated RowImpl is send back to the lifecycle server where I got a dead viewobject row error. So we have to do it manually. To make life a little bit simplier I use two methods which can transform this employee object to EmployeeRowImpl and back.
package nl.ordina.flex.adfbc;
import java.math.BigDecimal;
import java.sql.Date;
import nl.ordina.flex.model.dataaccess.EmployeesViewRowImpl;
public class Employee {
private BigDecimal employeeId;
private String firstName;
private String lastName;
private String email;
private String phoneNumber;
private Date hireDate;
private String jobId;
private BigDecimal salary;
private BigDecimal commissionPct;
private BigDecimal managerId;
private BigDecimal departmentId;
public Employee() {
}
public Object getAttribute(String attribute) {
if ( attribute.equalsIgnoreCase("firstname")) {
return firstName;
}
if ( attribute.equalsIgnoreCase("lastname")) {
return lastName;
}
return null;
}
public Employee transform( EmployeesViewRowImpl row) {
Employee emp = new Employee();
emp.setEmployeeId(row.getEmployeeId());
emp.setFirstName(row.getFirstName());
emp.setLastName(row.getLastName());
emp.setCommissionPct(row.getCommissionPct());
emp.setDepartmentId(row.getDepartmentId());
emp.setEmail(row.getEmail());
emp.setHireDate(row.getHireDate());
emp.setJobId(row.getJobId());
emp.setManagerId(row.getManagerId());
emp.setPhoneNumber(row.getPhoneNumber());
emp.setSalary(row.getSalary());
return emp;
}
public EmployeesViewRowImpl transform( Employee employee) {
EmployeesViewRowImpl row = new EmployeesViewRowImpl();
row.setEmployeeId(employee.getEmployeeId());
row.setFirstName(employee.getFirstName());
row.setLastName(employee.getLastName());
row.setCommissionPct(employee.getCommissionPct());
row.setDepartmentId(employee.getDepartmentId());
row.setEmail(employee.getEmail());
row.setHireDate(employee.getHireDate());
row.setJobId(employee.getJobId());
row.setManagerId(employee.getManagerId());
row.setPhoneNumber(employee.getPhoneNumber());
row.setSalary(employee.getSalary());
return row;
}
public void setEmployeeId(BigDecimal employeeId) {
this.employeeId = employeeId;
}
public BigDecimal getEmployeeId() {
return employeeId;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getFirstName() {
return firstName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getLastName() {
return lastName;
}
public void setEmail(String email) {
this.email = email;
}
public String getEmail() {
return email;
}
public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}
public String getPhoneNumber() {
return phoneNumber;
}
public void setHireDate(Date hireDate) {
this.hireDate = hireDate;
}
public Date getHireDate() {
return hireDate;
}
public void setJobId(String jobId) {
this.jobId = jobId;
}
public String getJobId() {
return jobId;
}
public void setSalary(BigDecimal salary) {
this.salary = salary;
}
public BigDecimal getSalary() {
return salary;
}
public void setCommissionPct(BigDecimal commissionPct) {
this.commissionPct = commissionPct;
}
public BigDecimal getCommissionPct() {
return commissionPct;
}
public void setManagerId(BigDecimal managerId) {
this.managerId = managerId;
}
public BigDecimal getManagerId() {
return managerId;
}
public void setDepartmentId(BigDecimal departmentId) {
this.departmentId = departmentId;
}
public BigDecimal getDepartmentId() {
return departmentId;
}
}
Create an EmployeeService which does the CRUD operations in ADF BC.
package nl.ordina.flex.adfbc;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import nl.ordina.flex.model.dataaccess.EmployeesViewImpl;
import nl.ordina.flex.model.dataaccess.EmployeesViewRowImpl;
import nl.ordina.flex.model.service.EmployeeModuleImpl;
import oracle.jbo.client.Configuration;
public class EmployeeService {
private EmployeeModuleImpl am;
public EmployeeService() {
am = (EmployeeModuleImpl)Configuration.createRootApplicationModule("nl.ordina.flex.model.service.EmpoyeeModule", "EmployeeModuleLocal");
}
public List getEmployees() {
List list = new ArrayList();
Employee emp = new Employee();
EmployeesViewImpl empView = am.getEmployeesView1();
empView.executeQuery();
while (empView.hasNext()) {
EmployeesViewRowImpl row = (EmployeesViewRowImpl) empView.next();
list.add(emp.transform(row));
}
return list;
}
public Employee getEmployee(BigDecimal employeeId) {
EmployeesViewImpl view = am.getEmployeesView1();
Employee emp = new Employee();
view.setid(employeeId);
view.executeQuery();
if ( view.hasNext()) {
EmployeesViewRowImpl row = (EmployeesViewRowImpl)view.next();
return emp.transform(row);
} else
return null;
}
public Employee create(Employee employee) {
EmployeesViewImpl view = am.getEmployeesView1();
Employee emp = new Employee();
view.insertRow(emp.transform(employee));
am.getDBTransaction().commit();
return employee;
}
public static String capitalize(String s) {
if (s.length() == 0) return s;
return s.substring(0, 1).toUpperCase() + s.substring(1);
}
public boolean update(Employee employee, List changes) {
EmployeesViewImpl view = am.getEmployeesView1();
view.setid(employee.getEmployeeId());
view.executeQuery();
if ( view.hasNext()) {
EmployeesViewRowImpl row = (EmployeesViewRowImpl)view.next();
for (int i = 0 ; i < changes.size(); i++ ) {
String attribute = capitalize(changes.get(i).toString());
row.setAttribute(attribute,employee.getAttribute(attribute));
}
am.getDBTransaction().commit();
return true;
} else
return false;
}
public boolean delete(Employee employee) {
EmployeesViewImpl view = am.getEmployeesView1();
view.setid(employee.getEmployeeId());
view.executeQuery();
if ( view.hasNext()) {
EmployeesViewRowImpl row = (EmployeesViewRowImpl)view.next();
row.remove();
am.getDBTransaction().commit();
return true;
} else
return false;
}
}
We have to create Assembler class which is used by the LifeCycle server
package nl.ordina.flex.adfbc;
import java.util.List;
import java.util.Collection;
import java.util.Map;
import flex.data.DataSyncException;
import flex.data.assemblers.AbstractAssembler;
import java.math.BigDecimal;
public class EmployeeAssembler extends AbstractAssembler {
public Collection fill(List fillArgs) {
EmployeeService service = new EmployeeService();
return service.getEmployees();
}
public Object getItem(Map identity) {
EmployeeService service = new EmployeeService();
return service.getEmployee(((BigDecimal) identity.get("employeeId")));
}
public void createItem(Object item) {
EmployeeService service = new EmployeeService();
service.create((Employee) item);
}
public void updateItem(Object newVersion, Object prevVersion, List changes) {
EmployeeService service = new EmployeeService();
boolean success = service.update((Employee) newVersion, changes);
if (!success) {
BigDecimal employeeId = ((Employee) newVersion).getEmployeeId();
throw new DataSyncException(service.getEmployee(employeeId), changes);
}
}
public void deleteItem(Object item) {
EmployeeService service = new EmployeeService();
boolean success = service.delete((Employee) item);
if (!success) {
BigDecimal employeeId = ((Employee) item).getEmployeeId();
throw new DataSyncException(service.getEmployee(employeeId), null);
}
}
}
Now the last step in jdeveloper is to add a new data service entry to the lifecycle configuration ( data-management-config.xml located in the flex folder)
<?xml version="1.0" encoding="UTF-8"?>
<service id="data-service"
class="flex.data.DataService">
<adapters>
<adapter-definition id="actionscript" class="flex.data.adapters.ASObjectAdapter" default="true"/>
<adapter-definition id="java-dao" class="flex.data.adapters.JavaAdapter"/>
</adapters>
<default-channels>
<channel ref="my-rtmp"/>
</default-channels>
<destination id="employee">
<adapter ref="java-dao" />
<properties>
<cache-items>false</cache-items>
<use-transactions>false</use-transactions>
<source>nl.ordina.flex.adfbc.EmployeeAssembler</source>
<scope>application</scope>
<metadata>
<identity property="employeeId" type="java.math.BigDecimal" />
</metadata>
<network>
<session-timeout>20</session-timeout>
<paging enabled="false" pageSize="100" />
<throttle-inbound policy="ERROR" max-frequency="500"/>
<throttle-outbound policy="REPLACE" max-frequency="500"/>
</network>
</properties>
</destination>
</service>
Now we can create a Flex lifecycle project ( For more information on creating a flex j2ee project see one of my BlazeDS examples. But here we have to create the employee object too. Create an new Class called Employee
package
{
import flash.display.InteractiveObject;
[Managed]
[RemoteClass(alias="nl.ordina.flex.adfbc.Employee")]
public class Employee
{
public function Employee()
{
}
public var employeeId:int;
public var firstName:String;
public var lastName:String;
public var email:String;
public var phoneNumber:String;
public var hireDate:Date;
public var jobId:String;
public var salary:Number;
public var commissionPct:Number;
public var managerId:int;
public var departmentId:int;
}
}
Add the employee destination to mx:DataService. Import the employee object by adding xmlns="*" to the mx:Application element. Now you can use <Employee/> in Flex.
<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx="http://www.adobe.com/2006/mxml"
xmlns="*"
width="1155" height="276"
applicationComplete="ds.fill(employeeArray)" >
<mx:ArrayCollection id="employeeArray"/>
<mx:DataService id="ds" destination="employee"/>
<Employee/>
<mx:DataGrid dataProvider="{employeeArray}" editable="true"
width="100%" height="100%"/>
</mx:Application>
Start the flex project twice so you can see the data push in action.
Show Blob of other table in APEX Form
--
I read your answer in the forum on a problem regarding : display BLOB in Apex.
My question is if it is possible (and HOW) to display an image on a form page based on table A but the image is in table B (and table A contains the values to read the blob(s) from table B).
I guess I have to add an (file browse) item in the form but what's next ??
--
It's not that difficult to get around this problem with the standard functionality in APEX 3.0 and up, if we use the BLOB support that the APEX team developed for us.
Our test case:
* Database level
CREATE TABLE ex_A
(
id NUMBER(6) ,
name VARCHAR2(50),
picture_label VARCHAR2(30),
picture_id NUMBER ,
CONSTRAINT EX_A_PK PRIMARY KEY (ID)
);
CREATE TABLE ex_B
(
picture_id NUMBER ,
picture BLOB ,
picture_filename VARCHAR2(100),
picture_mimetype VARCHAR2(100),
CONSTRAINT EX_B_PK PRIMARY KEY (PICTURE_ID)
);
* APEX level
Create a new application with a Form based on table ex_A and a Form based on table ex_B by following the wizard.
We adapt Page 2 - Form based on table ex_B - and change the item P2_PICTURE to
(On Page 2 and Page 1 I also made the Hidden and Protected item - the pk - visible as I don't have a trigger to populate it, so I just give a unique id in myself.)
So now the answer to the question... How do you display the blob from ex_B table on the form that is based on table ex_A?
Add a new item: Display as Text (does not save state) and in the Source (PL/SQL Function Body) you specify:
As you can see we use the APEX_UTIL.GET_BLOB_FILE_SRC function. It will look at page 2 for the format of the blob and will pass the picture_id to it.
If you upload a file on Page 2 and after wards in Page 1 you reference the picture_id, it should get displayed.
You can try to upload a picture yourself here. Click first the Ex_B tab and upload a picture, next go to Ex_A tab and create a reference and add the same id as for the one you used for the picture.The export of the application can be found here (save as blob_support.sql).
Hope that answer the question.
Oracle OpenWorld 2008
It's only 2 month left to Oracle OpenWorld 2008. Few news about this conference and my first time ever participation. Last week I've registered for OOW2008 as Blogger. Yesterday I've received confirmation email - I was accepted. I'm really looking forward to be in San Francisco and meet with all Oracle geeks, enthusiast and other bloggers.
I still need to apply for US Visa, and organize my trip. And of course choose sessions in which I would like to participate.
There is also one good news for people in Eastern Europe, Post Soviet Republics, Middle East & Asia. People from those countries can get discount on registration fee.
Attendees will need to enter EMKT as their priority code during "Step 2" of the registration process.
The countries from the EE, CIS & MEA regions which qualify for the discount are:
African Operations (except South Africa), Albania, Armenia, Azerbaijan, Belarus, Bosnia & Herzegovina, Bulgaria, Croatia, Czech Republic, Cyprus, Estonia, FYR Macedonia, Georgia, Hungary, Iraq, Iran, Jordan, Kazakhstan, Kyrgyzstan, Latvia, Lebanon, Lithuania, Malta, Moldova, Montenegro, Oman, Palestine, Poland, Romania, Russia, Serbia, Slovakia, Slovenia, Syria, Tajikistan, Turkey, Turkmenistan, Ukraine, Uzbekistan, and Yemen.
Do not wait. Register for Oracle OpenWorld 2008 !Paweł
--
Related Articles on Paweł Barut blog:
- One Year Blogging
- I've been tagged 3 times; 8 things about me
- Oracle 11g new features
- Oracle 11g new features, part 2
- Oracle Database 11g Release 2 is coming
New Forms Modernization Case Study using Oracle Forms, Web Services, JDeveloper and ESB
I've just uploaded a new case study from Oracle partner iAdvise on their work with a Dutch insurance company. This is another great "real world" story of someone whose business needs are changing and are working on a path of modernization that maximizes their current investment while exploiting new opportunities and technologies.
In this case study, the are mixing a Forms and web application, which are sharing web services orchestrated through rules in an Enterprise Service Bus - ESB
A couple of quotes that stood out for me:
"Oracle technology provides a no-pain-all-gain innovation path to its customers, letting them profit from new technology while leveraging and extending the life of their existing IT investments"
"We have also seen how Oracle supports this new technological trend through its comprehensive SOA suite, and through ensuring that existing technologies such as Oracle Forms are kept up to speed with technological evolution by allowing them to integrate easily and seamlessly in a service oriented architecture"
Not surprisingly, this is right in line with our vision for Forms customers and we are hearing more and more of these case studies coming through each month. I see these customer stories as the validation of out modernization message and hope its a path you feel you can follow.
Easier Linking for OTN Forums
jQuery Datepicker and APEX
Dynamic Date Filtering in Interactive Reports
Whilst the filtering part of interactive reports currently supports a fair number of options, there isn't currently the facility to specify a dynamic date filter using SYSDATE. A common use case for this would be a dashboard report where you want to display all the overdue items in a dataset. In the interactive report filter definition, it would look something like this...
But unfortunately, we do not currently support the use of SYSDATE in the Expression field. So what can you do? I will discuss 3 possible workarounds for achieving this.
Note: Method 1 does not require any changes from a developer perspective, methods 2 and 3 require some developer work.
How...
Method 1
Use the 'Is in the last' operator, and then specify some arbitrarily large amount (100 years).
This could then be saved by the user as a 'Named Report' for future use. Although this is the quickest and easiest method, it isn't very intuitive for the user to come up with and also it's not 100% water tight to use this filter, as it might be possible that a date is entered outside of the arbitrary range specified.
Method 2
It's possible to specify filters on interactive reports via the URL. This uses the 'IR%' prefix in the 'Item Names' argument in the URL syntax, as detailed in the documentation. This method involves dynamically provisioning a link to the interactive report page and could be implemented by creating a SQL report to look like a menu, like this:
1) Add a region of type 'SQL Report' and for the 'Region Source', specify:
select sysdate from dual2) Ensure the following is set during the wizard creation:
- Report Template: template: [theme number].Borderless3) Go the 'Report Attributes' page and specify the following:
- Pagination Scheme: No pagination.3) Add a column link to the report by going the 'Report Attributes' page and clicking the 'Add Column Link' link from the 'Tasks' menu on the right. For this column link, specify the following values:
- Untick the 'Show' checkbox for the SYSDATE column.
- Click 'None' for 'Headings Type'.
- Link Text: 'Overdue Reviews'Note: My interactive report is on page 2 of my application, but change all occurences of 2 to to whatever your page is.
- Page: 2
- Clear Cache: '2,CIR'
- Item 1 Name: IRLT_REVIEW_DATE
- Item 1 Value: #SYSDATE#
This generates a link like this:
Of particular interest here are...
- Clear Cache = '2,CIR'
This clears the cache for page 2, and also uses the new 'CIR' parameter, which clears all settings that could already have been applied to the interactive report (including any default report settings). - Item Names = 'IRLT_REVIEW_DATE'
This can be broken down as follows:
- 'IR': This is used to set interactive report filters dynamically over the URL.
- 'LT': The characters directly after the 'IR' prefix are equivalent to the 'Operator' value in the interactive report 'Filter' dialog, so in this case 'LT' specifies a 'Less than' operator. Note: You could also use 'LTE' which would specify a 'Less than or equal to' operator. See here for more options.
- 'REVIEW_DATE': Then after the underscore, this is the column name on which to apply the filter, equivalent to the 'Column' value in the interactive report 'Filter' dialog. In this case we are filtering on the 'REVIEW_DATE' column.
This method works well, but does require some sort of separate menu or link to go to the interactive report, which steps out of the normal use-case for interactive reports.
Method 3
The final method requires the developer to code a derived column to calculate if the record is overdue in the report SQL. A snippet something like:
(caseOnce this has been done by the developer, the user could then choose to apply a filter on this column as shown in the screenshot:
when review_date < sysdate then 'Yes'
else
'No'
end) review_overdue
This could then be saved by the user as a 'Named Report' for future use.
Note: With methods 1 and 3, the developer could also default these reports for users, saving the report as 'Default Report Settings' in the 'Save Report' dialog. This means that when any user logs into and sees this report, they will default to these filters. One consideration here though, when the developer saves the 'Default Report Settings', you cannot specify a 'Name' for the report, so the user would see the filter as it is coded without a named tab.
Conclusion...
So method 3 would be my preferred choice, because it works and I believe provides the most user friendly solution for the users whilst keeping within the standard use-case for interactive reports. One consideration of this approach though would be that no index would be used on that column (see comments).
A couple of questions for the community...
- Would you find it useful to be able to filter by SYSDATE?
- Would you find it useful to be able to filter not just by a value, but by another column value?
- What else would you like to see incorporated into Interactive Reports to make them even better?
Further Reading...
General overview and information on interactive reports
Marcie Young's Advanced Interactive Report Tutorial
APEX documentation page on dynamic interactive report filtering via the URL
David Peake's (APEX PM) blog post on 'Advanced Interactive Reports
System 9 Gadgets
A while ago I created some System 9 gadgets for the Windows Vista sidebar. The project went well but when Hyperion was bought by Oracle it kind of got lost and forgotten. Well I found the source while going through some old files and I decided to dust them off. I removed the old branding and made sure they worked with the newest version of System 9 (11.1).
There are 2 gadgets, one that lets you browse the System 9 repository and another that stores favorite reports that you set in the first. The gadgets are great for the user that opens the same key reports on some sort of periodic basis.
I think these are pretty useful and some users may want them. Next steps are to figure out how to post them and to see if I can quickly convert them to Google gadgets. I will update the blog when I get them posted.