DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

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

  1. #1
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448

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

    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 :
    Code:
    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 :

    Code:
    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
    Last edited by Orca777; 11-25-2003 at 08:26 AM.

  2. #2
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width