Oracle E-Business Suite Authentication in Ruby

I was developing Ruby on Rails application that access data from existing Oracle E-Business Suite application and I wanted to add to this application possibility for users to authenticate themselves by their existing Oracle E-Business Suite user names and passwords.

Oracle is not publicly providing algorythms which are used for user passwords encryption (which are stored in FND_USER table) and therefore some googling was necessary. I was lucky to find Jira eBusiness Suite Authenticator with full source code in Java which also included all Oracle E-Business Suite password encryption / decryption functions. Actually it seems that the >1000 lines source (more...)

Mail from Application Express with Access Control Enabled

One of the post installation task after installing Oracle 11 is the creation of Access Control List (ACL). Oracle provides a few scripts in order to allow flows_030000 to connect any host. What if you do not allow the database to connect any host but only one host. For instance, (more...)

Where is the Scott schema in APEX?

I decided to install Oracle 11g. And with Oracle 11g Application Express (APEX)is installed by default. Before importing an application based on the emp table, I decided to create a workspace based on the existing schema Scott. However the administrative interface did not allow me to select Scott. By not (more...)


We thought long and hard about possible titles for this new PL/SQL development standard proposed on OTN, but we couldn't improve on the one it came with.

I want share a new IDEA to create a new standard PL/SQL developing:

Function ( Standard Buffer) return number variable Number; variable1 Varchar2; begin variable := get from Standard Buffer; variable1 := get from Standard Buffer; { make your business } put in standard buffer your results end;

Give me feedback if you are interested at the new STANDARD called "FRAMEWORKIA".

A lot possibilities are ready.

Do you see the genius of it?


Multiple Verison of I.E on one machine

After the lastest round of updates were installed from Microsoft, I foolishly forgot to uncheck the "Upgrade to I.E 7" box and hence after a reboot, a fresh new version of Internet Explorer was waiting for me. On face value this appeared ok until I tried to access Mercury (more...)

SELECT as many rows as indicated by column value

| Oct 23, 2007
This select statement will return as many rows as are indicated by the value of a column. For example, if the column value is 7, then 7 copies of that row will be returned.



Insert into S
(101, 'TEST_PART1', 1);
Insert into S
(102, 'TEST_PART2', 2);
Insert into S
(103, 'TEST_PART3', 3);
Insert into S
(105, 'TEST_PART5', 5);

SELECT rnum, tpart, sq
CONNECT BY LEVEL <= s_alias.sq);

11g on Windows (32-bit) is out!

Thanks to OTN Headlines. I’m downloading Oracle 11g for windows at this moment.

But only the client version, since all Oracle servers I managed runs on Linux.

With the Windows client, I will be able to use cool new features like errorlogging.

Global Temporary Tables

| Oct 21, 2007
I listened intently to the new Oracle programmer as he described all the struggles he's been having on his first big project. As I've done many times already in his short career, I interrupt with some words of wisdom."It's time to add Global Temporary Tables to your toolbelt.""What are those?" he asks, as he opens the directory with the Oracle documentation. I smile. He has already learned

Identifying mutex holder

In my previous post on flushing a cursor we see that a process is waiting on a mutex. In this post we will try to identify the holder from a systemstate dump

• Find the process executing the purge API. In this case it was process 22
• Open the systemstate dump and go to PROCESS 22
• Under this (if the systemstate is at level 266) you will see the short stack of the process


As can be seen from the above stack it includes the function kxsPurgeCursor() which contains this piece of functionality (to flush the cursor)

• Under the session state object of this process you will see that the session is waiting on

waiting for 'cursor: pin X' blocking sess=0x0 seq=9208 wait_time=0 seconds since wait started=0 idn=dbabc3c, value=1, where|sleeps=e00000629

wait_time=0 indicates the session is waiting at the time this process was dumped.

• If you scroll down further in the same process state object and search for idn=dbabc3c

KGX Atomic Operation Log 3d6e12a08
Mutex 3da75c7d0(0, 1) idn dbabc3c oper GET_EXCL

Hence this process is waiting to acquire the mutex in exclusive mode.

• To find the process holding the mutex search on the string "dbabc3c oper"

You will find something like the below

KGX Atomic Operation Log 3d6e671e0
Mutex 3da75c7d0(0, 1) idn dbabc3c oper SHRD

Search the process holding this.
In vi editor you can use ?PROCESS which will lead you to


Hence Process 15 is holding the mutex and this is the process doing the merge join Cartesian in Session 1.

Flushing a single cursor

In a very intensive OLTP environment plan stability is of utmost importance as a single sub optimal query can bring the system down to its knees.

Using sql outlines and sql profiles is always not possible if you have an application with more
than a million lines of code and any one of the thousands of sql statements could have a plan flip.

In 11g there is a new procedure in the DBMS_SHARED_POOL package which helps you flush out a single cursor.
There is a typo in the syntax portion of the documentation in
which (more...)

11g on Linux x86_64 is out!

