I'm Dumping TOAD, Helloooo Oracle SQL Developer!

It's already come around, out of nowhere the price of flowers and greeting cards increases ten fold and that special one starts leaving hints all around the house. Yes, Valentines day, loves is in the air although with a very fake and commercial smell to it. In keeping with the love theme I have decided to announce my separation from TOAD, I have found somebody new, a young new model, little ruff around the edges but with loads of possibilities and the best of all she's much cheaper (Not that kind of cheap...).

I have been looking to move over from TOAD to Oracle's SQL Developer (OSD) since it's initial incarnation as Raptor, but have always found some key feature missing from the product. Recently I have decided to attempt the crossover again, removing TOAD from easy desktop access and focusing on doing all work previously handled by TOAD through OSD. At the start I had to make frequent trips back to TOAD, unable to find certain functions and tools or just getting frustrated with the unfamiliar interface. But as I started using OSD more and more I discovered those familiar TOAD tools in there OSD disguise, and began to appreciate the added functionality provided by OSD. It's not been easy and at first getting the preferences right for your preferred setup may be a little tricky, but once familiar with the new interface and the location of those favourite tools and functions you'll forget about that first love pretty soon.

My Top tips for migrating from TOAD to Oracle SQL Developer

1. Intentionally or not, Oracle has placed allot of the functions and tools used in TOAD in the same navigation path in OSD, so if your stuck just remember how you accessed it in TOAD and 9 out of 10 you should have the same functionality available.
   
2. Save code for later use, highlight the code and right click, select Save Snippet. They can later be retrieved by using menu option View-> Snippets
3. Search database objects using menu option View -> Find DB Objects

Please feel free to contribute any tips you might have for making the transition a little easier, happy Valentines day.

OAF Key Do's and Don'ts (Part 2) - Performance Tuning: "Top 10" OA Framework Development Rules

1) ALWAYS use design time view objects (VOs) rather than dynamic VOs. Dynamic VOs have to be described by BC4J through an additional execute call for the VO prepared statement, and they potentially result in non shareable SQL due to different combinations.

2) ALWAYS set precision for all columns in the VO. This reduces the VO memory footprint. Without a set precision, for example, all String columns default to 4KB in size each.

3) AVOID calling VO.getRowCount to check for existence. getRowCount causes the entire VO row set to be fetched back to middle tier.

4) NEVER call VO.getEstimatedRowCount. getEstimatedRowCount executes select count(*) on the VO. This count is not guaranteed to stay the same, thus the method name.

5) ALWAYS call registerOutParameters with proper precision for callable statements. This reduces the callable statement memory footprint. Without a set precision, for example, all varchar columns default to 32KB.

6) ALWAYS use Oracle-style binding (:1, :2) in your SQL and DO NOT use JDBC style binding (?). This avoids parsing SQL at runtime to do String replacement.

7) AVOID coding generic VOs just to try to reuse them. The "one VO fits all"approach adds complexity to your SQL, increases number of attributes and VO memory footprint.

8) DO NOT execute searches by default nor allow blind queries.

9) Use PNG format not JPEG for BI graph beans.

10) Use JDeveloper to profile your code. JDeveloper 9i has memory, CPU and
event profilers.

OA Framework or ADF?

Check out Sara Woodhull's article on OA Framework and ADF.

OAF Key Do's and Don'ts (Part 1) - "Top 10" Golden Rules

I don't know how many of you have come across this in the OAF Devguide, it was only by change that I found it, thought I'd share:

There's a lot to learn when getting started with the OA Framework, but the following list of rules are so universal -- and so fundamental -- they should be familiar to anyone who's doing Framework development.

1) ALWAYS try to declaratively define your UI. Resort to a programmatic layout only if the UI cannot be implemented declaratively. Programmatic layouts are difficult to customize (they don't leverage the Personalization Framework) and may diverge from the UI Guidelines over
time.

2) NEVER change your UI layout properties in processFormRequest(). ALWAYS make changes in processRequest(), even if that means handling an event in processFormRequest() and then redirecting back to the same page. This ensures that the web bean hierarchy is in a stable state when the page renders.

