Development

Oracle Forms: What's next?

With the release in July 2009 of Oracle Fusion Middleware 11g, including tools such as Oracle Forms 11g and Oracle Reports 11g, Oracle customers are looking for guidance on their choice of development tools for existing and future application development.

 

Oracle customers have a considerable level of investment in applications developed using the Oracle Forms and Reports products, and, frequently, ask questions about Oracle’s strategy and commitment to these products.

The statement of Oracle is very clear: Oracle has no plan to de-support these products. Furthermore, new version of Oracle Forms, Oracle Reports will continue to be released as part of Oracle Fusion Middleware and Oracle Forms 11g and Oracle Reports 11g are components of Oracle Fusion Middleware 11g. In line with Oracle’s product strategy, future development activities will be aimed at smoother version-to-version upgrade, integration with features of the platform/ technology stack and product stability.

Given the rapid growth and adoption of Java/Java EE technologies in the industry, Oracle’s strategy is also to provide a productive Java EE development environment (JDeveloper and ADF) allowing Forms and Reports customers to embrace Java EE development while still using a familiar RAD, visual and declarative approach.

Following the successful example of the Oracle E-Business Suite, Oracle recommends customers to web deploy their existing Forms and Reports applications, consider the opportunities of new development in Java EE using JDeveloper and ADF and integrate these applications together on the application server, sharing common services and business logic. Thus, existing Forms and Reports applications can co-exist with new services and applications built on Java EE and share common infrastructure of Oracle Fusion Middleware.

With this strategy, Oracle allows customers to continue to leverage their existing investments for many years while offering a path to incrementally move to Java EE, at their own pace, using a productive and familiar development environment (JDeveloper and ADF).

At Contribute, we can help your organization to choose the right direction for your current and future development projects. Our specialists have experience in upgrading Forms to the latest 11g release, modernize Forms - by adding new functionalities and integrate them with SOA technologies - or completely migrate your Forms investment to Oracle ADF or another Java related technology.

Over 4 years ago I wrote a blog post about software that helps you to compare two Oracle schemas. That post is accessed a lot and I still get questions about it, so I decided to write a follow up on that post as things change over time.

Comparing two Oracle schemas is still something I have to do regularly, e.g. if I want to compare a development, test and production instance after a deployment of an application.

Alongside Oracle Application Express (APEX), I use Oracle SQL Developer daily. They have a built-in Database Diff tool which works, but I tend to use other tools as well, as it’s hard with SQL Developer to see exactly what has changed. We built our own tool in APEX that compares schemas based on dbms_metadata, which we use when we have access only through the APEX interface. But when I can run things from my own system, I like to use Schema Compare for Oracle.

The people of Red Gate wrote a nice post on how to use their software, with screenshots and steps you can follow, so I won't repeat that. Instead I’m going to compare it against the Diff Tool built into SQL Developer.

Simplicity

The older I get, the more I can appreciate user-friendly software. That is something I try to do in the software I write myself, follow the KISS principle = keep it simple stupid.

For me software has to look good, ’be easy to use and do what it’s intended to do in an efficient way. If I want to compare two schemas I just want to follow a couple of steps:
1. Select my source and target database
2. Select the schema(s) I want to compare
3. Optional - select what exactly it should compare (tables, packages etc.)
4. Get an overview of the results
5. Produce synchronization scripts

Schema Compare for Oracle (SCfO)

Here’s how it works in Schema Compare for Oracle.

When you open Schema Compare you have to create or open a project.


It asks you for the source and target database you want to connect to and which schema you want to compare. An advantage in SCfO is that you can compare multiple schemas at once.
Another thing I found interesting was the option to compare against a Snapshot. You basically take a “picture” of your schema at a certain moment in time and compare against that. Very handy when you can’t access both schemas from the same location. Or if you develop applications and want to generate upgrade scripts, I see the use of snapshots too.

But let’s get further with the normal schema compare.

In SCfO you don’t select the objects first, you just hit the Compare Now button and SCfO starts to compare the whole schema directly. At first I thought, I don’t really want you to do that as you are doing a lot of work for nothing, but the more I used the tool, the more I appreciated it. To start a comparison is very easy and quick...

One thing I found handy as well is the Options tab, which allows you to define if the tool needs to ignore white spaces, storage clauses etc.


When we are happy with the options, we click the Compare Now button and we get a screen with the progress.


Once it’s finished you have a complete overview of the differences in both schemas.


I like this screen a lot as it has many more functionalities than you would first imagine. The filter (find box) is very handy to find some specific objects quickly. You can also sort by different things and just from the way it looks it is so easy to understand what is different in which schema and database! But the nicest thing is when you click on a row where there are differences. It shows you both versions and highlights the differences. No need for an extra tool or text editor to get that information out. It’s just there.


If you want to create an upgrade script or make both schemas equal you just have to use the Synchronization Wizard...

The wizard asks you what it has to do and generates a script for you or automatically synchronizes both schemas based on the objects you selected.


I think Schema Compare for Oracle does it really well. So let’s compare it to the comparison tools that come with Oracle SQL Developer.

Oracle SQL Developer

In SQL Developer you go to Tools - Database Diff.

The first time it will tell you it’s using Oracle Change Management, a payable option of the Oracle database, and you have to acknowledge you have a proper license to use that.


Next, it will pop up with a screen that allows you to select a source and destination connection and immediately asks you what objects you want to compare.


Clicking on Next shows you a screen where you can select the individual objects.
You can view all objects at once, or change the select-list to only see tables, sequences and select the objects that way. This might be a good thing to do if you have many objects!


Once you click Finish it will compare the objects and present you the result in a Diff Report.


You can click on the green SQL button and it will generate the script for you based on the differences you selected.

