Tuesday, 29 January 2013

Primary Key and Foreign Key on Char field (ORA-02291: integrity constraint)



I have tables a and b with character field as its primary key foreign key relation. The primary key field is with char(5) and foreign key field is with char(50). When I tried to insert to child table selecting from primary key column i get "ORA-02291".

After giving little rest to brain I figured out whats going wrong. When insert is fired to column with char data type of child table, oracle is appending spaces on top of the value coming from primary key column (because its a char field!!!) . So primary key column value + spaces to fill the character field that makes the child a different value, that's the reason for ORA-02291.

This is how it works

SQL> create table a(id number,rel char(5)) ;

Table created.

SQL> create table b(id number,rel char(5)) ;

Table created.

SQL> create table c(id number,rel char(50)) ;

Table created.

SQL> alter table a add(constraint a_pk primary key (rel));

Table altered.

SQL> alter table b add(constraint b_fk foreign key (rel) references a(rel));

Table altered.

SQL> alter table c add(constraint c_fk foreign key (rel) references a(rel));

Table altered.

SQL>
SQL> insert into a values(1,'A') ;

1 row created.

SQL> INSERT INTO B SELECT * FROM A;

1 row created.

SQL>INSERT INTO C SELECT * FROM A;
INSERT INTO C SELECT * FROM A
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.C_FK) violated - parent key not found


SQL> alter table C modify(rel char(5)) ;

Table altered.

SQL>INSERT INTO c SELECT * FROM A ;

1 row created.





The varchar version



SQL> create table a(id number,rel varchar2(5)) ;

Table created.

SQL> create table b(id number,rel varchar2(5)) ;

Table created.

SQL> create table c(id number,rel varchar2(50)) ;

Table created.

SQL> alter table a add(constraint a_pk primary key (rel));

Table altered.

SQL> alter table b add(constraint b_fk foreign key (rel) references a(rel));

Table altered.

SQL> alter table c add(constraint c_fk foreign key (rel) references a(rel));

Table altered.

SQL> insert into a values(1,'A') ;

1 row created.

Tuesday, 8 January 2013

Void option on Payment actions screen is disabled while trying to void a payment.



Void option on Payment actions screen is disabled while trying to void a payment.

Oracle application R12(12.1.3)


The payment is done for a prepayment and the prepayment is already applied to a standard invoice.

Oracle actually restrict voiding a payment for a prepayment already  applied to an invoice

To void the payment 

Go to prepayment applications and unapply any invoices applied
Query for the invoice to which you have applied the prepayment (now the invoice should have new distribution line and status must have changed to ‘Needs revalidation’)
Validate the invoice and create accounting
Go back to payment screen and query the payment you want to void
Click the Actions Button
Void check box should be enabled now

Saturday, 5 January 2013

Accessing oracle application 11i Froms 11.5.10.2 on IE8(XP, Windows 7)

Accessing oracle application 11i 11.5.10.2 on IE8, works on both XP and windows 7

If you try to access oracle applications 11i 11.5.10.2 forms on IE8 it installs jinitiator but when the form link is clicked to open the form explorer just closes abruptly.

oracle recommends upgrading to Sun JRE native plugin (Oracle JInitiator 1.3 for E-Business Suite 11i - Forms version 6.0.8.x [ID 552692.1])


The workaround to get it working on IE8 is this way

go to Oracle Java Archive website and download Java SE Runtime Environment 6u13 (jre-6u13-windows-i586-p.exe)




Install the JRE 6u13 (jre-6u13-windows-i586-p.exe)

After installation has finished successfully

copy the jvm.dll from java jre 6u13 installation directory (C:\Program Files (x86)\Java\jre6\bin\client) to the jinitiator direcotry (C:\Program Files (x86)\Oracle\JInitiator 1.3.1.21\bin\hotspot)

Rename the the old jvm.dll file in the (C:\Program Files (x86)\Oracle\JInitiator 1.3.1.21\bin\hotspot) directory

Now open explorer and login to the application and access the forms :)