Forms 6i, same module generates different LOCK-Statements on different

[Orca777]

Topic : Forms 6i, same module generates different LOCK-Statements on different
instance

Error : FRM-40654 : Record has been changed" Error!

Hi, we have a real problem and do not understand it. A Forms-modul without
specified ONLOCK Section generates the LOCK Statement itself. The problem
is that dependend on the Instance ( same datamodel but different release
level ) it generates different LOCK Statements. The Lock is on a view :

INSTANCE ORCK super!:
SELECT
LAEI_LAD_GEW,LAEI_LDM_GEW,LAEI_EIG_GEW,LE_ZEICHEN,LE_NR,KZ_LAENGE,
GATTUNG,BUCH_NR,MAND_NR,AUFT_NR,LAEI_LFD_NR
FROM
v141 WHERE ROWID=:1 FOR UPDATE OF LAEI_LAD_GEW

INSTANCE OREK BAD; locks almost the complete table:
SELECT ROWID,LAEI_LAD_GEW,LAEI_LDM_GEW,LAEI_EIG_GEW,LE_ZEICHEN,LE_NR,
KZ_LAENGE,GATTUNG,BUCH_NR,MAND_NR,AUFT_NR,LAEI_LFD_NR
FROM
v141 WHERE LAEI_LFD_NR=:1 FOR UPDATE OF LAEI_LAD_GEW

We found a workaround and specified some (the tables PK ) colums as
Primary key in the data block of the view, but ... we do not understand
the behavior.
The views are the same, the prime keys if the base tables are the same.

Here the view :

CREATE OR REPLACE VIEW LPK.VW_0141
(le_zeichen, le_nr, kz_laenge, size_type, gattung, laei_lad_gew,
laei_ldm_gew, laei_eig_gew, hoehe, gg_kzn, zoll_kzn, laei_kuehlct_kzn,
beku_nr_empf, beku_nr_vers, klbe_nr_rechempf, besteller, klbe_nr_verf,
status, mand_nr, auft_nr, laei_lfd_nr, buch_nr, anz_gepo, gg_soll)
AS
SELECT LAEI.LAEI_LE_ZEICHEN LE_ZEICHEN,
LAEI.LAEI_LE_NR LE_NR,
LAEI.KELL_KELL_KENNZAHL_LE_LAENGE KZ_LAENGE,
LAEI.GARE_GARE_ID_SIZETYP SIZE_TYPE,
GATT.GATT_GATTUNG GATTUNG,
LAEI.LAEI_LAD_GEW LAEI_LAD_GEW,
LAEI.LAEI_LADEMITTELGEWICHT LAEI_LDM_GEW,
LAEI.LAEI_EIG_GEW LAEI_EIG_GEW,
LAEI.HOEH_HOEH_IN_FUSS HOEHE,
DECODE(LAEI.LAEI_AKT_STATUS_BET,
'GEB',
LAEI.LAEI_GEFAHRGUT_KZN_SOLL,
LAEI.LAEI_GEFAHRGUT_KZN) GG_KZN,
LAEI.LAEI_ZOLL_KZN ZOLL_KZN,
LAEI.LAEI_KUEHLCONTAINER_KZN LAEI_KUEHLCT_KZN,
--LAEI.KLBE_KLBE_NR_EMPFANGEN KLBE_NR_EMPFANGEN,        -- AEN TS
20021205
--LAEI.KLBE_KLBE_NR_VERSENDEN KLBE_NR_VERS,         -- AEN TS
20021205 LAEI.BEKU_BEKU_NR_EMPFANGEN, -- AEN TS 20021205
LAEI.BEKU_BEKU_NR_VERSENDEN, -- AEN TS 20021205
AUFT.KLBE_KLBE_NR_RECHNUNGSEMP KLBE_NR_RECHEMPF,
AUFT.AUFT_ANSPRECHPARTNER BESTELLER,
LAEI.KLBE_KLBE_NR_BETREUEN KLBE_NR_VERF,
LAEI.LAEI_AKT_STATUS_BET STATUS,
LAEI.AUFT_MAND_MAND_NR MAND_NR,
LAEI.AUFT_AUFT_NR AUFT_NR,
LAEI.LAEI_LFD_NR LAEI_LFD_NR,
AUFT.AUFT_BUCH_NR,
LAEI.LAEI_ANZ_GEPO ANZ_GEPO,
LAEI.LAEI_GEFAHRGUT_KZN_SOLL GG_SOLL
FROM LADEEINHEITEN LAEI, AUFTRAEGE AUFT, GATTUNGEN GATT
WHERE AUFT.MAND_MAND_NR = LAEI.AUFT_MAND_MAND_NR AND
AUFT.AUFT_NR = LAEI.AUFT_AUFT_NR AND
GATT.GATT_ID = LAEI.GATT_GATT_ID