3) NEVER use index numbers to find beans when you want to change their properties. ALWAYS search by name. Index numbers can change during processing.

4) NEVER change the properties of a parent bean from a child bean. This is a poor design practice that hampers reuse while introducing fragile code (particularly if the child code executes too late in the page rendering cycle to properly affect the parent).

5) NEVER instantiate Beans using "new OA*Bean()". ALWAYS use the createWebBean() factory methods available on the OAControllerImpl class. Not all Bean properties are initialized correctly when you use "new."

6) NEVER create Form Beans in code (this means NEVER add nested Form beans to a page; your Page Layout region should be the only form region). Multiple form Beans on a page are not supported and can result in strange runtime behaviors.

7) NEVER count on your Application Module using the same database connection in subsequent requests. For example, NEVER post and commit in separate requests. For performance reasons, the Framework will start pooling and reusing connections in 5.7 instead of holding onto a single connection throughout the life of an Application Module.

8) NEVER use JDBC directly unless you're calling a PL/SQL routine (you should use a view object instead, and if possible, the view object should be defined declaratively and not programmatically).

9) NEVER add member variables UNLESS THEY ARE TRANSIENT OR FINAL to view objects, Controllers, entity object, view rows and Application Modules.

10) ALWAYS adhere to the Self-Service Performance Guidelines

Call PL/SQL from OAF

In many situations developers need to execute PL/SQL code from there OAF application, be it legacy code or complex database manipulation better suited for PL/SQL.

The following example calls a PL/SQL procedure, passing in and receiving back parameter values. To ensure we athere to the MVC architecture, you must execute this code from your Application Module (AM). The method returns a HashMap object containing the two return values from the procedure, the HashMap can be returned to your controller, and used to raise a message for the user.

The following code is placed in your region or page controller, to call the method from your AM:

// Invoke the method
HashMap returnValues = (HashMap) am.invokeMethod(
"executePlsql");

// Get the returned values from the method
String status = (String) returnValues.get("Status");
String message = (String) returnValues.get("Message");

if (!status.equals("SUCCESS"))
{
am.invokeMethod("rollback");

// ERRORS from executePlsql
MessageToken[] tokens =
{
new MessageToken("STATUS", status),
new MessageToken("MESSAGE", message),
};

OAException errorMessage = new OAException("XX",
"XX_PLSQL_ERR", tokens,
OAException.ERROR, null);

pageContext.putDialogMessage(errorMessage);

The method to call your PL/SQL code, created in your page or regions AM:

import com.sun.java.util.collections.HashMap;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;

public HashMap executePlsql(String objectId, String objectSubType)
{
CallableStatement st = null;
HashMap returnValues = new HashMap(2);

try
{
String stmt = "BEGIN xx_pkg.xx_procedure( " +
"p_object_id => :1, " +
"p_object_sub_type => :2, " +
"p_status => :3, " +
"p_message => :4); END;";

OADBTransaction tr = getOADBTransaction();

st = tr.createCallableStatement(stmt, 1);

// Bind the input parameters
st.setInt(1, Integer.parseInt(objectId));
st.setString(2, objectSubType);

// Register return variables
st.registerOutParameter(3, Types.VARCHAR);
st.registerOutParameter(4, Types.VARCHAR);

st.execute();

// Assign returned values to variables
String status = st.getString(3);
String message = st.getString(4);

st.close();

// Populate HashMap with return variables
returnValues.put("Status", status);
returnValues.put("Message", message);

OAExceptionUtils.checkErrors(tr);
}
catch (SQLException sqle)
{
throw OAException.wrapperException(sqle);
}

return returnValues;
}

OAF White Paper

I recently presented a white paper at the 2006 South African Oracle User Group, an introduction to OAF development called: Oracle Applications Development Framework - The New Frontier

I hope this will help new comer's to get a good introduction to OAF development, extension and personalization. If there are any questions related to the white paper, please drop me a comment and I will reply a.s.a.p.

Please leave me a comment with your e-mail address and will get the document to you.