Easy and fast environment framework

Getting tired of typing and setting your ORACLE_HOME, ORACLE_SID and troubleshooting your . oraenv ?
going/connecting to different client sites with different platforms and server environments sometimes it is frustrating just how long it takes to get a proper SQL*Plus environment

So I always make use of this script

and for every server that I access I just have to do three bits of typing:

. ~oracle/.karlenv <<– set the environment
2 <<– choose an instance
s <<– rlwrap'd sqlplus alias

see example usage below:

[root@desktopserver ~]# su - oracle
[oracle@desktopserver ~]$
[oracle@desktopserver ~]$ vi .karlenv      <-- copy  (more...)

Look mom: a mutating table error without a trigger!

| Dec 30, 2011
Did you ever wonder why it says 'trigger/function' in the error message of ORA-04091?

ORA-04091: table ... is mutating, trigger/function may not see it

We know (and understand) by now that a row trigger cannot read a mutating table, but what's the /function all about in above message text? Well there is a completely different scenario that has nothing to do with triggers, where you can run into this error. I thought to spend a short post on that first, so that you really fully understand that ORA-04091 is your friend. And again in this scenario it prevents you from (more...)

Invoice Image Processing Architecture in Fusion Payables

| Dec 29, 2011

Fusion Payables is tightly integrated with Oracle Document Capture (ODC), Oracle Imaging and Process Management (IPM), Oracle Content Management and Oracle BPEL process Manager to provide a seamlessly integrated solution supporting the entire payables cycle starting from scanning of physical invoices, invoice image recognizition using OCR to pre-populate invoice header, routing of (more...)

You Don’t Know SQL

Or maybe you do. However, I’ve talked to a lot of DBAs (pretty much the target audience for this blog) and you might be surprised how often the SQL skills of your average DBAs dwindle over time. In today’s role-specific market, developers do developer stuff while DBAs do database stuff. Somewhere in between falls SQL – the red-headed stepchild of the programming world. Ask a DBA and they’ll probably say SQL is a legitimate fourth generation language. Tell a Java programmer that and they’ll laugh themselves into a seizure. It’s strange that DBAs become less familiar with SQL over time, (more...)

BIP Quartz tables

On a 11g BIP only install you can use the QUARTZ scheduler. The scheduler is fed from the XMLP_SCHED_JOB table from the repository. It will seed either the QRTZ_SIMPLE_TRIGGERS (one off jobs) or QRTZ_CRON_TRIGGERS (repeating jobs). This will feed the QRTZ_TRIGGERS table which controls the next execution time. The actual job is in the QRTZ_JOB_DETAILS table.


You can inject jobs directly into these tables. Remember that all DateTimes are in QUARTZ_TICKS calculated back to GMT time. A ms-sql conversion function DateTime to Ticks can be foud here. From Ticks to DateTime is this one.

Till Next Time

BIP11g Users and Roles

Did some work on a 11g BI publisher only install recently. Opposite to OBIEE11g you can still add users directly into BIP. Be sure to give them the right roles and sync it with your weblogic security realm.

Till Next Time

The mutating table error prevents non-deterministic behavior of your code

| Dec 23, 2011
A short recap of workarounds 1a and 1b from our previous post.
  • In workaround 1a we ensured that the query that was selecting from the mutating table, was being executed from within an autonomous transaction.
  • In workaround 1b we ensured that this query was being executed via a loopback database link.
We also discussed the major difference between the two workarounds:
  • 1a: the query sees the mutating table as it existed prior to the start of the main transaction.
  • 1b: the query sees the mutating table as it is being mutated by the triggering DML statement. Essentially this workaround behaves (more...)

Latch Timescales

To compare old and new latch mechanisms, I found useful the following illustration. Since it is hard for us, humans, to visualize milli- and microseconds, imagine “time microscope” that zooms in timed events one million times. Alternatively, just imagine contemporary Oracle software running on 1950th style hardware. Such microscope will magnify the microsecond to second. [...]

Visualizing Active Session History (ASH) Data With R

One of the easiest ways to understand something is to see a visualization. Looking at Active Session History (ASH) data is no exception and I’ll dive into how to do so with R and how I used R plots to visually present a problem and confirm a hypothesis. But first some background…


Frequently DBAs use the Automatic Workload Repository (AWR) as an entry point for troubleshooting performance problems and in this case the adventure started the same way. In the AWR report Top 5 Timed Foreground Events, the log file sync event was showing up as the #3 (more...)

Do we need Smarter Developers or Smarter DBAs?

There is an ongoing discussion about who is responsible for application performance tuning: Oracle developers or DBAs. While all Oracle database users and developers argue that it’s a DBA who is responsible for application performance tuning, DBA cries it’s a developer who write the code and so he should write it in an efficient way and later optimize it.

