Feed aggregator

Using Database Reporting to Ensure Standards Compliance

Susan Duncan - Mon, 2009-12-14 05:30
In JDeveloper 11gR1 we introduced a SQL-like interface for reporting on your database models. Watch this demo to review the basic functionality. In the latest release we've added a set of pre-built reports. These reports not only get you started with reporting but also illustrate how you can use database reporting to ensure that the objects in your offline database model comply with your team's naming standards.

I say SQL-like interface because the database model you develop offline in JDeveloper is stored in XML. However, we took the decision that as most database developers are familiar with SQL it made perfect sense to provide a familiar way to interrogate the model. For instance, the pre-built report to list all tables with no Primary Key is:

T.SCHEMA '.' T.NAME "Table"

As you get more familiar with the meta-model you might write your own SQL queries, but there is also a declarative UI to help you traverse the model and develop the query you want.
Below is an image of the wizard, showing how the FROM clause for the above query is selected. You can also select the objects, apply different JOINs, add a WHERE, GROUP BY and aliases using the declarative UI.

Here is an example of a more complex SQL query - to report on any tables that do not have the audit column CREATION_DATE*


Now the world is your reporting oyster - how about creating a series of reports in a Standards Project - and distributing that project to every team that is doing database development? Here is one approach:
  1. Check the Standards Project out of source control into the required application
  2. In the Standards Project open the Project Properties, in Project Source Paths select the Offline Database node
  3. Add the projects containing your database model project(s) - see image below

  4. Open each report (double-click) and select the offline database you want to run against
  5. Go to the Publish tab and enter the details of how and where you want the report to be save. In the example below I've chosen to save the report in the project that contains the database - so my audit reports can be shown to represent my offline DB Model.
  6. Use the context menu of the report format to Run the report. In my example the HTML version of the report (AuditTableCheck) is now stored in Project1 along with the database, the report format (Check_Audit_Tables) is stored in the Standards project along with the other report formats.

Let me know how you get on - it would be good to build up a list of reports written by you that could be shared with others.

*For a full example of using this in a tutorial see this Oracle By Example

This blog was migrated to http://blogs.oracle.com/soabpm

Clemens Utschig - Sun, 2009-12-13 12:04
After a while of non blogging - I started again - this time, under the oracle flag - at blogs.oracle.com/soabpm.

Hope to see you there.

RAC, ASM and Linux Forum, December 15, 13:30 - 18:00 Beit HP Raanana

Alejandro Vargas - Sun, 2009-12-13 03:04

It's time for our 2nd, 2009 RAC, ASM and Linux Forum in Tel Aviv!

I would like to warmly invite you to our next RAC, ASM and Linux forum to be held at Beit HP in Raanana, on December 15.

You can register on the Israel Oracle User Group site.

On this forum we will have a 11g R2 Technology Update and 2 very interesting Customer Presentations about minimum downtime VLDB Migration to RAC on Linux and Creating and Managing RPM's for Oracle Silent Install on Linux.

Panel on Grid Infrastructure, ASM, Clusterware and RAC 11g R2, Technology Update

Annie Flint, Principal Member of Technical Staff, Oracle Corporation
Ofir Manor, Senior Sales Consultant, Oracle Israel
Alejandro Vargas, Principal Support Consultant, Oracle Advanced Customer Services

In the few months since last forum on June many things happened, 11g Release 2 is already production and brought a revolution in terms of performance and deep changes to the world of ASM, Oracle Clusterware and RAC.

Exadata Release 2 was released opening the way for OLTP databases based on the new Oracle - Sun Database Machine.

In this seminar we will review the new face of ASM, Oracle Clusterware and RAC on 11g Release 2 and we will comment on some of the incredible performance enhancements of the new version.

Migration of a VLDB to RAC 11g with Minimal Downtime

Dotan Mor,
Senior DBA
Pelephone DBA Team

Dotan will tell us the whole story of migrating an 8TB Datawarehouse, with near 0 downtime, from Linux 3 with OCFS2 to Linux 5, with CRS, RAC and ASM 11g, and Infiniband; and how his customer still cannot believe the incredible improvements they got in performance.

He will tell us also all the several problems faced in the way to this big success and how all of them were overcome.

Packaging Application and Database Together On Linux for Super-Silent-Installs

Liron Amitzi,
VP Professional Services

Liron will explain us how to build a Linux RPM that contains inside the whole set of files required to easily and fast deploy a complete application, from the database to last application executable.

See you there!

Best Regards,


Categories: DBA Blogs

Check IO Scripts

Alejandro Vargas - Wed, 2009-12-09 19:32

These scripts are very useful to check throughput.

The original version can be found on My Oracle Support Note 422414.1 by Luca Canali.

set lines 250 pages 50000

alter session set nls_date_format='dd-mm-yyyy hh24:mi';

col Phys_Read_Total_Bps for 999999999999
col Phys_Write_Total_Bps for 999999999999
col Redo_Bytes_per_sec for 999999999999
col Phys_Read_IOPS for 999999999999
col Phys_write_IOPS for 999999999999
col Phys_redo_IOPS for 999999999999
col OS_LOad for 999999999999
col DB_CPU_Usage_per_sec for 999999999999
col Host_CPU_util for 999999999999
col Network_bytes_per_sec for 999999999999
col Phys_IO_Tot_MBps for 999999999999
col Phys_IOPS_Tot for 999999999999

