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...)

Frameworkia

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?

(more...)

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

Uncategorized
| 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.


CREATE TABLE S

(
RNUM NUMBER,
TPART CHAR(10),
SQ NUMBER
)
;

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


SELECT rnum, tpart, sq
FROM s s_alias, TABLE(SELECT COLLECT(dummy)
FROM DUAL
CONNECT BY LEVEL <= s_alias.sq);

Global Temporary Tables

Uncategorized
| 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

ksdxfstk()+36<-ksdxcb()+2452<-sspuser()+176<-sigacthandler()+44<-__systemcall()+52<-semtimedop()+28<-sskgpwwait()+224<-ksliwat
()+988<-kslwaitns_timed()+48<-kskthbwt()+232<-kslwait()+116<-kksLockWait()+396<-kgxWait()+444<-kgxExclusive()+132<-kxsPurgeCur
sor()+476<-psdpur()+1444<-pevm_icd_call_common()+728<-pfrinstr_ICAL()+132<-pfrrun_no_tool()+72<-pfrrun()+832<-plsql_run()+696<
-peicnt()+260<-kkxexe()+824<-opiexe()+12736<-kpoal8()+1912<-opiodr()+1548<-ttcpip()+1284<-opitsk()+1432<-opiino()+1128<-opiodr
()+1548<-opidrv()+896<-sou2o()+80<-opimai_real()+124<-main()+152<-_start()+380


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

PROCESS 15:
-------------------------------------------


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
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_shpool.htm#sthref8578
which (more...)

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

Uncategorized
| 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

Uncategorized
| 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

Uncategorized
| 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)
 
MODEL
 
DIMENSION BY (mynum )
 
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

SELECT LEVEL num
FROM   Dual
CONNECT BY LEVEL <= 50;
If you want to use either of these examples, make sure (more...)

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

Uncategorized
| 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

Uncategorized
| 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

Uncategorized
| 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 oracle.com but found only a few scant references to this problem. I was unable to find any (more...)

Technology

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.

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

Uncategorized
| 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
(more...)