So it’s rather straightforward to compare two Oracle schemas in SQL Developer, but there are some things I would prefer a bit different.

This is my wish list:
1. Not be forced to have the Change Management license for your Oracle database. The price depends your Oracle Database license, but it can be high if you just want to compare two schemas through SQL Developer.
2. Be able to compare multiple schemas at the same time.
3. Get a cleaner Diff Report, which allows me to add filters to it and allows me to see what exactly is different between the two versions. At the moment I just see a count there, when I click on the row. I get the script to generate that version, but I couldn’t find a way to see exactly the source and target and compare them side-by-side.

Point 3 is my biggest issue and that is why I searched for something else that made my life easier and finally came across Schema Compare for Oracle. Although Red Gate created a Windows only version of Schema Compare for Oracle, so as a Mac user I need to run it through a VM, I find it worthwhile to do it. The interface of SCfO is very clean, it’s easy to use and it gives me most features I’m searching for. There is even a SQL Developer plugin for SCfO (search for Schema Compare for Oracle in the SQL Developer Plugin repository).

So is Schema Compare perfect and is it better in every aspect than the Diff Tool in SQL Developer? Almost, but not completely. SQL Developer supports the comparison of Materialized Views (and their logs) and Database links, which SCfO doesn’t do in the release I tested (1.3).The version of SQL Developer I used was 2.1. I’m not sure the Diff Tool in SQL Developer 3 (which will be released in a few weeks/months) will be different...

Happy comparison!

Wondering what is there to see at Oracle Open World and Oracle Develop if you are interested in Oracle ADF Development or Oracle JDeveloper in general? Well your official guide is now out here. I think there are about 50... shay.shmeltzer http://blogs.oracle.com/shay