spool io_max_checkup.log

select min(begin_time), max(end_time),
sum(case metric_name when 'Physical Read Total Bytes Per Sec' then maxval end) Phys_Read_Tot_Bps,
sum(case metric_name when 'Physical Write Total Bytes Per Sec' then maxval end) Phys_Write_Tot_Bps,
sum(case metric_name when 'Redo Generated Per Sec' then maxval end) Redo_Bytes_per_sec,
sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then maxval end) Phys_Read_IOPS,
sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then maxval end) Phys_write_IOPS,
sum(case metric_name when 'Redo Writes Per Sec' then maxval end) Phys_redo_IOPS,
sum(case metric_name when 'Current OS Load' then maxval end) OS_LOad,
sum(case metric_name when 'CPU Usage Per Sec' then maxval end) DB_CPU_Usage_per_sec,
sum(case metric_name when 'Host CPU Utilization (%)' then maxval end) Host_CPU_util, --NOTE 100% = 1 loaded RAC node
sum(case metric_name when 'Network Traffic Volume Per Sec' then maxval end) Network_bytes_per_sec,
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;

spool off

spool io_maxtot_summary.log

select min(begin_time), max(end_time),
sum(case metric_name when 'Physical Read Total Bytes Per Sec' then maxval end)/1024/1024 +
sum(case metric_name when 'Physical Write Total Bytes Per Sec' then maxval end)/1024/1024 +
sum(case metric_name when 'Redo Generated Per Sec' then maxval end)/1024/1024 Phys_IO_Tot_MBps,
sum(case metric_name when 'Physical Read Total IO Requests Per Sec' then maxval end) +
sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then maxval end) +
sum(case metric_name when 'Redo Writes Per Sec' then maxval end) Phys_IOPS_Tot,
sum(case metric_name when 'Current OS Load' then maxval end) OS_LOad,
sum(case metric_name when 'CPU Usage Per Sec' then maxval end) DB_CPU_Usage_per_sec,
sum(case metric_name when 'Host CPU Utilization (%)' then maxval end) Host_CPU_util, --NOTE 100% = 1 loaded RAC node
sum(case metric_name when 'Network Traffic Volume Per Sec' then maxval end) Network_bytes_per_sec,
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;

spool off

Categories: DBA Blogs

Using OCFS2 as a Generic Cluster File System?

Sergio's Blog - Wed, 2009-12-09 00:56
If you use OCFS2 for purposes other than running Oracle software, please leave a comment or drop me a line: sergio[dot]leunissen[at]oracle[dot]com I'm curious how you use OCFS2, how big your filesystems are, how many nodes are in the cluster, what you like about OCFS2, etc.
Categories: DBA Blogs

Oracle Advanced Compression Advisor

Fairlie Rego - Sat, 2009-12-05 20:28
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 created.

Grant succeeded.

No errors.

SQL> @prvtcomp.plb

Package body created.

Synonym created.

Grant succeeded.

No errors.

SQL> set serveroutput on

SQL> exec dbms_comp_advisor.getratio('OWNER1','CROSS','OLTP',1);

Invalid Compression option for the current COMPAT setting

Thankfully the package body is not encrypted and a quick look at the code reveals that it does the following

SQL> create table DBMS_COMPRESSION_TEMP_UNCMP as select /*+ full('CROSS') */ * from cross sample block( 1);

Table created.

SQL> create table DBMS_COMPRESSION_TEMP_CMP compress for all operations as select * from DBMS_COMPRESSION_TEMP_UNCMP;
create table DBMS_COMPRESSION_TEMP_CMP compress for all operations as select * from DBMS_COMPRESSION_TEMP_UNCMP
ERROR at line 1:
ORA-00922: missing or invalid option

Hmmm... not sure how they expect "compress for all operations" to work in a pre 11g database...

How to run eBusiness Suite R12 using IE8

Brenden Anstey - Sat, 2009-12-05 02:40

IE8 is not supported for R12 as yet but it does work by disabling a security setting related to Cross Site Scripting (XSS). This solution works on Vista 64 using IE8 on Release 12.1

Here is how to do it:
Firstly we need to add the EBS application server to trusted sites within IE as we definitely do want the IE8 XSS filter active for general Internet browsing.
1. In IE8 Select Tools->Internet Options->Security(tab)->Trusted Sites
2. Click Sites and type in the hostname of the server running R12, untick Require HTTPS if needed and add the website to the list of trusted sites. Hit close.
3. Still in the Security tab click the custom level button and scroll right to the bottom and the third option from the bottom at the time of writing is 'Enable XSS Filter" set the option value to Disable.

Make sure you only disable the XSS filter for Trusted sites which should be a small list of intranet servers that you trust the content from.

Lifetime Social Value, How Sony Screwed the Pooch

Ken Pulverman - Fri, 2009-12-04 23:08
About a year after I started dating my girlfriend, who is now my Fiancee, she needed a new laptop. I helped her do the research and suggested a Sony laptop. Two months after it went out of warranty, the motherboard died. This was a well known issue at the time and was all over their website. Elaine called Sony, but they wouldn't budge and would only offer an unreasonable fee of over $600 to fix the problem. During this time, laptop prices had gone down and this fee made no economic sense.

