Ten Great Tips For 2010

| Dec 31, 2009
Ten Great Tips For 2010

1. Stay out of Trouble.

2. Aim for Greater Heights.

3. Stay Focused on your Job.

4. Exercise to Maintain Good Health.

5. Practice Team Work.

6. Rely on your Trusted Partner to Watch your Back. Take your Time Trusting Others.

7. Save for Rainy Days.

8. Rest and Relax.

9. Always Take Time to Smile.


10. Realize That Nothing is Impossible.

Wishing You and Your Family A Very Happy, Prosperous and Healthy New Year 2010!

50+ SQL Performance Optimization scenarios

Before the year ends I’d like to share some good stuff…

I have never seen a huge compilation of SQL tuning tips or rewrite scenarios (with test cases) and got them only on OracleFans forum… ooops… so you can’t read Chinese? try this translated version, whew.. good thing Google has this translate service that I am able to read in Chinese.. :)

So how did I end up on this forum? mm… I can’t really remember… I’m a curious wayward Googler, either I was having problems on a particular SQL on a client or I’m getting really curious about SQL (more...)

Three Recoveries, Two Refreshes, One Delete

I feel like it's been a month since my last entry. Oh yeah, that's because it has been. I continue to be amazed with how fast time flies the older I get. Kids are supposed to make you feel younger and as they grow up, I just feel older!

The title of this post describes a full day I had a few weeks ago with a vendor application. Another title could have been "Support That Cannot Be Reached is No Support At All." The application in question has a relatively small database, but a couple hundred users. We actually (more...)

ExcelDocumentType: Create Hyperlinks between Worksheets

This post  introduces a new feature in the ExcelDocTypeUtils PL/SQL package that allows a developer to create hyper linked cell data that points from one worksheet to another.   The feature is implemented by a  new function called createWorksheetLink.  The function takes two parameters: the cell data, and the name of the target worksheet.  The function is called from the dynamic SQL statement that is passed as part of a worksheet data object (ExcelDocTypeUtils.T_WORKSHEET_DATA).

The following code sample demonstrates how this feature can be used to generate a summary worksheet containing a hyper linked list (more...)

Oracle VPN

Virtual Private Networks have made remote working and access to critical corporate resources so much easier over the past few years. In fact, there is so much about the way we work today that is dependent on being able to get at data remotely yet in a secure manner. The (more...)

Happy Xmas to everybody !!!!!

| Dec 25, 2009

Holiday Pundemonium

There was a jolly man named St. Nick
Who didn't know which IDM stack to pick
By the yule log
He read our blog
That well-rounded cheeky man named St. Nick

Happy Holidays!

The death of Forms?

At the UKOUG conference in Birmingham, I gave a presentation entitled “Life After Forms” for people wondering what to do about their Forms applications. The reason that people consider this is of course that the talk in the Oracle community tend to concentrate on the two new options: ADF Faces (more...)

Application Express 4.0 EA Running on Amazon EC2

The Application Express 4.0 evaluation instance is running on an Amazon EC2 instance. You can access it at http://tryapexnow.com. You may also be interested in checking out the APEX 4.0 Web Services Evaluation Guide here.

PL/SQL Phases of the Moon

At work this past week, we kicked off a large Predictive Analytics and BI project.  The predictive analytics piece will use various factors and statistical models to try and pre-determine likely areas of criminal activity around our city.  Among those factors … the current and historical phase of the moon (based on date).  The Oracle RDBMS contains a plethora of date and calendar related functions, but determination of the moon’s phase is not among them.   Since I am the resident Oracle “guru”, the gauntlet was thrown down in front of me to come up with such (more...)

Index column ordering and performance

When we are talking about why oracle is not picking up index, one of the well known reason is cardinality. When data is uniform, an index is selective only when cardinality is not very low. When data is not uniform, we can have histograms but here we will keep that feature apart.

So lets assume we want to create a composite index on two columns which both are part of the filter condition. One column (ID) is having high cardinality and another (Status) is having very low cardinality. Then what should be the column sequence for that index? The obivious (more...)

No code changed, but now ORA-01841. Mistery Solved