When it comes to application performance tuning most of the times DBA remains in a tight corner, new functionalities being added to the database are just like giving more weapons to performance firefighters when it should also empower the developers community (more...)

OBIEE11g / BIP Quartz Scheduler

Seems that Oracle is using there own standard for the Date Time format in the BIP quartz scheduler. They are not using standard ticks (starting on 1-jan-0001) but a milliseconds count starting on 1-jan-1970. So 1-Jan-2012 = 1325376000000. IE: a whole day is 86400000 ticks.

Till Next Time

Reference Data Set

| Dec 18, 2011
This is a new concept that has come in Fusion. Reference Data sets are logical groups which provides the enterprise to decide which business unit access the reference data groups, such as grades, locations, AR & AP payment terms, departments, and jobs. Oracle provides a default Reference Data set which can be used across all Business units. However, we can define our own Reference data sets, to partition the data from effectively.
E.g. in R12 we had to live with the entire list AR payment irrespective to the fact whether one OU was using it or not. however, in (more...)

Put Intelligence on the map (part 2)

In my last post, we discussed how OBIEE enables geospatial analysis on digital map. Now it’s time to get hands dirty and learn more from practice.

Out of the box, the deployment of OBIEE includes Oracle Mapviewer. However, no geospatial data or map tiles are provided with the product installation. Those can be acquired separately from NAVTEQ. A package of free sample NAVTEQ data with relevant deployment instructions can be downloaded from the URL: http://www.oracle.com/technetwork/middleware/bi-foundation/obiee-samples-167534.html

The free sample NAVTEQ data contains two components: geospatial data (coordinates, boundaries, etc) and map tiles (background images for different zoom level) (more...)

OBIEE10g Auto Suggest Prompt

A client asked me if I could create an auto suggest prompt for him. (ie: Google Style Prompt). Basically he wanted an edit box prompt which would fill an suggestion box which he could tab trough to make the right selection.

Since this isn’t a standard 10g functionality I wrote some JavaScript to make it happen. But I didn’t reinvent the wheel Knipogende emoticon ! The people at jQuery already did the bases, I simple adapted it for usage in OBIEE 10g.

1. Download the jQuery UI package here. Install it in your b_mozilla directory’s (or other webserver dirs you use).

2. Download (more...)

How ORA-13236 was about to steal Christmas

Recently an error was reported to me ...

Message: ORA-00604: error occurred at recursive SQL level 1
ORA-13236: internal error in R-tree processing: [insertion at root (mdrbin_mem_ins_rt)]
ORA-13236: internal error in R-tree processing: [partition and pair bucket (mdrbin_optmz_mem_ins_node)]
ORA-13236: internal error in R-tree processing: [pair buckets (mdrbin_partition_pair_bckts)]
ORA-13236: internal error in R-tree processing: [mdrugnd - getting a node (mdrbin_pair_bckts)]
ORA-13234: failed to access R-tree-index table [MDRT Table]
ORA-29400: data cartridge error
ORA-06512: at "MDSYS.SDO_IDX", line 149
ORA-06512: at line 1

StackTrace: at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String (more...)

Dev or DBA? Where does the responsibility lie…

… which could be the title of a really great post about a big prod issue debriefing, but sadly, all you’re getting is this post instead!

At the UKOUG 2011 conference, I attended a presentation by Michael Salt about indexes. During the course of the presentation, he mentioned something along the lines of “DBA’s should be responsible for indexes (including creating them)” (that’s not a quote; I can’t remember exactly what he said, unfortunately!). I more or less recoiled, because as a database developer, I would expect to be the person worrying about whether I should be creating an (more...)

UKOUG 2011 – Social stuff

I go to the UKOUG conferences to learn stuff from the presentations, but I also go to meet people. If I didn’t really know anyone, I wouldn’t go! (As evidenced by the fact that I didn’t go until 2 years ago, which was not long after I’d met Doug Burns and let him talk me into going! Doug knows *everyone*, I swear!).

Here are the Social highlights of this year’s conference for me (I could detail every meeting, but even I would get bored of reading all that! Needless to say, everyone I met was brilliant, and the discussions (more...)

SQL Navigator 6.6 released

| Dec 15, 2011
Quest Software released the 6.6 version of SQL Navigator. Mainly it corrects some bugs.
The most usefull new feature is in the history search (where you can search the runned scripts/commands for an expression) to filter the commands by type.

Check the Release Notes and the Known Issues List.

TIOBE Programming Community Index for December 2011

| Dec 15, 2011
PL/SQL is on the 14th place TIOBE Index (december 2011).
It's was 23st in November last year.
It was in 13th in 2008 and 2007 in the same month!

Worst Blogger Ever…

Yes.  I know.  I'm the worst blogger ever.  That last post says... <choke>... May.  But I have an excuse (sort of).  Busy does not describe my past six months.  Some of you are familiar with the reason, but for those of you who aren't, I'll post about it very soon.