Feed aggregator

Bashing RDBMS

Robert Vollman - Wed, 2008-04-23 17:39
Please review the following interesting article by Jonathan Holland.Why Relational Databases end up being the bottleneckFor the appropriate context of this debate, you can review this forum discussion on Joel's web site as just one example.Stored Proc to avoid frequent buildsAs is evident from the title and content of my blog, I'm a database professional. Nevertheless, I try to stay as objectiveRobert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com8

How to setup Ruby and new Oracle Instant Client on Leopard

Raimonds Simanovskis - Wed, 2008-04-23 16:00

UPDATE: New version of instructions for Snow Leopard is available here.


We waited for it long, long time and finally it has arrived – Oracle Instant Client for Intel Mac. I was lucky to test beta version of the client already for the last couple of weeks and so far everything was working OK. Therefore as the final version is now available to everybody I am here rewriting my previous instructions on how to get Ruby working with Oracle on Mac.

Installing Oracle Instant Client for Intel Mac

At first you need to download Oracle Instant Client for Intel Mac. Download “Instant Client Package – Basic” and “Instant Client Package – SDK” and also I suggest “Instant Client Package – SQL*Plus” if you would like to have command line sqlplus utility.

Unzip downloaded archives and move it where you would like to have it – I am keeping it in /usr/local/oracle/instantclient_10_2. Then go to this directory and make symbolic links for dynamic libraries

sudo ln -s libclntsh.dylib.10.1 libclntsh.dylib
sudo ln -s libocci.dylib.10.1 libocci.dylib

Then I recommend to create and place somewhere your tnsnames.ora file where you will keep your database connections definitions – I place this file in directory /usr/local/oracle/network/admin.

Then finally you need to set up necessary environment variables – I place the following definitions in my .bash_profile script:

export DYLD_LIBRARY_PATH="/usr/local/oracle/instantclient_10_2"
export SQLPATH="/usr/local/oracle/instantclient_10_2"
export TNS_ADMIN="/usr/local/oracle/network/admin"

Use your path to Oracle Instant Client if it differc from /usr/local/oracle/instantclient_10_2. And as you see I also define NLS_LANG environment variable – this is necessary if your database is not in UTF8 encoding but in Ruby you want to get UTF-8 encoded strings from the database. Specifying this NLS_LANG environment variable you will force that Oracle Instant Client will do character set translation.

After these steps relaunch Terminal application (so that new environment variables are set), specify database connection in tnsnames.ora file and try if you can access your database with sqlplus from command line.

Ruby installation

If you are using Leopard then I assume that you are using preinstalled Ruby which is the simplest option. I tried to compile Ruby from sources on Mac OS X Leopard but when I compared performance then original Ruby was a little bit faster on some benchmarks and therefore I sticked with original one.

Compile and install ruby-oci8

Download the latest version of ruby-oci8 (version 1.0.0 at time of writing this post).

As Oracle Instant Client is available just for Intel i386 architecture you need to change in file /usr/lib/ruby/1.8/universal-darwin9.0/rbconfig.rb line 17 to:

'-arch i386'

Then go to directory where you extracted ruby-oci8 source and execute ruby-oci8 standard installation sequence:

ruby setup.rb config
sudo make install

After that you can change back file /usr/lib/ruby/1.8/universal-darwin9.0/rbconfig.rb line 17 to:

'-arch ppc -arch i386'

Now try

ruby -r oci8 -e "OCI8.new('scott', 'tiger','orcl').exec('select * from emp') do |r| puts r.join(','); end"

or similar to verify that you can access Oracle database from ruby.

That’s it! Please write in comments if something is not working according to these instructions.

Categories: Development

ALM - help me help you!

Susan Duncan - Tue, 2008-04-22 04:34
Some of the areas that I've always been interested in - as a consultant, a teacher, a curriculum developer or as a Product Manager - are standards, methodology and development process: From Designer to JDEV, from CDM to Agile, from ClearCase to SVN, from IE to UML and so much more. Now we call that ALM - Application Lifecycle Management - the management of the lifecycle development practices (requirements, build, test, change control, defect mgmt etc) fused together through application of process, reporting, traceability and collaboration.