In my latest PL/SQL newsletter (if you don't yet receive it, visit www.stevenfeuerstein.com and enter your email in the yellow box on the left side of the home page), I passed on a tip I got during a training, namely:
Tech Tip: A New Place for Your Comments?I just gave a two-day, "Best of Oracle PL/SQL" training to a team of developers in Winona, MN, home of the Sugar Loaf Bluff, which I climbed after the first day of training.

One of the development managers, Jason McClellen of Fastenal, offered the following suggestion when I talked about the importance of application tracing or instrumentation:

Comments are usually added to code as "static" text, visible only when you open up and look at your source code. Instead of hiding away your comments, include them in the calls to your tracing mechanism. That way, whenever tracing is enabled, you not only see values of data being manipulated by the program, you also read information that informs your understanding of that data.

I think this is a fantastic idea, and I plan to start doing this in my own code. In Quest Code Tester for Oracle, I use a variation on the Quest Error Manager to do my tracing, so a trace call (preceded by a comment) might look like this:

/*
Simple datatypes require an initial value. This "value"
includes the assignment operator to simplify template construction.
*/
IF qu_runtime.trace_enabled
THEN
   qu_runtime.
    trace (
      'set_placeholders typename'
    , outcome_in.result.attribute.datatype_declare
   );
END IF;

In the future (in fact, right now - I will "refactor" this code), my tracing will look like this:

IF qu_runtime.trace_enabled
THEN
   qu_runtime.
    trace (
      'set_placeholders'
    , 'Simple datatypes require an initial value. This "value" '||
        'includes the assignment operator to simplify template construction.'
   );
   qu_runtime.
    trace (
      'set_placeholders typename'
    , outcome_in.result.attribute.datatype_declare
   );
END IF;

I then asked readers to tell me what they thought. Several people wrote in and I will post this in a reply. Feel encouraged to add your own thoughts.

The PL/SQL Challenge recently (6 August) offered a quiz on the INSTR function. I showed everyone the header of this function from the STANDARD package in which it is defined:

-- Find nth occurrence of str1 in str2 starting at pos
function INSTR(STR1 VARCHAR2 CHARACTER SET ANY_CS,
   STR2 VARCHAR2 CHARACTER SET STR1%CHARSET,
   POS PLS_INTEGER := 1,
   NTH POSITIVE := 1) return PLS_INTEGER;

Jean Xu reported a problem with the comment above the header. It should say "of str2 in str1"! I'd never noticed this, with all the times I've looked at the STANDARD package. I let Bryn Llewellyn, PL/SQL Product Manager, know about this, so hopefully the next release of Oracle will include a correction.

Yet another benefit of playing the PL/SQL Challenge: you can help improve the quality of the PL/SQL language itself!

So if you are not yet one of the more than 2400 developers who have been taking the quiz in the last month, register and start playing today!

There can be situations where you need to add some security like HTTP basic authentication to your Composite Services or References. Especially when you have some HTTP Binding Services or References. The HTTP Binding Service in SOA Suite 11g also has a SOAP endpoint beside the HTTP endpoint. With the SOAP endpoint you can always use WS-Security instead of the basic authentication, but if that was the case you won't choose for the HTTP Binding.    
For this blogpost I will use my http binding example of this blogpost 
In this example I have a Mediator with a HTTP Binding Reference. This reference has as endpoint the execute url of the Execute HTTP Binding Service, which is connected to the BPEL Component.
Select the execute Service and configure SOA WS Policies, Here you need to select the oracle/wss_http_token_service_policy . This OWSM policy enables HTTP Basic authentication for HTTP  & SOAP or WSS Username Token in SOAP.

For the Composite Reference you need to use the oracle/wss_http_token_client_policy.

Off course you need to provide the username / password for the basic authentication.  To do this you need to go to the Enterprise Manager Application and select your WebLogic Domain. In the Menu, select the Security menu Item and then go to Credentials.

When you don't have the oracle.wsm.security Map then you need to create this. In the Map you need to add the basic credentials Key where you can provide the username / password for the HTTP Binding Service and Reference.
After rebooting the SOA Server you can test this HTTP Binding Service. I use Wfetch of Microsoft. The internal tester client of WebLogic and Enterprise is not so great with HTTP posts and security.

First test is a POST on the HTTP endpoint with a bad username.  This gives a HTTP 403 Forbidden.
Now with a good username / password and for the POST I only have to provide the request in the body and without the SOAP envelop.

The HTTP Binding service also has a SOAP Endpoint. First we test this with a bad username.


Now with a good username / password. For the SOAP post you need to provide the Content-Type and SOAPAction HTTP Headers and the SOAP envelope with the request.

That's all.

With all the Fusion Middleware releases of late April 2010 its nice to take a look at the security part of FMW & OSB 11g. You may already know FMW 11g Patch Set 2 is a patch on PS1 which was a full release but OSB 11g is a new release. Lets see if Oracle changed WSM in PS2 and what they did with the Weblogic 9.2 WS-Security policies in OSB 11g.
Lets start with WSM  in PS2, I did some checks and came to the conclusion that it works the same as in PS1, so my WSM in FMW blogpost is still valid. The only thing what changed is that JDeveloper 11g PS2 give you more information about the WSM policies.
   
OSB11g is a major release and in this 11g release Oracle did not change the WS-Security policies of  Weblogic 9.2, so my OSB blogpost about the security policies in OSB 10.3 is still valid in the 11g version. From this you can conclude Oracle is going full for WSM. With OSB 11g Oracle added support for WSM agents. You can now use the WSM server policies on your OSB Proxy services and the WSM client policies on the OSB Business services. But more inportant, it is now easy to call these secured WSM proxy services from other FMW components ( ADF , Soa Suite & Java )
To use WSM in OSB 11g you need to have a MDS repository and extend the OSB Domain with the WSM components.
When your OSB server is running then you can add the WSM server policy to a Proxy Service. Just select the OWSM policy option and add the right WSM server policy

It works the same for an OSB business service only now you use the client policies.
Before you can test these WSM policies you need to follow the steps of  my FMW WSM blogpost . If you configured the WSM keystore then you can test the proxy service in the service bus console application ( sbconsole) . In the security part of this testpage you only  need to provide the keystore.recipient.alias. Leave the rest empty.

Or you can generate a web service proxy service in JDeveloper and use this in your Proxy client.

public class HelloWorldServiceSoapHttpPortClient
{
  @WebServiceRef
  private static HelloWorldService_Service helloWorldService_Service;

  public static void main(String [] args)
  {
    helloWorldService_Service = new HelloWorldService_Service();
        SecurityPolicyFeature[] securityFeatures =
            new SecurityPolicyFeature[] { new SecurityPolicyFeature("oracle/wss10_message_protection_client_policy") };
        HelloWorldService helloWorldService = helloWorldService_Service.getHelloWorldServiceSoapHttpPort(securityFeatures);
        // Add your code to call the desired methods.

        Map<String, Object> reqContext = ((BindingProvider) helloWorldService).getRequestContext();
        reqContext.put(ClientConstants.WSSEC_KEYSTORE_TYPE, "JKS");
        reqContext.put(ClientConstants.WSSEC_KEYSTORE_LOCATION, "C:\\test_keystore.jks");
        reqContext.put(ClientConstants.WSSEC_KEYSTORE_PASSWORD, "password");
        reqContext.put(ClientConstants.WSSEC_SIG_KEY_ALIAS, "client_key");
        reqContext.put(ClientConstants.WSSEC_SIG_KEY_PASSWORD, "password");
        reqContext.put(ClientConstants.WSSEC_ENC_KEY_ALIAS, "client_key");
        reqContext.put(ClientConstants.WSSEC_ENC_KEY_PASSWORD, "password");
        reqContext.put(ClientConstants.WSSEC_RECIPIENT_KEY_ALIAS, "client_key");
        

        System.out.println(helloWorldService.sayHello());

  }
}

       

In Fusion Middleware 11g it is not so difficult to protect your JAX-WS Web services or your Composite Services. You just need to add an Oracle Web Service Manager service policy to this Web Service. So that's all the work for the developer or release manager. And now the work starts for the Administrator. This persons need to be familiar with the Enterprise Manager, WebLogic Console, OpenSLL and with the keytool utility of the JDK. In this blogpost I will show what you need to do if you choose for a particular OWSM Policy.


Let's start simple with one of the following policies
oracle/wss_http_token_service_policy
oracle/wss_username_token_service_policy
These policies can be used for HTTP Basic Authentication or for an Username Token in a SOAP message. The only thing you need to do for these policies is to add some Users to the myrealm Security Realm in the WebLogic Console.
On the client side you need to do the following.

execute = new Execute();
SecurityPolicyFeature[] securityFeatures =
            new SecurityPolicyFeature[] { new SecurityPolicyFeature("oracle/wss_username_token_client_policy") };
Request_Response_ptt request_Response_ptt = execute.getRequest_Response_pt(securityFeatures);
    
// Add your code to call the desired methods.
Map<String, Object> reqContext = ((BindingProvider) request_Response_ptt).getRequestContext();
reqContext.put(BindingProvider.USERNAME_PROPERTY, "test" );
reqContext.put(BindingProvider.PASSWORD_PROPERTY, "weblogic1" );
       
Request req = new Request();
req.setName("edwin");
req.setMessage("hi");
Response resp = request_Response_ptt.requestResponse(req);

The Message protection policies
oracle/wss10_message_protection_service_policy
oracle/wss11_message_protection_service_policy
When you choose for one of these policies you need to generate a Server certificate for encryption and put this in a Java keystore and for the Client side you also need to make a Keystore but this contains only the public key of this Server encryption certificate.

To add your Server keystore to FMW, you need to go to the Enterprise Manager and select your Weblogic Domain. In the menu go to the Security / Security Provider Configuration page. And on this page you can import your Java keystore. Before you start you need to copy your keystore to your domain folder and put this in the config/fmwconfig folder.

In this example I used two certificates one for the signature and one for the encryption. For Message protection Service policies you only need the encryption certificate.
On the client side you need to load the client keystore and the public key of server encryption certificate. 

execute = new Execute();
SecurityPolicyFeature[] securityFeatures =
    new SecurityPolicyFeature[] { new SecurityPolicyFeature("oracle/wss11_message_protection_client_policy") };
Request_Response_ptt request_Response_ptt = execute.getRequest_Response_pt(securityFeatures);
// Add your code to call the desired methods.
Map<String, Object> reqContext = ((BindingProvider) request_Response_ptt).getRequestContext();

reqContext.put(ClientConstants.WSSEC_KEYSTORE_TYPE, "JKS");
reqContext.put(ClientConstants.WSSEC_KEYSTORE_LOCATION, "C:/client_keystore.jks");
reqContext.put(ClientConstants.WSSEC_KEYSTORE_PASSWORD, "welcome");

reqContext.put(ClientConstants.WSSEC_ENC_KEY_ALIAS, "server_encr");
reqContext.put(ClientConstants.WSSEC_ENC_KEY_PASSWORD, "welcome");
reqContext.put(ClientConstants.WSSEC_RECIPIENT_KEY_ALIAS, "server_encr");

Request req = new Request();
req.setName("edwin");
req.setMessage("hi");
Response resp = request_Response_ptt.requestResponse(req);

The above policies can also be combined. Like in these policies.
oracle/wss10_username_token_with_message_protection_service_policy
oracle/wss11_username_token_with_message_protection_service_policy

For these policies you need to a create user in the WebLogic Console for the username token and generate a server and client keystore for the message protection part.
On the client side you need to the following.

execute = new Execute();
SecurityPolicyFeature[] securityFeatures =
    new SecurityPolicyFeature[] { new SecurityPolicyFeature("oracle/wss11_message_protection_client_policy") };
Request_Response_ptt request_Response_ptt = execute.getRequest_Response_pt(securityFeatures);
// Add your code to call the desired methods.
Map<String, Object> reqContext = ((BindingProvider) request_Response_ptt).getRequestContext();
reqContext.put(BindingProvider.USERNAME_PROPERTY, "test" );
reqContext.put(BindingProvider.PASSWORD_PROPERTY, "weblogic1" );

reqContext.put(ClientConstants.WSSEC_KEYSTORE_TYPE, "JKS");
reqContext.put(ClientConstants.WSSEC_KEYSTORE_LOCATION, "C:/client_keystore.jks");
reqContext.put(ClientConstants.WSSEC_KEYSTORE_PASSWORD, "welcome");

reqContext.put(ClientConstants.WSSEC_ENC_KEY_ALIAS, "server_encr");
reqContext.put(ClientConstants.WSSEC_ENC_KEY_PASSWORD, "welcome");
reqContext.put(ClientConstants.WSSEC_RECIPIENT_KEY_ALIAS, "server_encr");

Request req = new Request();
req.setName("edwin");
req.setMessage("hi");
Response resp = request_Response_ptt.requestResponse(req);

The last part of this blogpost I will explain the following policies
oracle/wss10_x509_token_with_message_protection_service_policy
oracle/wss11_x509_token_with_message_protection_service_policy

These policies will use the client certificate for the signature and the public key of the server encryption certificate for the encryption.
So we start by making some keystores with some certificates. I don't use self signed certificates because then for every new client I need to update the server keystore and reboot the FMW server. Now I only have to import the CA public certificate in the Server keystore. This is how my Server keystore looks like

It got a private certificate for the server signature and for encryption. The CA public key is trusted.

For the client I have this keystore. ( Every customer / application can have its own client keystore )

The CA and Server encryption certificates are public certificates and are trusted.
Because the FMW Server does not know this client certificate ( it only knows the CA ) you need to add a new user in the myrealm Secuirty Realm in the WebLogic Console. The password of this user is not important, the only requirement is that the common name of this client certificate is the same as the WebLogic Username.

And as last the Client code, where we need to provide the client signature certificate details.

execute = new Execute();
SecurityPolicyFeature[] securityFeatures =
    new SecurityPolicyFeature[] { new SecurityPolicyFeature("oracle/wss11_x509_token_with_message_protection_client_policy") };
Request_Response_ptt request_Response_ptt = execute.getRequest_Response_pt(securityFeatures);
// Add your code to call the desired methods.
Map<String, Object> reqContext = ((BindingProvider) request_Response_ptt).getRequestContext();

reqContext.put(ClientConstants.WSSEC_KEYSTORE_TYPE, "JKS");
reqContext.put(ClientConstants.WSSEC_KEYSTORE_LOCATION, "C:/client_keystore.jks");
reqContext.put(ClientConstants.WSSEC_KEYSTORE_PASSWORD, "welcome");

reqContext.put(ClientConstants.WSSEC_SIG_KEY_ALIAS, "client1");
reqContext.put(ClientConstants.WSSEC_SIG_KEY_PASSWORD, "welcome");

reqContext.put(ClientConstants.WSSEC_ENC_KEY_ALIAS, "server_encr");
reqContext.put(ClientConstants.WSSEC_ENC_KEY_PASSWORD, "welcome");
reqContext.put(ClientConstants.WSSEC_RECIPIENT_KEY_ALIAS, "server_encr");

Request req = new Request();
req.setName("edwin");
req.setMessage("hi");
Response resp = request_Response_ptt.requestResponse(req);

Next time I will try to describe the SAML policies.

Number column as boolean

When you use an Oracle database, you don't have a boolean type to use in your tables. Usually, you work around this problem by using a numeric column with values 0 and 1. This however could cause some extra programming work when building an application on top of your database. The numbers have to be converted to booleans, and vice versa.

Now, in ADF Business Components 11g, Oracle solves this problem for you. When you create an Entity Object from a database table, you have the option to specify the type 'Boolean' when adjusting your column settings. If you select 'Boolean' on a numeric column, it will automatically map 0 to false and all other values to true. That way, you can use a boolean throughout your entire application without having to worry about the conversion to and from a numeric value.

Hi just read Joel Kallman's post that the Oracle Application Express (APEX) team released 4.0.1 which corrects a number of issues found in APEX 4.0.

I recommend going to 4.0.1 if you have 4.0 installed as soon as possible.

If you still have 3.x running and want to upgrade to 4.0 you can directly download the 4.0.1 release and install that.

The new version of APEX is 4.0.1.00.03.

Quest Software and AUSOUG sponsored a PL/SQL Challenge Live quiz at the Insync 2010 conference on 16 August (which took place just 3 hours after I arrived from a very long odyssey from Chicago). This is the second live quiz I have done (the first was at ODTUG's Kaleidoscope conference in June), and it went much smoother than the first, since I built a PL/SQL procedure to automatically compute winners (manual processes really are things to avoid whenever possible!).

Forty-two developers played the quiz and I am pleased to announce that Jeff Kemp, currently ranked #5 in this quarter on the daily quiz, took first place with a score of 37 points out of a possible maximum score of 43. He wins a $100 giftcard from Westfield Mall. Here is list of the top 20 scorers in the competition:

01 Player JEFF KEMP = 37
02 Player STUART WATSON = 34
03 Player INDU NEELAKANDAN = 33
04 Player TREVOR TRACEY-PATTE = 33
05 Player BRETT MCBRIDE = 32
06 Player MARIA DE MESA = 32
07 Player KHOI NGUYEN = 32
08 Player IAN CORKHILL = 31
09 Player MARC THOMPSON = 31
10 Player ROHAN MILTON = 31
11 Player SWEE KEONG TAN = 31
12 Player RAY FEIGHERY = 31
13 Player GARETH OWEN-CONWAY = 30
14 Player TIM DANIELL = 30
15 Player ASHOK SHOKKANNAA = 30
16 Player THOMAS WENDE = 29
17 Player PANKAJ DADOO = 29
18 Player ROGER SCHNEIDER = 29
19 Player ANTHONY BRUMBY = 29
20 Player R F = 29

Hopefully they will all register at and play the daily quiz of the PL/SQL Challenge as well. They clearly have lots of promise! And if you are not yet playing the daily quiz of the PL/SQL Challenge, well please do join the other 1400 developers who play almost every day, learning more about PL/SQL and winning weekly and monthly prizes.

Finally we were lucky enough to have Tom Kyte in attendance at the live quiz. He was kindly pointed out two subtle problems with the quiz, so I cleaned those up for the other quizzes this week. One point will also result in a minor change in our assumptions.

You may increase the efficiency of your Java applications by applying some optimization methods on the Java byte-code. Shrinking and obfuscation are commonly used for this purpose. Shrinking is the process of removing unused classes, methods and fields, resulting in the processed byte-code having smaller size. Obfuscation is primarily used to make the code harder to debug and reverse-engineer. As an additional feature, obfuscation also reduces the size of the byte-code, since this process replaces the identifers in class files with shorter ones.

Reduction in the size of byte-code often implies an improvement in load time. Applet developers could especially benefit from such optimizations, since the transfer of compiled code accross a network is an important bottleneck.

In this article, I will show the use of optimization methods on a sample JavaFX application. I will use ProGuard 4.5 as the optimization tool. I choosed this tool, because it is available under the GPL free licence, easy to use and has a few more additional features than the other options out there. For more information about ProGuard, please see http://proguard.sourceforge.net/ .

Here is the JavaFX code:

package test;


import javafx.stage.Stage;
import javafx.scene.Scene;
import javafx.scene.text.Text;
import javafx.scene.text.Font;
import java.applet.Applet;
import javafx.scene.layout.VBox;
import org.apache.tools.ant.taskdefs.Concat;
import org.apache.tools.ant.taskdefs.Cvs;
import org.apache.tools.ant.taskdefs.Delete;
import org.apache.tools.ant.taskdefs.GZip;
import org.apache.tools.ant.taskdefs.Length;
import org.apache.tools.ant.taskdefs.Move;
import org.apache.tools.ant.taskdefs.SendEmail;
import org.apache.tools.ant.types.resources.selectors.Date;

// Create some random objects which reference the Ant.jar
var objList = new java.util.ArrayList();
objList.add(new Concat());
objList.add(new Move());
objList.add(new Cvs());
objList.add(new Delete());
objList.add(new GZip());
objList.add(new Length());
objList.add(new SendEmail());
objList.add(new Date());

for(obj in objList){
println(obj);
}

var startTime = FX.getArgument("startTime");
var loadTime = 0.0;

try { 
var endTime = java.lang.System.currentTimeMillis();
var startTimeL = Long.parseLong(startTime.toString());
loadTime = (endTime - startTimeL);
} catch (e : java.lang.Exception) {
e.printStackTrace();
}

Stage {
scene: Scene {
width: 400
height: 250 
content: [
Text { content: "Load Time: {loadTime} ms"
y: 150 x: 100 font: Font { size: 25 } }
]
}
}

The only task this code carries out is to create some random objects referencing the Ant library. This may not mean much for an applet, but the purpose of this example is to demonstrate the difference between the load times of optimized and non-optimized byte code. Now, since we have references to ant.jar file from JavaFX code, we may expect a faster loading time after making some optimizations on ant.jar. Here are the steps to follow:

1. Download the ProGuard distribution and extract it to a location on your computer.
2. Run proguardgui.bat which resides in ${PROGUARD_HOME}\bin directory. This will open ProGuard in graphical mode. (When you click the buttons in the menu on the left side, appropriate pages will be opened to allow you make configurations.)
3. Input/Output configuration: As input, we need to add the ant.jar file, since this is the one we want to process. We also need to specify the output path to which the ProGuard will generate the final jar. In the libraries section, ProGuard allows us to specify referenced libraries. Java runtime library is added in this section by default, so we don't need to make further changes here.

4. Shrinking configuration: We may specify which classes to shrink or keep at this page. In our case, we should keep the classes that is referenced from JavaFX. Down at the page, the section titled “Keep additional classes and class members” allows us to do that.

5. Obfuscation configuration: Similar to the shrinking step, we should keep the JavaFX referenced classes from being obfuscated.
6. I did not make any changes in Optimization Page. Finally, I had to check “Ignore warnings about possibly erronous input” box in Information Page, since ProGuard could not produce the output jar without this option checked. In our example, this change does not cause any problems, however you should be careful with this option when you are working on your own projects.

7. Now that we made the necessary configurations, we are ready to process the file. Clicking on the Process button on the Process page will produce the output jar for us.

Let's compare the size's of the original (input) ant.jar file, and the processed (output) ant.jar file:        Original:    1.476 KB    Processed: 300 KB
As you can see, there is a significant reduction in the size of jar file.
You can download the source code and binaries from here. When you extract the downloaded package, you  will see two folders named as optimized_test and nonoptimized_test. You can compare the load times by opening Main.html files in these folders. Please note that, you should clear the Java cache between consecutive executions of the JavaFX application. Otherwise, the results will not be healthy.
Below are my results of load time comparison :

Internet Explorer 8.0Google Chrome 5.0Non-Optimized1045 ms1250 msOptimized776 ms958 ms
My system properties are:
Processor: Intel Core 2 Duo T9550 2.67GHz
Operating System: Windows 7 Ultimate 32 bit
Memory: 3 GB


Yücel Ünlü

========================================bpel.xml==============================
<?xml version = '1.0' encoding = 'UTF-8'?>
<BPELSuitcase>
   <BPELProcess id="RawQueuePayloadUsingNativeFormat"
src="RawQueuePayloadUsingNativeFormat.bpel">
      <partnerLinkBindings>
         <partnerLinkBinding name="Dequeue">
            <property name="cacheConnections">false</property>
            <property name="wsdlLocation">Dequeue.wsdl</property>
            <property name="retryMaxCount">10</property>
            <property name="retryInterval">60</property>
         </partnerLinkBinding>
         <partnerLinkBinding name="Enqueue">
            <property name="wsdlLocation">Enqueue.wsdl</property>
            <property name="retryMaxCount">10</property>
            <property name="retryInterval">60</property>
            <property name="cacheConnections">false</property>
         </partnerLinkBinding>
      </partnerLinkBindings>
      <activationAgents>
         <activationAgent
className="oracle.tip.adapter.fw.agent.jca.JCAActivationAgent"
partnerLink="Dequeue">
            <property name="portType">Dequeue_ptt</property>
         </activationAgent>
      </activationAgents>
   </BPELProcess>
</BPELSuitcase>
============================================================================== ======/SOA/10.1.3.1/OracleAS_1/j2ee/oc4j_soa/config/data-sources.xml============
==============================================================================
<managed-data-source connection-pool-name="AQSAMPLE_POOL"
jndi-name="jdbc/aqSample" name="AQSAMPLE_DS"
manage-local-transactions="false" tx-level="global" />
<connection-pool name="AQSAMPLE_POOL" validate-connection='true'>
      <connection-factory
factory-class="oracle.jdbc.xa.client.OracleXADataSource" user="scott"
password="welcome1" url="jdbc:oracle:thin:@localhost:1521:orcl"
connection-retry-interval="60" max-connect-attempts="60"
commit-record-table-name="" validate-connection-statement="select 'x' from
dual"/>
      <connection-properties>
          <property name="oracle.jdbc.ReadTimeout" value="300000"/>
      </connection-properties>
</connection-pool>
==============================================================================
==/SOA/10.1.3.1/OracleAS_1/j2ee/oc4j_soa/application-deployments/default/AqAdapter/oc4j-ra.xml==
==============================================================================
<connector-factory location="eis/AQ/aqSample" connector-name="AQ Adapter">
                <config-property name="xADataSourceName"
value="jdbc/aqSample"/>
                <config-property name="dataSourceName" value=""/>
                <config-property name="connectionString" value=""/>
                <config-property name="userName" value=""/>
                <config-property name="password" value=""/>
                <config-property name="defaultNChar" value="false"/>
                <config-property name="useDefaultConnectionManager"
value="true"/>
                <connection-pooling use="none">
                </connection-pooling>
                <security-config use="none">
                </security-config>
        </connector-factory>
==============================================================================

Note: I am using useDefaultConnectionManager not private connection

Additional properties you check for RAC and other timeouts:

validate-connection="true" time-to-live-timeout="60" abandoned-connection-timeout="60" property-check-interval="600" inactivity-timeout="60" initial-limit="10" max-connections="20" min-connections="10"
<property name="connectionCachingEnabled" value="true"/>
<property name="fastConnectionFailoverEnabled" value="true" />

For 10.1.3.X XA Datasource Requirement refer:
Oracle Metalink/Support  note:738108.1
http://www.oracle.com/technology/tech/soa/pdf/oracle-soa-suite-xa-rac-guide.pdf
http://www.oracle.com/technology/tech/java/newsletter/articles/oc4j_datasource_config.html
http://www.oracle.com/technology/tech/java/newsletter/articles/oc4j_data_sources/oc4j_ds.htm
http://download.oracle.com/docs/cd/B25221_04/web.1013/b14427/datasrc.htm#sthref442

http://www.oracle.com/technology/pub/notes/technote_ds_caching.html

http://www.oracle.com/technology/sample_code/products/rac/index.html

I will be conducting a third series of webcasts for Quest Software that will put you on the fast track to maximizing your understanding and use of Oracle PL/SQL. Here are lots of details and links for registration. I hope to "see" you there!

Session 1: Programming with Collections
Monday, September 27, 2010 at 2:00 p.m. Eastern ($200.00 US)
Collections (array-like structures in PL/SQL) are critical data structures that are used in some of the most important features of PL/SQL. Every PL/SQL developer should have a deep familiarity with collections and the wide range of features that Oracle has implemented for them over the years. This session introduces collections and quickly moves on to detailed explanations of collection methods, how to leverage string indexing in associative arrays, multi-level collections, set-level operations on nested tables and more. The session will run for approximately two hours. 
Register 

Session 2: Say Goodbye to Hard-Coding in PL/SQL
Wednesday, October 6, 2010 at 2:00 p.m. Eastern ($200.00 US)
Everyone knows that hard-coding is a bad idea. Too many developers, however, only think in terms of literal values when they think of hard-coding. There are, unfortunately, many ways that hard-coding manifests itself in our programs. This webinar offers a comprehensive look at all the types of hard-coding that can appear in your programs, from literals to explicit declarations to exposed formulas, and offers specific techniques to get rid of the hard-coding. The result is code that is much easier to read and to maintain. The session will last approximately two hours.
Register

Session 3: Dynamic SQL in Oracle PL/SQL
Wednesday, October 20, 2010 at 2:00 p.m. Eastern ($200.00 US)
Dynamic SQL (construction, parsing and execution of SQL statements and PL/SQL blocks at runtime) has become a common and critical element of most modern applications. PL/SQL offers two distinct methods for dynamic SQL: native dynamic SQL  (NDS) and DBMS_SQL. This webinar focuses primarily on the capabilities of native dynamic SQL: how to use EXECUTE IMMEDIATE to execute dynamic query, DML, and DDL statements. We will also go beyond the basics and delve into when to use DBMS_SQL (method 4 dynamic SQL), and new Oracle Database 11g features for dynamic SQL (most importantly, interoperability between DBMS_SQL and NDS.  The session will last approximately two hours.
Register

As you might already know, JavaFX allows you to use your existing Swing components in your applets. All you have to do is to add a few lines of JavaFX code. However, you may run into unexpected problems because of Java Web Start security model. We had such an issue in one of our projects, when we tried to transform a custom swing component into a JavaFX widget. The problem was that the resources such as button icons were not being loaded correctly. When I investigated the code, I noticed that I was using ClassLoader.getSystemClassLoader calls to load resources. However, Java Web Start uses a user-level classloader to load the application resources instead of the system classloader. I got rid of the problem by replacing ClassLoader.getSystemClassLoader calls with this.getClass().getClassLoader().
You may find more information about this issue at http://download.oracle.com/javase/1.5.0/docs/guide/javaws/developersguide/faq.html#211.
I hope this tip saves some time for you.


Yücel Ünlü

So you already did the tutorials, wrote a basic application and have a basic understanding of ADF? Well here is your chance to go to the next level of knowledge. The JDeveloper product management team has started producing a series... shay.shmeltzer http://blogs.oracle.com/shay

If you want to set a default value for a column in your tabular form, you can set the Default Type to "PL/SQL Expression or Function" and put into Default e.g. 'N'.


That sets the default for that column to the static text 'N'.

Almost three weeks ago, my son Eli started working for me as an APEX programmer - with absolutely no background in software. He'd focused mainly on being a musician for the last five+ years (guitar, songwriting, etc.). So: minimal mathematics education; no knowledge of SQL or PL/SQL; no experience with HTML, Javascript....what were we getting ourselves into?

I am very pleased to report that Eli has made rapid progress. He is now building basic APEX applications, writing queries and other SQL statements, making changes to the PL/SQL Challenge application (very simple changes for now!), learning HTML.

He whips through APEX design pages so quickly, I start to get dizzy trying to follow him. It looks like this is going to work out very, very well.

Eli told me that while he hadn't written software, he had lots of experience with creating and manipulating music in his head. We agreed that this would likely help with programming and I would say that this is now well established. While Eli had not experience with SQL syntax, his brain seems to be very nicely trained to work with a large number and highly complex abstractions. So the switch from music to logic/code seems to be a smooth one.

In addition, while Eli is not video game fanatic, he has certainly spent time on various game consoles from a relatively early age. I think back to a 10 year old Eli playing a game that involved choosing and arming characters for a quest, solving puzzles on the screen, quickly paging through menus and options....and then I look at him "playing with" the APEX interface.

I realize that for a person who has trained his or her brain to work with a rich UI found in video games, in which most of your activity is point-and-click rather than typing, an environment like APEX is "child's play" - a relatively crude and simplified interface.

So I now declare myself happy that Eli played video games, to prepare himself for the "real world."

Eli, it has been a real pleasure to work with you and I look forward to lots more of it in the future!

When you bind a method to an ADF page and also include the results of the method on the page , by default ADF will try to execute the method when the page displays so the results can be populated.... shay.shmeltzer http://blogs.oracle.com/shay

Oracle Application Express 4.0 introduces lots of big new features; websheets, dynamic actions, plug-ins, RESTful web services, team development, updated charts, the list goes on. But there are also many enhancements to existing functionality that we hope will help to simplify the overall process of developing applications in APEX. One such area, and the focus of this post is how data is validated. This post will give you an overview of what's changed with validations and how these changes will make your daily development life a little easier.


Item-Centric Validation
Historically in APEX if you want to validate data input on a page, you create a validation. The validation is a separate component that you need to define and maintain. Now in APEX 4.0, the actual item can handle some simple validation of the data it receives. For example, all item's (both native to APEX and plug-ins) now have a 'Value Required' attribute. By setting this to 'Yes', APEX will automatically validate a value has been entered and raise an error if not, no separate validation required.

Additional to this 'Value Required' validation, some item types also validate their data, based on how the item is defined. For example, the new 'Number' item type, which you can use for handling numeric data contains settings for 'Minimum Value' and 'Maximum Value'. When these are defined, APEX will automatically validate the data received based on these settings and raise appropriate errors.

Settings for the new 'Number' item, settings highlighting are automatically validated

The new datepicker item in APEX 4.0 also supports this type of automatic validation. Additional to the 'Value Required' setting (available for all items), the datepicker also allows setting 'Format Mask', 'Minimum Date', 'Maximum Date' and 'Year Range'. In doing so, APEX will again automatically validate the data received based on these settings and raise appropriate errors.

Settings for the new 'Date' item, settings highlighting are automatically validated

Plug-in items may also contain automatic validations, depending on whether the plug-in author has coded in this support.

You also have a couple of ways of customising the error message that is displayed by APEX when these validations fail, in terms of content and position. To override the default error messages, please refer to this section of the user guide. This details all of the 'Text Messages' that you would need to define in your applications to override the defaults. If you want to change where the error is displayed on screen, please see the 'Default Error Display Location' attribute available via 'Edit Application Properties' on the application homepage.

Finally, debug mode has also been enhanced to show when these validations are executed and whether they passed or failed.

Item based validations offer a more logical approach to validating data and requires fewer moving parts, which means less to define and less to maintain. Of course, for other more complex situations a separate validation will still be required, but this greatly simplifies some of the more common, simple scenarios.


Button-Centric Validation Exclusion
Again, historically in APEX if you don't want a validation to fire when certain buttons are pressed, you would define that logic in the validation. Let's take an example. If you have a typical 'Form' page used for inserting, updating and deleting data, you may want your validations to fire for insert and update, but not for delete. This would involve going through each validation and setting some condition such as where REQUEST != 'DELETE' or similar, to prevent the validation from firing. Now, in APEX 4.0, the actual button can be defined to either 'Execute Validations' or not.

Specify that pressing the button should not cause any validations to fire.

This is much easier, all you need to do is set this at button level and that's it, no item, plug-in or custom validations will fire. Wizard created forms will set this up for you automatically, so when creating these types of forms, the 'Create' and 'Save' buttons execute validations and the 'Delete' and 'Cancel' buttons do not.

You can also override this at validation level by setting the 'Always Execute' validation attribute to 'Yes' (defaults to 'No'). This could be useful for example if you want to always execute a security check, regardless of any button exclusions.

Debug mode has again been enhanced here to show if validations are prevented from firing because of the button setting.


Tabular Form Validations
APEX 4.0 now also supports declarative validation of tabular form data. Before APEX 4.0, there was no declarative support for validating this type of form and you would have to do a lot of manual PL/SQL to validate your data. Currently, tabular form validations only support a subset of what's available with page item validation, but do cater for some of the more common scenarios (required values, type checks and string comparisons). We are looking to extend this in a future release of APEX.


Error Message Label Placeholders
This is small but one of my favourites. When defining an error message that displays when a validation fails, if the validation is associated with a specific page item, you can now use the #LABEL# placeholder to dynamically reference the associated item's label.

Use #LABEL# instead of hard-coding the associated item label text.

So instead of having to duplicate the label text in the error message (and have to remember to change it if you change the item's label) as was historically the case, just use the #LABEL# placeholder and that's it. Again, less to define and less to maintain. An equivalent placeholder is also available for the new tabular form validations, #COLUMN_HEADER#.


Upgrading Applications
So finally, what about your existing APEX applications that have been long since built, where you want to take advantage of some of these new features. Well, take a look at the 'Upgrade Application' feature available via the 'Utilities' menu from the application homepage. This assists you in upgrading your application to use some of the new features in APEX 4.0.

Of particular relevance to validations are the following upgrade types:

• Update Text Field Item to Number Field Item, where appropriate - Locates where you have an unconditional 'Is Numeric' validations on 'Text Field' items and upgrades them to use the 'Number' item type with in-built numeric checking. Also removes the now redundant separate validation.
• Update Value Required item attribute to Yes, where appropriate - Locates where you have unconditional 'Not Null' validations on items and sets those item's 'Value Required' attribute to 'Yes'. Also removes the now redundant separate validation.
• Numeric, Required and Date Picker Item updates based upon conditional validations - Just locates where you have conditional validations for 'Is Numeric', 'Not Null' or 'Is Valid Date' on 'Text Field' items, for your manual review. So you can determine if the validation can be replaced with some item settings and button exclusions.

So quite a few little enhancements that hopefully add up to easier and more intuitive data validation. Good luck with your new APEX 4.0 style validations and let us know what you think!

Many thanks to Patrick Wolf for reviewing this post and filling in the gaps.