A report that is coded in PL/SQL that has existed for years "all of sudden" started generating a "ORA-01841: (full) year must be between -4713 and +9999, and not be 0" error. Why and why all of sudden?

I discovered that a data problem has existed for years. When reports run they save results to a shared database table. Later on in the reports, the reports delete some of the data that does not meet a given time period. The table has both YEAR and MONTH columns which are both numeric along with a unique primary key value generated from (more...)

Some Tips on Oracle FMW 11g Clustering: deployment and configuration

In this blog-posting, I share some first tips on Oracle FMW 11g ( cluster-wide deployment and cluster configuration based on my first experiences with setting up such an environment.

Most tips come from these two documents:
- Enterprise Deployment Guide for Oracle SOA Suite
- Oracle FMW 11g High-Availability Guide

Cluster-wide deployment tips

Query and Transitive Closure

I have got some of the comments on my last blog - "Query Performance & Join Conditions" which were asking about the transitive closure. The example that I have given was not for transitive closure. Oracle has introduced transitive

closure when the literal values are present for joining conditions. For example when I have join condition on two tables and same column of one of the table is having filter condition then other table will use the same filter condition.

To demonstrate, we will see the following test case.

SQL> create table t1 as select rownum "N", rownum*30 "M" from (more...)

Export issue in 11g ( with R12 (12.1.1)

| Dec 15, 2009
I got one request to export entire R12 (12.1.1) database. I thought it going to be easiest task for me, but it turn to be most challenging for me. This is how proceed:
1. Create Directory in database:
create directory SUNEXP as '/sundeep/exp';
2. Grant read, write on SUNEXP to DBA role, as I want to start expdp as system.
grant read,write on directory EXPDIR to "DBA";
3. Ran expdp full export:
expdp system/password DIRECTORY=SUNEXP FULL=Y COMPRESSION=ALL DUMPFILE=exp_sundeep.dmp LOGFILE=exp_sundeep.log

I thought I am done and it will gona take its own sweet time. But it failed (more...)

OPP on Virtual Host

| Dec 9, 2009
In one of my earlier post I have mentioned how to use Virtual host for Concurrent manager. In this post I am specifically talking about issue you might encounter while using report output as xml and it doesn't show the actual output rather it shows html tags. So just understand the concept how OPP is being used in xml reports and then look for solution.

An Application user submits an XML Publisher based report.
The Standard Concurrent Manager process the request.
The XML data file is generated by the standard concurrent manager.
This can be done by various methods:
Oracle (more...)

Oracle Fusion Middleware 11g purge scripts available on Oracle Metalink

On Oracle Metalink there is now a Note available that describes the usage of the FMW 11g purge scripts. Note 815896.1 contains all the details.

An OPatch patch 8328187 is available for download that contains all the scripts that are required to create the purging functionality in the SOA_INFRA schema

How to set-up a common Ant project for using the Oracle FMW 11g B2B Ant tasks

Inspired by this great blog posting about how to use the FMW Ant tasks to deploy a SOA composite, I started to use that example to set up my own Ant project for B2B management purposes.

In this blog-posting, I will give an example project that is capable of importing and exporting CPAs to and from Oracle FMW 11g B2B.

Setup the build project
In JDeveloper, create an empty project and name it fmw-build for (more...)

Oracle Advanced Compression Advisor

Am currently working on how best to use compression techniques in an OLTP environment on a current 10g database which is due to be migrated to 11.2 in the next 6 months and I came across this OTN note


The following statement got me very interested

This package can be used on Oracle Databases running Oracle Database 9i Release 2 through 11g Release 1.
A compression advisor (DBMS_COMPRESSION) comes packaged with Oracle Database 11g Release 2.

So lets give it a try...

I downloaded the package and compiled it..

SQL> @dbmscomp.sql

Package created.

Synonym (more...)

Login problems R12

| Dec 4, 2009
On our R12 eBS environment we are facing a problem when loggin in. It does not happen all the time, because we use more then one web node (loadbalancer), but when trying to acces the login page the following error is shown..."Unable to generate forwarding URL. Exception: oracle.apps.fnd.cache.CacheException"or a blank page is shown instead of the login page. In the applications.log file ($