Here in JDeveloper land we are always looking at ways to improve both our tooling and the developer experience so I've put together a survey to find out what ALM tools you are using today. We're looking into adding better integration with ALM tools and the results of the survey will ensure that we move in the right direction.

So please, take a couple of minutes to complete this quick online survey and help me help you!

Things I believe in

Robert Baillie - Tue, 2008-04-22 03:17
It's easier to re-build a system from its tests than to re-build the tests from their system. You can measure code complexity, adherence to standards and test coverage; you can't measure quality of design. Formal and flexible are not mutually exclusive. The tests should pass, first time, every time (unless you're changing them or the code). Flexing your Right BICEP is a sure-fire way to quality tests. Test code is production code and it deserves the same level of care. Prototypes should always be thrown away. Documentation is good, self documenting code is better, code that doesn't need documentation is best. If you're getting bogged down in the process then the process is wrong. Agility without structure is just hacking. Pairing allows good practices to spread. Pairing allows bad practices to spread. Cycling the pairs every day is hard work. Team leaders should be inside the team, not outside it. Project Managers are there to facilitate the practice of developing software, not to...

Tab Order of Elements on an APEX Page

Duncan Mein - Mon, 2008-04-21 05:45
To set the TAB order of your form, you simply need to add the attribute: TABINDEX="1" to the "HTML Form Element Attributes" of your items replacing the number with the desired sequence.

This is a great way of enhancing the usability of your forms.

Business case - Customer on Credit Hold

Krishanu Bose - Sun, 2008-04-20 12:23

Business Scenario:
There is a requirement to put all open orders in Order Management on Hold by putting a Credit Hold on customer under the following two conditions:
1. Customer exceeds the Credit limit set
2. Cutomer has not paid an invoice even after 'x' days of the invoice due date on invoice. However, this customer may not have exceeded the credit limit.

The solution to the first scenario is to enable the Credit Check for the customer and put an amount in the Credit Limit field for the customer. Oracle Order Management would use this information to place an order on hold.
However, for the second scenario there is no standard solution. We have to create custom program to put the customer on a 'Credit Hold'.
Step1: Enable a DFF (we can name it as say 'Over Due Days') for the customer which would hold the maximum number of days the payment is overdue that is accecptable to the business.
Step2: Write a small program that would check daily for all customers where the system date is greater than the due date by the number of days specified in the DFF. A report would also be an output of the program which is something very similar to the standard report "Past Due Invoice Report" with a couple of additional fields to capture the days beyond the maximum number of days after due date as per DFF. This program would put all customer on a Credit Hold where the invoice Due date is greater than 'Due Date + No of days in DFF'.
Ensure that the program is scheduled to run just after midnight to so that the program does not miss out on any new invoices created.

Oracle CPUApr2008 for DB: patch set and first install

Pawel Barut - Sun, 2008-04-20 08:40
Written by Paweł Barut
To my surprise CPUApr2008 is not available for latest patch-set of Oracle 10gR2 ( that was released in first quarter 2008. In note that goes together with patch 6810189:
The following are the updated components of this patch set:
  * Up to January 2008 CPU is included in this patch set
Based on this I was expecting that CPUApr2008 will be also available for patch-set. In note 552248.1 you will find then:

1.3 Database Patch Set

The Database Patch Set includes the CPUApr2008 content.

So the CPUApt2008 was available to all at least month before official release and was included in patch set! Is this situation secure?

Anyway I had to install CPUApr2008. It's good that there is no need to recompile views this time. I've sucessfully installed it on test server, with minor problem. In installation log I've found one problem:

BEGIN emd_maintenance.recompile_invalid_objects; END;

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "SYSMAN.EMD_MAINTENANCE" has been
ORA-06508: PL/SQL: could not find program unit being called:
ORA-06512: at line 1

Hmm... After reconnecting, this commend run without errors:
SQL> conn / as sysdba
SQL> BEGIN sysman.emd_maintenance.recompile_invalid_objects; END;

PL/SQL procedure successfully completed.
After all, server runs ok, and I do not find any problems within applications running on this.

Cheers Paweł

Related Articles on Paweł Barut blog:
Categories: Development

JavaScript API Documentation

Duncan Mein - Sat, 2008-04-19 06:39
I have just been reading the Apex 3.1 API documentation and noticed that the JavaScript API that are commonly used in Apex are now documented:


There is some very useful stuff in there!!

Avoid Deprayments

Robert Vollman - Fri, 2008-04-18 11:01
There are deployments and there are deprayments. What's the difference?A deployment is when you deploy a change and verify that it was successfully deployed and functioning properly.A deprayment is when you "deploy and pray." It's kind of like Dr. Evil in the Austin Powers movie: you don't actually witness that the deployment succeeded, you just walk away and assume everything went according toRobert Vollmanhttp://www.blogger.com/profile/08275044623767553681noreply@blogger.com3

Integrating Financial Management with Project Management using EcoSys Financial Manager

Richard Byrom - Thu, 2008-04-17 15:11

Recently I been working with a company called Ecosys Management to learn and implement one of their products, EcoSys Financial Manager. According to their site: -

EcoSys Financial Manager Architecture EcoSys Financial Manager (FM) is a web based enterprise project financial management system. Project financial management involves all aspects of managing and monitoring the flow of funds from the organization into discrete initiatives, programs, projects, and ongoing operations. These discrete efforts execute and should align to strategic objectives and priorities. The project financial management cycle encompasses long range planning, fiscal period budgeting, forecasting, spending requests, allocations, performance measurement, controls, closeouts, and adjustments.

Large organizations need to be agile and responsive - financial processes and workflows need to flow top down and bottom up. Finance and Accounting needs to reconcile information flowing in both directions: down from executives responsible for strategic planning and up from the people in the field who are close to the work required.

EcoSys FM successfully bridges corporate strategic planning and project financial management for our customers in a varied range of industries. Particularly if you are looking to integrate project management with financial management, EcoSys FM is a proven solution in organizations using Primavera and other enterprise project management systems.

If you’re interested in learning a bit more then download this EcoSys Financial Manager Presentation. If you’re potentially interested in implementing this product in your organisation then contact EcoSys


crsctl start crs does not work in 10gR2

Gaurav Verma - Thu, 2008-04-17 08:33


Nowadays, we are doing a 10g upgrade for  one of our clients and hit upon the idea of pre-staging the 10gR2 CRS + DB technology stack on their RAC servers that are running 9iR2 RAC on HP service gaurd already. This is nothing but a downtime reduction technique, that saved about 5-6 hours. Thankfully, the idea worked, but not before some excitement.
Suprise, surprise..
A week after doing the 10gR2 CRS + DB installation on the pre-production servers, when we were starting the real Database upgrade, we had to bring up the 10gR2 CRS.

I was surprised to see that the 10gR2 CRS services would not come up. We had tried the following three things:

1) Uncommenting the crs, css, and evm daemons in /etc/inittab
2) Issued /etc/init.d/init.crs enable
3) Issued /etc/init.d/init.crs start
4) Isused $ORA_CRS_HOME/bin/crsctl start crs