This morning, I was surprised to learn that the Linux x86_64 announcement release was not in this Oracle New Downloads RSS Feed. I got aware of the news reading Niall Litchfield Blog. I guess news travels faster via the Oracle blog community! , also, I thought Oracle would have normally released 11g for other o/s prior to releasing the Linux x86_64 version. In any case, I will download this release now.

It is now available here: Oracle 11g on Linux x86_64.

I have been waiting for this release for a while since two of my databases are stuck to 32 (more...)

ORA-06502: PL/SQL: numeric or value error: host bind array too small

| Oct 12, 2007
ORA-06502: PL/SQL: numeric or value error: host bind array too small

With 10gR2 dbms_output has been improved to handle more that 255 characters. I ran into this problem when I forgot to consider that it is the CLIENT ( not necessiarily the db server) that must be 10gR2. The PL/SQL procedure would run fine for me because I happened to have the 10gR2 client installed on my PC. Our QA had an older version of the client and kept running into this ora-06502.

So, it's probably not a good idea to drop those string chunking procedures until you are sure (more...)

Mike’s PL/SQL Notes 2007-10-06 16:40:00

| Oct 6, 2007

Incremental elimination using predicate negation

Application requirements sometimes include the need to match consumers with products. A common solution is to create a consumer table having a varchar2 column which will contain a string of predicates or a where clause. The consumer’s where clause is tested against the attributes of a product. The process of matching might be implemented in any of the following modes:

  • Tell me if this consumer matches this product
  • Tell me all of the consumers that match this product
  • Tell me all of the products that match this consumer

In any case, if the where clause (more...)

Mike’s PL/SQL Notes 2007-10-06 13:22:00

| Oct 6, 2007

This code will generate a list of integers from 1 to 50
10g and above


SELECT  mynum
FROM (SELECT 1 mynum FROM dual)
MEASURES ('mynote' AS note)
RULES  (note[FOR mynum FROM 1 TO 50 INCREMENT 1] = '' )
ORDER BY mynum;

This code will also generate a list of integers from 1 to 50;
8i and above

FROM   Dual
If you want to use either of these examples, make sure (more...)

Mike’s PL/SQL Notes 2007-10-06 11:53:00

| Oct 6, 2007

Powermultiset to find combinations

Part 1 of 2

This article consists of a statement of the problem to be solved, and then walks through the steps of solving it. There could be mistakes. There could be a better way to do this. Hopefully the reader can find some benefit regardless of the imperfections.

Statement of the Problem
Each department has it’s own products. Each product has a price.
The SQL query in this example answers the question:
Given that a customer can purchase, from a single department, any number of products, but not more than one of a specific (more...)

Mike’s PL/SQL Notes 2007-10-06 11:53:00

| Oct 6, 2007

Powermultiset to find combinations

Part 2 of 2

If you haven’t figured it out, the reason for the xx column is to identify the set, which was produced by the powermultiset function. Starting from row 1 of the html table above, read it like this:

product 10 of department 1  is a member of the set (DEMO_PROD_TYP(1, 10))
product 24 of department 2  is a member of the set (DEMO_PROD_TYP(2, 22))
product 22 of department 2  is a member of the set (DEMO_PROD_TYP(2, 24))
product 22 of department 2  is a member of the set (DEMO_PROD_TYP(2, 22), DEMO_PROD_TYP(2, 24))
product (more...)

MERGE and Database Link Problem

| Oct 5, 2007
Using 10g R2 database.

When trying to do a MERGE where the target table is on the remote database and the source table is a sub-select on the local database, the following error was raised:

ORA-01008: not all variables bound

The MERGE statement in question had no bind variables.

Furthermore, if I change the target table to an identical local table it works just fine, so it's not a syntax problem.

I searched all over the web and all over technet and but found only a few scant references to this problem. I was unable to find any (more...)


These are general-interest articles about computer hardware, software, and the internet. You know what kind of nerd I must be when I have separate sections for Science and Technology... and multiple subsections for each.

Oracle Validated Configuration RPM for OEL5

In the Oracle® Database Installation Guide for 11g there was a mention of Oracle Validated Configuration RPM.

When I first installed OEL5, the RPM’s for that o/s version were not yet released.

But since Oracle has now released the oracle-validated RPM’s for OEL5 on Unbreakable Linux Network. I decided to give it a try.

I installed OEL5 with the default installation. I then registered my machine with the up2date utility.

Do not forget to register to the proper channel on Unbreakable Linux Network or the following error will happen:

[root@oracletest ~]# up2date oracle-validated

Fetching Obsoletes list for channel: el5_i386_latest…

Fetching (more...)

Mike’s PL/SQL Notes 2007-10-02 17:14:00

| Oct 2, 2007

Call-Back Trigger


--Assume you have many application servers running EHCache. You need to make
--sure that these caches have the latest data from the database. Doing a
--total cache refresh on a periodic basis is not feasible because there
--is a large amount of data and only a small percentage of it gets updated.

--Instead, you want the database to send the latest data to the
--caches upon updating corresponding database data.

Create Or Replace Trigger Call_back
Before Update Of Prod_limit, Current_prod_cnt
On Prod_total
Referencing New As New Old As Old
For Each Row