CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10goracleforms
sat33man
sat33man

Reputation: 75

Oracle forms 10g,multiple insert and update not working

I have tabular form (4 records displayed) with one datablock (based on a view).

After querying, the form could not update all the records but only first record value can select from LOV.

I called a procedure in on-insert and on-update.

The query is like this:

PACKAGE BODY MAPPING IS

PROCEDURE INSERT_ROW(EVENT_NAME IN VARCHAR2)
IS
BEGIN
IF (EVENT_NAME = 'ON-INSERT') THEN


INSERT INTO XX_REC_MAPPING
(BRANCH_CODE,COLLECTION_ID,PAY_MODE_ID,RECEIPT_METHOD,CREATED_BY,
CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES
( :XX_REC_MAPPING.OFFICE_CODE,
:XX_REC_MAPPING.COLLECTION_ID,
:XX_REC_MAPPING.PAY_MODE_ID,
:XX_REC_MAPPING.RECEIPT_METHOD,
:XX_REC_MAPPING.CREATED_BY,
:XX_REC_MAPPING.CREATION_DATE,
:XX_REC_MAPPING.LAST_UPDATED_BY,
:XX_REC_MAPPING.LAST_UPDATE_DATE,
:XX_REC_MAPPING.LAST_UPDATE_LOGIN); 

ELSIF (EVENT_NAME = 'ON-UPDATE') THEN 

UPDATE XX_REC_MAPPING
SET BRANCH_CODE=:XX_REC_MAPPING.OFFICE_CODE,
COLLECTION_ID=:XX_REC_MAPPING.COLLECTION_ID,
PAY_MODE_ID=:XX_REC_MAPPING.PAY_MODE_ID,
RECEIPT_METHOD=:XX_REC_MAPPING.RECEIPT_METHOD,
LAST_UPDATED_BY=:XX_REC_MAPPING.LAST_UPDATED_BY,
LAST_UPDATE_DATE=:XX_REC_MAPPING.LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN=:XX_REC_MAPPING.LAST_UPDATE_LOGIN
WHERE ROWID=:XX_REC_MAPPING.ROW_ID;

END IF;


END INSERT_ROW;

END MAPPING;

Whether the table gets looked or should I use some other trigger or loops?

Please could someone suggest to me how to edit this query for multiple updates?

Thanks

sat33

Upvotes: 0

Views: 8162

Answers (1)

OraNob
OraNob

Reputation: 694

Tony is correct.

Your block is based on a view behaves just as it would if the block were based on a table. When a record on this block is editted there are several things that happen. The :system.form_status will go from QUERY to CHANGED. Each record that is changed will have the :system.record_status set to CHANGED.

Consequently when the form is committed - Oracle forms automatically knows it has to do some work because the form_status is changed. It then begins to process the records in the block looping through the records and processing each record which has a record_status of changed.

If you are over-riding the ON-INSERT and ON-UPDATE (which you appear to be) because your block is based on a view - then all you need to do is cut-and-paste your statement INSERT INTO XX_REC_MAPPING ..... into the ON-INSERT trigger at the block level. This effectively over-rides the insert which forms would have fired.

And you should do the same for your update as well - only cut-and-paste that statement into the ON-UPDATE trigger at block level.

FRM:40654 record has been changed by another user requery to see the change

This error occurs due to the LOCKING mechanism firing in the form. The block is attempting to lock the record by using ROWID. If you have based the block on a view there will be no ROWID available.

Setting the KEY MODE on the block to be non-updateable should fix this issue.

I'd also suggest do some reading up on the KEY MODE property since this is pivotal to basing blocks on JOINS and VIEWS.

Upvotes: 2

Related Questions