LADEEINHEITEN, AUFTRAEGE are views on single table; GATTUNGEN is a table;
Does anybody know about the mechanism, or have do i to write an article
about 'the secret life of Forms 6i Modules'?

Regards for your help

Karl

[Orca777]

Problem solved :

The Test-Statement

SELECT vw_0141_orca777.le_rowid
FROM vw_0141_orca777
WHERE vw_0141_orca777.auft_nr = 13826

The Explain-plan on Test Environment (OREK) is different from the Explain-plan (ORCK)!!!


EXPLAIN-Plan Test-DB OREK

SELECT STATEMENT, GOAL = CHOOSE 6 1 35
NESTED LOOPS 6 1 35
NESTED LOOPS 6 1 31
INDEX RANGE SCAN LPK AUFT_PK 3 1 10
TABLE ACCESS BY INDEX ROWID LPK LADEEINHEITEN 3 3 63
INDEX RANGE SCAN LPK LAEI_AUFT_FK_I 2 3
INDEX UNIQUE SCAN LPK GATT_PK 650 2600

EXPLAIN-Plan Prod-DB ORCK

SELECT STATEMENT, GOAL = CHOOSE 5 1 41
NESTED LOOPS 5 1 41
NESTED LOOPS 5 1 37
TABLE ACCESS BY INDEX ROWID LPK LADEEINHEITEN 4 1 27
INDEX RANGE SCAN LPK LAEI_AUFT_FK_I 3 1
INDEX UNIQUE SCAN LPK AUFT_PK 1 1 10
INDEX UNIQUE SCAN LPK GATT_PK 706 2824



With rule-hint the explain-plan on test-environment OREK ist the same as productional ORCK

SELECT STATEMENT, GOAL = CHOOSE 5 1 41
NESTED LOOPS 5 1 41
NESTED LOOPS 5 1 37
TABLE ACCESS BY INDEX ROWID LPK LADEEINHEITEN 4 1 27
INDEX RANGE SCAN LPK LAEI_AUFT_FK_I 3 1
INDEX UNIQUE SCAN LPK AUFT_PK 1 1 10
INDEX UNIQUE SCAN LPK GATT_PK 706 2824



And now thats very interesting : following SQL on OREK Test-DB fails with ORA-01445

ORA-01445: cannot select ROWID from a join view without a key-preserved table
Cause: A SELECT statement attempted to select ROWIDs from a view derived from a join operation.
Because the rows selected in the view do not correspond to underlying physical records, no ROWIDs can be returned.



SELECT *
FROM vw_0141_orca777
WHERE vw_0141_orca777.ROWID = (SELECT vw_0141_orca777.le_rowid
FROM vw_0141_orca777
WHERE vw_0141_orca777.auft_nr = 13826)


But with RULE-Hint (same Explan-plan as ORCK Prod-DB )

The SAME Statement on OREK Test-DB worked WITHOUT ORA-ORA-01445

SELECT /*+RULE*/ *
FROM vw_0141_orca777
WHERE vw_0141_orca777.ROWID = (SELECT vw_0141_orca777.le_rowid
FROM vw_0141_orca777
WHERE vw_0141_orca777.auft_nr = 13826)




Conclusion :

Depending on the Execution-PLAN Oracle decided if it can return a rowid or not
In production-DB (ORCK) the problem never arised due to production amount data.

So if a View is adressed in Oracle forms it is recommended for locking
to specify the right columns as prime-key columns
you should not let Forms take control.


Thanks for help