I felt horrible about it. My family has been life long Sony bigots. Our first color TV was a Sony and we've had a strong preference for Sony products ever since including desktops, laptops, DVD players, stereos, Walkmen, clock radios, etc..

Not only had Elaine bought the laptop I recommended, she recommended Sony to several other people some of whom also bought.

Elaine resolved to never buy another Sony product ever again. .....This is where the story gets interesting from a marketing standpoint. Every time I suggest buying a new product that happens to be Sony, I get brow beaten to death. Elaine also tells anyone that will listen that she'll never buy a Sony product again. The result of all this Sony dogma is that I haven't bought another Sony product since Elaine's laptop failed two years ago.

It's a real shame for Sony too. We are both in the information technology industry and to say we are heavy consumers of technology would be an understatement. To give you some insight, Elaine is willingly accompanying me to CES this January for the second time since we've been going out, and both trips were personal and had nothing to do with our jobs. I believe we are the ideal consumers for an electronics company...early adopters who generally get asked by our friends, both male and female, what models of popular electronics they should buy.

We really aren't gamers (except for Elaine's new for an early Christmas present Nintendo Wii). However, tonight I told Elaine that I wanted to get a Sony PS3 because after comparison shopping, it looks like a very credible Blueray player that will be easily upgradable for some time to come. The first words out of Elaine's mouth were: "I am not paying for that." I would have never thought to ask her to contribute to my pursuit of 1080p (Blueray resolution), but she went on breathing Sony fire and damnation for several minutes more.

Effectively because Sony had no concept of how connected Elaine was to so many tech consumers, Sony screwed themselves by not fixing a known defect with Elaine's laptop. She can't help but badger me into not buying their products which compelled me to write this blog entry as an interesting marketing issue.

This story begins before the heavy use of the type of social media that will now see this story go from my marketing blog to my Twitter account and then on to my Facebook account automatically.

From a new marketing angle, this story made me think of two principles:

1. The Perpetual Profile - Businesses don't spend enough time profiling customers after they buy. We know we want them to buy again, so if they spend a few thousand dollars with us, shouldn't we figure out who they are? Elaine is a Technology Development VP, spent $2,000 on our laptop and lives with Ken who loves(ed) Sony. Perpetually profiling people who spend bigger dollars with us would be a necessary step to realize the theoretical lifetime value we all strive to achieve.

2. Poor Product Support Judgement Calls in the Social Era will Kill Brands - Effectively, some unimpowered support rep in the bowels of Sony effectively ruined my lifetime relationship and value for Sony and I am not even the one that had the direct issue.

Both these points speak to the need to get much smarter about how we deal with our customers and increasingly the web of people they influence and those who influence them. The good news is that for the products we like, we will give away this information freely. Everything Sony needed to know to alert them, perhaps electronically, that screwing up this issue would be a costly landmine for them was there for the taking.

Sony, I think you lost your opportunity to be our One and Only, but maybe there's still hope to get your act together and save a few more people like me.

The lesson for most companies is that your installed base matters. In fact they are the engine of your growth which is now amplified positively and negatively in the social era. Fail to identify or ignore those who have been your biggest or most loyal customers and deeply suffer the consequences. We used to say that those who had a bad experience told 10 others. This may be a more modern case, but I think it is the shape of things to come. So we are going to have to revise the phrase.

People who know people who've had a bad experience tell thousands of others. (via social media).

The good news, Sony, is that there is a short window to learn from your mistakes. The reverberating effect of Social Media is well documented, but its impact is really just revving up. Act fast and my second LCD TV may not be another Samsung.

Progress… YES?

V.J. Jain - Fri, 2009-12-04 14:45

After dealing with several challenges during the installation, I have successfully installed Oracle Apps on Oracle Enterprise Linux.  I’ve spent some time on the post-installation checks.

One of my specific challenges was that I already had the Oracle database installed previously.  This was causing some conflicts as the environment was not getting setup correctly.

Another issue that I faced was a problem with libdb.so.2.  This required creating a symbolic link as shown below:

root@localhost ~]# ls /usr/lib/libdb.so.2
ls: /usr/lib/libdb.so.2: No such file or directory
[root@localhost ~]# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
[root@localhost ~]# ls /usr/lib/libdb.so.2

The next issue I faced was to do with creating the Login Page.  I kept getting an error…

checking URL = http://localhost.localdomain:8000/OA_HTML/AppsLogin

RW-50016: Error: – {0} was not created:
File = {1}

I got around this by unlocking the Oracle accounts, running autoconfig, and bouncing the services.

Finally, I was able to see the login page and login as sysadmin.  I went to open the Applications by clicking on one of the forms and I faced another problem.  When opening the forms I received another error.

FRM-92101: There was a failure in the Forms Server during startup. This could happen due to invalid configuration
Please look into the web-server log file for details

Java Exception
oracle.forms.net.ConnectionException:Forms sessionfailed during startup:no response from runtime process
at oracle.forms.net.ConnectionException.createConnectionException(Unknown Source)
at oracle.forms.net.HTTPNStream.getResponse(Unknown source)
at oracle.forms.net.HTTPNStream.doFlush(Unknown Source)
at oracle.forms.net.HTTPNStream.flush(Unknown Source)
at java.io.DataOutputStream.flush(Unknown source)

I looked into the forms server log file which is given below

and found the following error