I was pretty aghast. Apart from thinking of logging a tar, on searching metalink, we came across a new command that I had not tried in 10gR1.

raclinux1:/opt/oracle/product/10.2.0/CRS/bin # ./crsctl start crs
Attempting to start CRS stack
The CRS stack will be started shortly

raclinux1:/opt/oracle/product/10.2.0/CRS/bin # ./crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM

raclinux1:/opt/oracle/product/10.2.0/CRS/bin # ./crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM

raclinux1:/opt/oracle/product/10.2.0/CRS/bin # ./srvctl status nodeapps -n raclinux1
PRKH-1010 : Unable to communicate with CRS services.
[Communications Error(Native: prsr_initCLSS:[3])]

raclinux1:/opt/oracle/product/10.2.0/CRS/bin # ./srvctl start nodeapps -n raclinux1
PRKH-1010 : Unable to communicate with CRS services.
[Communications Error(Native: prsr_initCLSS:[3])]

raclinux1:/opt/oracle/product/10.2.0/CRS/bin # ./srvctl status nodeapps -n raclinux1
PRKH-1010 : Unable to communicate with CRS services.
[Communications Error(Native: prsr_initCLSS:[3])]

raclinux1:/opt/oracle/product/10.2.0/CRS/bin # ./crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM
This is then when we tried the crsctl start resources and the CRS actually came up:

