This was alleviated in 4.1 by the ability to use ROWID to identify the record.
I was asked the question recently on how to get around it back in Apex 3.x
I gave some suggestions, but didn't have the opportunity to investigate at the time. Of course, I was really curious so I gave it a go myself when I had a chance - so here goes.
First I defined and populated a simple table with a primary key containing three columns.
CREATE TABLE three_col_pk (my_year number ,my_month number ,my_loc varchar2(1) ,my_data varchar2(20) ,CONSTRAINT my3_col_pk PRIMARY KEY (my_year, my_month, my_loc) ) / INSERT INTO three_col_pk values (2011, 12, 'A', 'Scott'); INSERT INTO three_col_pk values (2011, 12, 'B', 'Kylie'); INSERT INTO three_col_pk values (2011, 12, 'C', 'Penny'); INSERT INTO three_col_pk values (2011, 11, 'A', 'Branka'); INSERT INTO three_col_pk values (2010, 12, 'A', 'Chris'); INSERT INTO three_col_pk values (2011, 11, 'B', 'Eddie'); INSERT INTO three_col_pk values (2010, 12, 'C', 'Ray'); INSERT INTO three_col_pk values (2010, 12, 'B', 'Kate'); INSERT INTO three_col_pk values (2011, 10, 'C', 'Lynda');
If you try create a standard Report/Form combination with this table using the wizards you will receive this when attempting to fetch a record:
ORA-01422: exact fetch returns more than requested number of rows
And if you attempt to create a new record, you'll receive this complaint depending on the column that is excluded from the screen:
ORA-01400: cannot insert NULL into ("SAGE"."THREE_COL_PK"."MY_YEAR")
So to get around this problem, the first thing that came to mind was to create a view that contained the rowid and build a Report/Form combination on that. I figured there'd be a few problems arising from this, but one must start.
CREATE OR REPLACE VIEW three_col_pk_vw AS SELECT rowid my_pk ,z.* FROM three_col_pk z /
This certainly fixed my Automatic Row Fetch process, which now looked like this:
However, when I tested an update using the Apex form I received this error
ORA-20505: Error in DML: p_rowid=AAAGsHAAFAAAGRvAAA, p_alt_rowid=MY_PK, p_rowid2=, p_alt_rowid2=. ORA-01733: virtual column not allowed here
When I saw this I thought - does the automatic row update also update the primary key?
I have been curious about this in the past so I thought good a time as any to test it out.
Tangent - for players of the PL/SQL Challenge, this was a topic of a recent quiz.
To determine if Apex includes the primary key in the update within the Automatic Row Processing - I created an even simpler table with a primary key and a before update trigger that only fired when the primary key column was updated.
(Does anyone else have trouble consistently remembering create trigger syntax?!)
CREATE TABLE pk_test (a_pk number ,b date ,CONSTRAINT pk_test_pk PRIMARY KEY (a_pk) ) / INSERT INTO pk_test VALUES (1, sysdate); CREATE OR REPLACE TRIGGER pk_test_trg BEFORE UPDATE OF a_pk ON pk_test FOR EACH ROW BEGIN INSERT INTO pk_test VALUES (0, sysdate); END; /
I needed some way of identifying if the trigger fired. I've always found the idea of mutating tables funny, so I thought I'd just attempt to insert into the triggering table.
Sure enough, when I tested an update using the Apex form I received this:
ORA-20505: Error in DML: p_rowid=1, p_alt_rowid=A_PK, p_rowid2=, p_alt_rowid2=. ORA-04091: table SAGE.PK_TEST is mutating, trigger/function may not see it ORA-06512: at "SAGE.PK_TEST_TRG", line 2 ORA-04088: error during execution of trigger 'SAGE.PK_TEST_TRG'
So a way around that is to remove the automatic row processing created by the wizard and replace it with my own code. For simplicity, I just used the following:
BEGIN INSERT INTO three_col_pk (my_year ,my_month ,my_loc ,my_data) VALUES (:P9_MY_YEAR ,:P9_MY_MONTH ,:P9_MY_LOC ,:P9_MY_DATA); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE three_col_pk SET my_data = :P9_MY_DATA WHERE my_year = :P9_MY_YEAR AND my_month = :P9_MY_MONTH AND my_loc = :P9_MY_LOC; END;
A merge would probably be more appropriate, but I have an issue with merge - for another blog post. And I couldn't be bothered working it out when this rolls off the fingers.
Hope you found that interesting.