testMode: false
09/04/09 08:12:17 Oracle Containers for J2EE 10g ( initialized
09/04/09 08:14:27 ListenerServlet init()
09/04/09 08:14:28 Forms session aborted: runtime process failed during startup with
errors /u03/oracle/VIS/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries:
/usr/X11R6/lib/libXm.so.2: ELF file data encoding not little-endian

This required installing the openmotif package from Oracle’s download site http://oss.oracle.com/projects/compat-oracle/files/Enterprise_Linux/  The following package was missing: openmotif21-2.1.30-11.EL5.i3861.rpm

After trying again, a similar error appeared:

09/12/04 11:55:22.317 formsweb: Forms session <1> aborted: runtime process failed during startup with errors /u01/oracle/VIS/apps/tech_st/10.1.2/bin/frmweb: error while loading shared libraries: libXp.so.6: cannot open shared object file: No such file or directory

Metalink says the following about the libXP package…

The  i386 version of the libXp package is missing. The Oracle Universal Installer ( OUI  ) requires the file libXp.so.6 and this file is installed with the package libXp in RHEL 5 and OEL 5.

Issue the following to confirm the missing rpm:

% rpm -qa –queryformat “%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n” | grep libXp

The package libXp was not installed in RHEL 5/OEL 5 with default RPM packages, so it will need to be installed manually.   In RHEL 4 this file was provided by the package xorg-x11-deprecated-libs which is installed with default package installation but this is not the case for RHEL5/OEL5 and is now provided with a different rpm, the libXp rpm.

Install the package libXp by the command:

# rpm -ivh </path/to/>libXp.<version>.i386.rpm

Unfortunately, I did not have the DVD in front of me, so I found the RPM on http://rpm.pbone.net

[root@localhost tmp]# rpm -ivh libXp-1.0.0-8.1.el5.i386.rpm –nodeps
warning: libXp-1.0.0-8.1.el5.i386.rpm: Header V3 DSA signature: NOKEY, key ID a7048f8d
Preparing…                ########################################### [100%]
1:libXp                  ########################################### [100%]
[root@localhost tmp]# rpm -qa –queryformat “%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n” | grep libXp
libXp-1.0.0-8.1.el5 (i386)

After installing the libXp, my Apps Forms opened up at last.

At Last, I have my Vision instance up and running.

Login problems R12

Bas Klaassen - Fri, 2009-12-04 06:39
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 ($Bas Klaassenhttp://www.blogger.com/profile/04080547141637579116noreply@blogger.com5
Categories: APPS Blogs

Installing E-Business Suite 12.1.1 in RHEL5

V.J. Jain - Wed, 2009-12-02 11:29

The Adventure Begins

I started by purchasing a brand new enterprise server without an OS. My first goal was to install the latest release of Oracle Enterprise Linux for the operating system and then to install the latest release of Oracle Database (11.2). My next goal was to install the latest release of Oracle E-Business Suite for Linux x86-64 (12.1.1).

A few months ago I installed Oracle Enterprise Linux (Red Hat Enterprise Linux 5.4) on the brand new server. This was relatively easy and being comfortable with Unix probably helped. My next step was to install the latest Oracle Database which was (and still is) 11.2. This step was slightly more difficult as being “comfortable with Unix” does not translate to “proficiency with Unix”. After dealing with a few issues (dependencies top the list), I successfully installed and configured my 11.2 instance.

In my opinion, the performance of Oracle 11.2 on RHEL5 (OEL) is extremely good. I have instances on Windows, HP-UX, and Solaris; RHEL is the fastest and most stable. Obviously, my experience is limited to the features that I use and transactions that I perform.

The Adventure Continues

I’m finally getting around to installing Oracle E-Business Suite 12.1.1 on Oracle Enterprise Linux 5.4 (RHEL 5). This is being built on an Oracle 11.2 Database and so far things are going smoothly. I’m kind of surprised with how smooth the installation has been so far.

The only bump that I ran into was because I didn’t have Xming program installed. I was trying to get the X11 port forwarding (in Putty) to work for the GUI needed for the Oracle Rapid Install since I didn’t feel like hooking up the monitor directly to the server. It worked like a charm once I installed Xming.

I’m looking forward to having a fully functional R12 Vision instance in this hardware/OS/database configuration. From my experience with the 11.2 Database in RHEL5 so far, I have a feeling the performance will be excellent.

More to come on this soon…

My Background (Are you Technical or Functional, Apps or DB?)

I am an Oracle Database Consultant and an Oracle Applications Consultant. My area of expertise for most on-site projects that I personally work on is a Functional role for Oracle E-Business Suite (Oracle Applications) with a focus on Financials and Distribution. However, I work on remote projects that are not related to the E-Business Suite, such as database design, development, XML DB and ETL, performance tuning, etc. I have written many database-related articles and even been a co-author of an Oracle 11g book. As a result, my skill-set ranges from pure database to ERP Functional. Till recently, I had not developed my skills with system administration or Applications DBA. The goal of this in-house project was to expand my knowledge of these two roles.

Why I think this is Valuable

As a Functional Consultant, I have the responsibility of translating business requirements from my clients into solutions. When designing solutions to business requirements, it is important that the person who architects the solution understands the available components and tiers to the solution. For example, if a client had a requirement to manage a large amount of XML data, one solution might be (very high level) to load the data into Oracle. I can think of hundreds of other possible solutions based on many different criterion. For each business requirement, there are usually many sub-optimal solutions. Ultimately, the ability to design an optimal solution depends greatly on the awareness of the solution architect. An increased “awareness” increases the number of potential solutions for any requirement and I think that without exception, this leads to the optimal solution. Generally, this will save time and money while outperforming the other potential solutions.

About the author

V.J. Jain is an Oracle Database Consultant and an Oracle Applications Consultant. He is the owner is an Orange County based Oracle Consulting Firm, Varun Jain, Inc.. When personally performing work on-site, he works as a Consultant for Oracle E-Business Suite (Oracle Applications) with a focus on Financials and Distribution. With over 13 years of experience with database systems, he also specializes in database performance, custom database development, XMLDB/ETL, Oracle interfaces, and high performance solutions. Additional material by him can be found at http://www.oracle-developer.com. Also, Connect with V.J. Jain on LinkedIn

Lessons in Loose Coupling - Special Delivery

Gareth Roberts - Tue, 2009-12-01 16:15

I'm generally nervous when I hear people that deal with software throw around the phrase "loosely coupled" with exhuberance. For me, loose coupling is one of the few phrases that resonates a feeling of ambivalence. An abstraction layer is a necessity, especially in proprietory software, to give you the coding hooks you need. Just be careful though to avoid Special Deliveries of Coal. Classic!

Catch ya!
This is a post from Gareth's blog at http://garethroberts.blogspot.com
References Related Posts

RAC install root.sh fail on 2nd Node with error: "Timed out waiting for the CRS stack to start"

Alejandro Vargas - Mon, 2009-11-30 20:51

Yesterday we spent the day trial to solve this problem.

The actual solution, once the problem was found, took a couple of minutes to solve.

Usually this issue will arise when there is a network misconfiguration. We did check that carefully and everything was well configured:

- Same device names at both ends
- No dropped packets or transmission errors
- Both sides configured full duplex

Then we decided to move the install software to the second node and used scp over the interconnect:

scp -r -priv:

scp progressed for a while and then become stalled.

Retrying using the public network worked without any problem.

Any other test using the interconnect got stalled after a while.

The network people checked what the problem can be and found that the switch was configured half duplex.

They corrected the problem and everything worked fine.

Oracle Support Note 745215.1 describe the problem and other areas to check as well.

Categories: DBA Blogs


Freek D’Hooge - Sun, 2009-11-29 16:23

0x1A, better know as the end of file character.
Now also known as the cause of me waisting several hours on analyzing a 500MB raw trace file trying to figure out why the tkprof report did not seemed to be correct.

Still wondering why the application I was tracing had an EOF character in the value of a varchar2 type bind variable.

Categories: DBA Blogs

The £10 UKOUG Weak Joke Challenge

Oracle WTF - Sun, 2009-11-29 07:44

Oracle-WTF will pay the sum of £10 to the first person who makes the following weak Brummie joke to a conference audience at UKOUG:

Are there any Brummies here today?

Is it true that Ozzy Osbourne thought the Spice Girls were astronauts?

(Note for visitors to England: it's about the accent. And The Spice Girls used to be a pop group. And Ozzy Osbourne, oh never mind.)

Oracle PL/SQL unit testing with Ruby

Raimonds Simanovskis - Thu, 2009-11-26 16:00
Current PL/SQL unit testing options

Unit testing and TDD (test driven development) practices are nowadays one of the key software development practices. It is especially important if you are doing agile software development in small iterations where you need to automate unit testing as much as possible, as you cannot do manual regression testing of all existing and new functionality at the end of each iteration.

In some languages (like Java, Ruby, Python, C# etc.) there is quite good tools and frameworks support for unit testing and as a result there is quite high testing culture among top developers in these communities. But unfortunately in PL/SQL community so far automated unit testing is not used very often. During recent Oracle OpenWorld conference in presentations about unit testing when it was asked who is doing automated unit testing then only few hands were raised.

Why is it so? And what are current options for doing automated PL/SQL unit testing?

The first unit testing framework for PL/SQL was utPLSQL which was created by Steven Feuerstein and based on API defined by many other xUnit style frameworks (like e.g. JUnit). But the issue with this approach was that PL/SQL syntax for tests was quite verbose and tests were not very readable (see example). As a result Steven stopped developing further utPLSQL and currently there are no other active maintainers of this project. There are some other alternative frameworks which tried to simplify writing tests in PL/SQL (OUnit, pl/unit, PLUTO etc.) but none of them are very actively used and maintained by PL/SQL community.

Because of the issues with utPLSQL Steven Feuerstein started development of graphical interface tool for PL/SQL unit testing which is now Quest Code Tester for Oracle. This tool is actively developed and maintained by Quest Software but there are several issues with it:

  • It is a commercial tool and as a result it will not become widely accepted by all PL/SQL developers. There is also a freeware edition of it but the functionality of it is very limited.
  • It is a graphical tool – it can help you with quick creation of simple tests but when you will need more complex logic you might get stuck that you cannot do it (or you need to do it again in plain PL/SQL and have the same issues as in utPLSQL).
  • It stores tests in database repository – and it means that it might be hard to maintain unit tests in version control system like Subversion or Git.

And finally also Oracle started to do something in PL/SQL unit testing area and there is unit testing support in latest SQL Developer version 2.1 which currently still is in early adopter status. SQL Developer has very similar approach to Quest Code Tester – it is graphical tool which stores tests and test results in repository. So the benefit of SQL Developer over Quest Code Tester is that it is free :) But compared to Quest Code Tester it still has less features (e.g. currently not all complex data types are supported) and still is not released as final version and still has bugs.

Ruby as testing tool for PL/SQL

As you probably know I am quite big Ruby fan and always exploring new ways how to use Ruby to increase my productivity. And Ruby community has very high testing culture and has many good tools for testing support (I like and use RSpec testing framework). Therefore some time ago I started to use Ruby and RSpec also for testing PL/SQL code in our projects where we use Ruby on Rails on top of Oracle databases with existing PL/SQL business logic.

I have created ruby-plsql library which provides very easy API for calling PL/SQL procedures from Ruby and recent ruby-plsql version supports majority of PL/SQL data types.

So let’s start with with simple example how to use Ruby, RSpec and ruby-plsql to create PL/SQL procedure unit test. I will use BETWNSTR procedure example from utPLSQL examples:

   string_in   IN   VARCHAR2,
   start_in    IN   INTEGER,
   end_in      IN   INTEGER
   l_start PLS_INTEGER := start_in;
   IF l_start = 0
      l_start := 1;
   END IF;
   RETURN (SUBSTR (string_in, l_start, end_in - l_start + 1));

I took example tests from utPLSQL and wrote them in Ruby and RSpec:

describe "Between string" do
  it "should be correct in normal case" do
    plsql.betwnstr('abcdefg', 2, 5).should == 'bcde'
  it "should be correct with zero start value" do
    plsql.betwnstr('abcdefg', 0, 5).should == 'abcde'
  it "should be correct with way big end value" do
    plsql.betwnstr('abcdefg', 5, 500).should == 'efg'
  it "should be correct with NULL string" do
    plsql.betwnstr(nil, 5, 500).should be_nil

As you can see the tests are much shorter than in utPLSQL and are much more readable (also more readable than utPLSQL template which can be used to generate utPLSQL tests). And also you can create these tests faster than using GUI tools like Quest Code Tester or SQL Developer.

More complex example

Second more complex example I took from SQL Developer unit testing tutorial. We will create tests for PL/SQL procedure AWARD_BONUS:

 PROCEDURE award_bonus (
  emp_id NUMBER, sales_amt NUMBER) AS
  commission    REAL;
  comm_missing  EXCEPTION;
  SELECT commission_pct INTO commission
    FROM employees2
      WHERE employee_id = emp_id;
  IF commission IS NULL THEN
    RAISE comm_missing;
    UPDATE employees2
      SET salary = NVL(salary,0) + sales_amt*commission
        WHERE employee_id = emp_id;
END award_bonus;

I didn’t quite like the testing approach in SQL Developer unit testing tutorial – it was assuming that there is already specific data in employees2 table and was testing procedure using specific primary key values. As a result tests are not very readable as you cannot see all input data in the test case and tests could easily broke if initial data in table are different.

Therefore I created tests in Ruby using better approach that each test creates all necessary data that are needed for it and at the end of test there are no side effects which can influence other tests:

describe "Award bonus" do
  include CustomerFactory
  [ [1000,  1234.55,  0.10,   1123.46],
    [nil,   1234.56,  0.10,   123.46],
    [1000,  1234.54,  0.10,   1123.45]
  ].each do |salary, sales_amt, commission_pct, result|
    it "should calculate base salary #{salary.inspect} + sales amount #{sales_amt} * " +
                  "commission percentage #{commission_pct} = salary #{result.inspect}" do
      employee = create_employee(
        :commission_pct => commission_pct,
        :salary => salary
      plsql.award_bonus(employee[:employee_id], sales_amt)
      get_employee(employee[:employee_id])[:salary].should == result

I am generating three different tests with three different sets of input values. When you run these tests you see result:

Award bonus
- should calculate base salary 1000 + sales amount 1234.55 * commission percentage 0.1 = salary 1123.46
- should calculate base salary NULL + sales amount 1234.56 * commission percentage 0.1 = salary 123.46
- should calculate base salary 1000 + sales amount 1234.54 * commission percentage 0.1 = salary 1123.45

In addition I am using factory pattern (create_customer method) for test data creation. When using factory pattern you create test data creation method which will create valid new record with default field values. If in your test you need some specific non-default values then you can pass just these values as parameters to factory method. Factory pattern also helps in the maintenance of tests. For example, if new mandatory columns will be added to employees table then it will be necessary to add new fields with default values in factory methods and nothing should be changed in individual tests.

Here is example of employee factory implementation:

module EmployeeFactory
  # Creates new employee with valid field values.
  # Pass in parameters only field values that you want to override.
  def create_employee(params)
    employee = {
      :employee_id => plsql.employees2_seq.nextval,
      :last_name => 'Last',
      :email => 'last@example.com',
      :hire_date => Date.today,
      :job_id => plsql.jobs.first[:job_id],
      :commission_pct => nil,
      :salary => nil
    plsql.employees2.insert employee
    get_employee employee[:employee_id]
  # Select employee by primary key
  def get_employee(employee_id)
    plsql.employees2.first :employee_id => employee_id

And here is additional test for testing if procedure will raise exception if one input value is missing:

  it "should raise ORA-06510 exception if commission percentage is missing" do
    salary, sales_amt, commission_pct = 1000,  1234.55,  nil
    employee = create_employee(
      :commission_pct => commission_pct,
      :salary => salary
    lambda do
      plsql.award_bonus(employee[:employee_id], sales_amt)
    end.should raise_error(/ORA-06510/)
How to use it

I hope that if you are looking for PL/SQL unit testing tool then you will try this out :) You can get examples from this article together with necessary setup code and installation instructions at http://github.com/rsim/ruby-plsql-spec.

If you have any feedback or questions or feature suggestions then please comment.

Categories: Development

Vive la diference!

Nuno Souto - Wed, 2009-11-25 02:59
And I'm not talking about the one between Mars and Venus!Some of the regular readers will no doubt recall my comments regarding the MOS introduction.Yes, dang right they were strong words! We pay Oracle YEARLY in excess of 6 figures in maintenance fees.A large chunk of which is for our access to Metalink/MOS/whatever.The last thing I need when that is unusable for a long period is some idiot Noonsnoreply@blogger.com3

More Oracle data types supported by ruby-plsql gem

Raimonds Simanovskis - Tue, 2009-11-24 16:00

I have just released ruby-plsql gem version 0.4.0 which provides many new features. You can read about initial versions of ruby-plsql in previous blog posts.

Oracle complex data type support

Initial versions of ruby-plsql supported just simple Oracle types like NUMBER, VARCHAR2, DATE, TIMESTAMP, CLOB, BLOB as PL/SQL procedure parameters. Now support for many more complex data types is added. See examples below how to call PL/SQL procedures with these complex data types.

PL/SQL Record

Let’s assume you have PL/SQL procedure with PL/SQL record type parameter (which most typically will be in table%ROWTYPE format):

CREATE TABLE test_employees (
          employee_id   NUMBER(15),
          first_name    VARCHAR2(50),
          last_name     VARCHAR2(50),
          hire_date     DATE
CREATE OR REPLACE FUNCTION test_full_name (p_employee test_employees%ROWTYPE)
  RETURN p_employee.first_name || ' ' || p_employee.last_name;

Then you can create Ruby Hash with record field values (specifying field names as Symbols), e.g.:

p_employee = {
  :employee_id => 1,
  :first_name => 'First',
  :last_name => 'Last',
  :hire_date => Time.local(2000,01,31)

and pass this Hash as a parameter which will be translated to PL/SQL record parameter by ruby-plsql:

plsql.test_full_name(p_employee) #=> "First Last"
# or
plsql.test_full_name(:p_employee => p_employee) #=> "First Last"

In the same way you can get PL/SQL function return values or output parameter values as Hash values.

Object type

In similar way also object type parameters can be passed as Hash values. In this case also nested objects or nested collections of objects are supported:

  street    VARCHAR2(50),
  city      VARCHAR2(50),
  country   VARCHAR2(50)
  type            VARCHAR2(10),
  phone_number    VARCHAR2(50)
  employee_id   NUMBER(15),
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  hire_date     DATE,
  address       t_address,
  phones        t_phones
CREATE OR REPLACE FUNCTION test_full_name (p_employee t_employee)
  RETURN p_employee.first_name || ' ' || p_employee.last_name;

and from Ruby side you can call this PL/SQL function as:

p_employee = {
  :employee_id => 1,
  :first_name => 'First',
  :last_name => 'Last',
  :hire_date => Time.local(2000,01,31),
  :address => {:street => 'Main street 1', :city => 'Riga', :country => 'Latvia'},
  :phones => [{:type => 'mobile', :phone_number => '123456'}, {:type => 'home', :phone_number => '654321'}]
plsql.test_full_name(p_employee) #=> "First Last"
# or
plsql.test_full_name(:p_employee => p_employee) #=> "First Last"

And also object type return values and output parameters will be returned as Ruby Hash values (with nested Hashes or Arrays if necessary).

There is one limitation that these object types should be defined as database types and not just inside PL/SQL package definition. Unfortunately you cannot access type definitions inside packages from OCI or JDBC drivers and as a result cannot call such procedures from outside of PL/SQL.

TABLE and VARRAY collections

TABLE and VARRAY collection parameters can be passed as Array values:

CREATE OR REPLACE FUNCTION test_sum (p_numbers IN t_numbers)
  l_sum   NUMBER(15) := 0;
  IF p_numbers.COUNT > 0 THEN
    FOR i IN p_numbers.FIRST..p_numbers.LAST LOOP
      IF p_numbers.EXISTS(i) THEN
        l_sum := l_sum + p_numbers(i);
      END IF;
    RETURN l_sum;

And from Ruby side:

plsql.test_sum([1,2,3,4]) #=> 10

You can get also cursor return values from PL/SQL procedures:

  l_cursor  SYS_REFCURSOR;
  OPEN l_cursor FOR
  SELECT * FROM test_employees ORDER BY employee_id;
  RETURN l_cursor;

can be called from Ruby in the following way:

plsql.test_cursor do |cursor|
  cursor.fetch #=> first row from test_employees will be returned

It is important to pass block parameter in this case and do something with returned cursor within this block as after ruby-plsql finishes PL/SQL procedure call it will close all open cursors and therefore it will not be possible to do anything with returned cursor outside this block.

It is also possible to use returned cursor as input parameter for another PL/SQL procedure:

  RETURN test_employees%ROWTYPE
  l_record  test_employees%ROWTYPE;
  FETCH p_cursor INTO l_record;
  RETURN l_record;

which can be called from Ruby

plsql.test_cursor do |cursor|
  plsql.test_cursor_fetch(cursor) #=> first record as Hash

Note: you can pass cursors as PL/SQL procedure input parameter just when using ruby-plsql on MRI 1.8/1.9 with ruby-oci8, unfortunately I have not found a way how to pass cursor as input parameter when using JRuby and JDBC.


And finally you can use also PL/SQL BOOLEAN type – it is quite tricky data type as it is supported just by PL/SQL but not supported as data type in Oracle tables. But now you can also use it with ruby-plsql:

  ( p_boolean BOOLEAN )
  RETURN p_boolean;
plsql.test_boolean(true) #=> true

You can find more PL/SQL procedure call usage examples in ruby-plsql RSpec tests.

Table and sequence operations

I have been using and promoting to others ruby-plsql as PL/SQL procedure unit testing tool. As current PL/SQL unit testing tools are not so advanced and easy to use as Ruby unit testing tools then I like better to use Ruby testing tools (like RSpec) together with ruby-plsql to write short and easy to understand PL/SQL unit tests.

In unit tests in setup and teardown methods you typically need some easy way how to create some sample data in necessary tables as well as to validate resulting data in tables after test execution.

If you are Ruby on Rails developer then you probably will use ActiveRecord (or DataMapper) for manipulation of table data. But if Ruby is used just for unit tests then probably ActiveRecord would be too complicated for this task.

Therefore I added some basic table operations to ruby-plsql which might be useful e.g. in unit tests. Some syntax ideas for these table operations are coming from Sequel Ruby library.

# insert one record
employee = { :employee_id => 1, :first_name => 'First', :last_name => 'Last', :hire_date => Time.local(2000,01,31) }
plsql.employees.insert employee # INSERT INTO employees VALUES (1, 'First', 'Last', ...)

# insert many records 
employees = [employee1, employee2, ... ]  # array of many Hashes
plsql.employees.insert employees

If primary key values should be selected from sequence then you can get next sequence values with

plsql.employees_seq.nextval # SELECT employees_seq.NEXTVAL FROM dual
plsql.employees_seq.currval # SELECT employees_seq.CURRVAL FROM dual
# select one record
plsql.employees.first # SELECT * FROM employees
                      # fetch first row => {:employee_id => ..., :first_name => '...', ...}
plsql.employees.first(:employee_id => 1)  # SELECT * FROM employees WHERE employee_id = 1
plsql.employees.first("WHERE employee_id = 1")
plsql.employees.first("WHERE employee_id = :employee_id", 1)

# select many records
plsql.employees.all                       # => [{...}, {...}, ...]
plsql.employees.all(:order_by => :employee_id)
plsql.employees.all("WHERE employee_id > :employee_id", 5)

# count records
plsql.employees.count                     # SELECT COUNT(*) FROM employees
plsql.employees.count("WHERE employee_id > :employee_id", 5)
# update records
plsql.employees.update(:first_name => 'Second', :where => {:employee_id => 1})
                      # UPDATE employees SET first_name = 'Second' WHERE employee_id = 1
# delete records
plsql.employees.delete(:employee_id => 1) # DELETE FROM employees WHERE employee_id = 1
Other SQL statements

Any other SELECT statement can be executed with

plsql.select :first, "SELECT ..."
# or
plsql.select :all, "SELECT ..."

or any other non-SELECT SQL statement can be executed with

plsql.execute "..."

And also COMMIT or ROLLBACK could be executed simply with


I plan to write a separate blog post about how I recommend to create PL/SQL unit tests using Ruby and ruby-plsql and RSpec.


As always you can install latest version of ruby-plsql with

gem install ruby-plsql

Latest gem version is just on Gemcutter but now it should be available as default gem source for all Ruby installations.

And as always ruby-plsql is supported both on

  • Ruby 1.8.6/1.8.7 or Ruby 1.9.1 with ruby-oci8 gem version 2.0.3 or later (some specific issues with complex data types will be fixed in later versions of ruby-oci8)
  • JRuby 1.3/1.4 with Oracle JDBC driver (testing mainly with ojdbc14.jar but also ojdbc5.jar or ojdbc6.jar should be fine)

Please try it out and tell me if there are any issues with some particular data types or if there are still some unsupported PL/SQL data types that you would like to be supported in ruby-plsql. And also I encourage you to try ruby-plsql out for PL/SQL unit testing if you had no PL/SQL unit tests previously :)

Categories: Development

Production Upgrade complete... hack obsolete!

Gareth Roberts - Sun, 2009-11-22 18:14

It had to happen. I've moved away from very retro hardware requirements and a couple of hacks to something much simpler, and more applicable to "modern" computers ie. those with USB ;-)

No. 8 wire solution no longer needed ... luckily the wires aren't that thick :-)



Bonus points to Readers that guess the application of this stuff!

Catch ya!
This is a post from Gareth's blog at http://garethroberts.blogspot.com
References Related Posts

Collect enhancements in 11g release 2

Adrian Billington - Sun, 2009-11-22 02:00
Aggregate unique and ordered collection elements with the COLLECT function in 11g. November 2009


Subscribe to Oracle FAQ aggregator