raclinux1:/opt/oracle/product/10.2.0/CRS/bin # ./crsctl start resources
Starting resources.
Successfully started CRS resources

raclinux1:/opt/oracle/product/10.2.0/CRS/bin # ./crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy
At this point, I am not sure why the behaviour changed in 10gR2, whether it was intentional or un-intentional or whether this is a bug. But I am glad that we have a workaround. Everyday is a new learning.

Materialized view refresh change in 10G and sql tracing

Yasin Baskan - Thu, 2008-04-17 03:33
Complete refresh of a single materialized view used to do a truncate and insert on the mview table until 10G. Starting with 10G the refresh does a delete and insert on the mview table. This guarantees that the table is never empty in case of an error, the refresh process became an atomic operation.

There is another difference between 9.2 and 10G in the refresh process, which I have realized when trying to find out why a DDL trigger prevented a refresh operation. In 9.2 the refresh process runs an ALTER SUMMARY statement against the mview while in 10G it does not.

We have a DDL trigger in some of the test environments (all 9.2) for preventing some operations. The trigger checks the type of the DDL and allows or disallows it based on some conditions. After this trigger was put in place some developers started to complain about some periodic mview refresh operations failing a few weeks ago. I knew it was not because of the TRUNCATE because the trigger allowed truncate operations.

So I enabled sql tracing and found out what the refresh process was doing. Here is a simplified test case for it in 10G.

YAS@10G>create table master as select * from all_objects where rownum<=5;

Table created.

YAS@10G>alter table master add primary key (owner,object_name,object_type);

Table altered.

YAS@10G>create materialized view mview as select * from master;

Materialized view created.

YAS@10G>exec dbms_mview.refresh('MVIEW');

PL/SQL procedure successfully completed.

YAS@10G>create or replace trigger ddl_trigger before ddl on schema
2 begin
3 if ora_sysevent<>'TRUNCATE' then
4 raise_application_error(-20001,'DDL NOT ALLOWED');
5 end if;
6 end;
7 /

Trigger created.

YAS@10G>exec dbms_mview.refresh('MVIEW');

PL/SQL procedure successfully completed.

The refresh was successful in 10G even after the DDL trigger was active. In 9.2 it errors out after the DDL trigger is enabled.

SQL> exec dbms_mview.refresh('MVIEW');
BEGIN dbms_mview.refresh('MVIEW'); END;

ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of YAS.MVIEW
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1

After enabling sql trace and running the refresh again, the trace file shows the problem statement.

SQL> alter session set sql_trace=true;

Session altered.

SQL> exec dbms_mview.refresh('MVIEW');
BEGIN dbms_mview.refresh('MVIEW'); END;

ERROR at line 1:
ORA-04045: errors during recompilation/revalidation of YAS.MVIEW
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 820
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 877
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 858
ORA-06512: at line 1

The last statement in the trace file is this:


So, in 9.2 refresh process runs an ALTER SUMMARY command against the mview. After changing the trigger to allow DDL operations on SUMMARY objects I could refresh the mview without errors.

SQL> r
1 create or replace trigger ddl_trigger before ddl on schema
2 begin
3 if ora_sysevent<>'TRUNCATE' and ora_dict_obj_type<>'SUMMARY' then
4 raise_application_error(-20001,'DDL NOT ALLOWED');
5 end if;
6* end;

Trigger created.

SQL> exec dbms_mview.refresh('MVIEW');

PL/SQL procedure successfully completed.
Tonguc Yilmaz had a recent post about using 10046 for purposes other than performance tuning, like finding out what statements a procedure runs. I find uses for sql tracing nearly everyday and the above case is one of them. When you are not able to understand why an error happens, it is sometimes very useful to turn on sql tracing and examine the trace file. Errors related to specific bind values, errors raised from a "when others" block (you know we do not like "when others", right?) are a couple of things you can dig and analyze with sql tracing.

Carnegie Mellon Tepper School of Business - Part Time MBA Program

Arvind Jain - Wed, 2008-04-16 22:34
Quite many people have contacted me about the Carnegie Mellon part time MBA Program since I started the same in 2007. After having been through the experience for a year now, I can see direct applications of many of the Business School teachings to SOA world.
After all ... SOA & BPM is mostly about Business & IT alignment within an organization and in its partner eco system. So I though that it will be a good idea to write a summary about the CMU MBA program here in this blog.

Program Overview: I have been fortunate that the CMU MBA program is offered at Cisco Campus apart from their West Coast campus ( CMU West - http://west.cmu.edu/ ) in the Moffet Field (you can see signs on 101 north near Moffet Field exit). This is very handy at times when I need to hop out of meetings and join the class within minutes of getting out of office. It is first time at Cisco but the program itself was started in 1996.
It is delivered to corporate campuses in real-time via video-conferencing technologies and CISCO is leading the way with its TelePresence technology which is as real life as it can get. Others in the MBA class are from companies like Lockheed Martin, Goldman Sachs, United Technologies, Oracle, Intel, Applied Materials, Pitney Browns etc.

Tepper MBA program is highly known in Financial & Operations Research area due to its stress on analytical approach to problem solving and decision making in complex and dynamic business environments. CMU Tepper is ranked 3rd in Wall Street Journal Rankings.

Part Time program has same admission/graduation criteria as it exists for Full/Part-time students in CMU Pittsburg Campus. More info at: http://www.tepper.cmu.edu

If you need more information please feel free to get in touch.


Disclaimer: Opinions expressed are my own and CISCO does not sponsor or endorse this program.

Quick Don't Look! ohhhh too late

Carl Backstrom - Wed, 2008-04-16 17:33
Begin PSA.

This is a quick heads up , if you haven't checked the blogs you link to lately and you link to anything on orablogs.com you might want to remove or change that link.

orablogs.com isn't what you think it is anymore ;)

While the first click through is "probably" SFW any link or popup after that is probably NSWF.

Stupid link farms.

End PSA.

Blog Name Change...

Oracle EPM Smart Space - Wed, 2008-04-16 07:45
You may have noticed a subtle change in the name of the blog. It has changed from 'Oracle EPM Smart Space Technology' to 'Oracle EPM and BI Technology'. This is because many of our current endeavors are moving beyond a single product and we want to blog about them. This small change will allow for more posts on more topics. Anything EPM, BI and the technologies that power them are now fair game.

Categories: Development

Techies Protest against OOXML - Update

Siva Doe - Tue, 2008-04-15 15:58

There was indeed a candle light vigil. Source The Hindu.

Oracle Database 11g Release 2 is coming

Pawel Barut - Tue, 2008-04-15 15:52
Written by Paweł Barut
I've just received Oracle Partner Network newsletter with invitation to Oracle Database 11g Release 2 Roadshow. On this Roadshow Oracle is also going to find partners for beta testing. My guess: This year Oracle 11g R2 will be the main topic on Oracle Open World.
Schedule of Roadshow in Europe:
  • May 12, 2008: UK, Reading
  • May 13, 2008: Benelux, De Meern
  • May 14, 2008: Germany, Muenchen
  • May 15, 2008: Italy, Milan
  • May 16, 2008: Turkey, Istanbul
  • May 19, 2008: Czech Republic, Prague
  • May 20, 2008: Poland, Kraków
  • May 21, 2008: Sweden, Stockholm
  • May 22, 2008: Spain, Madrid
If you are OPN member then you probably also get invitation.
I've registered. I hope to get confirmation soon. I do not expect many technical details. I rather expect marketing speech. For me most important would be to meet people that work on new DB version, and become beta tester for 11gR2.

Cheers Paweł

Related Articles on Paweł Barut blog:
Categories: Development

Indian techies protest against OOXML

Siva Doe - Mon, 2008-04-14 22:49

Interesting read. From The Hindu newspaper. Here is the link.


Subscribe to Oracle FAQ aggregator