Solved

COMMIT causing "FRM-40405 No changes to apply." error

Posted on 2001-08-31
12
3,886 Views
Last Modified: 2012-05-04
I've got a Save button that contains the code below.  It works fine the first time I click it, but on the second click I get the error message "FRM-40405 No changes to apply" even if there WERE changes made.  It saves them and seems to work like normal, but I still get the message.  This is a twofold question:

1) Is there something wrong, or is this normal behavior in Oracle when I commit again?

2) If this is normal, how can I suppress the message?

Here's the code.  Thanks again.

--------------------------
update SEI_Inv
   set SEI_Inv_Code          = :txtCode,
      SEI_Inv_ManufacturerID = :txtManufacturerID,
      SEI_Inv_TypeID         = :txtTypeID,
      SEI_Inv_Description    = :txtDescription,
      SEI_Inv_Serial         = :txtSerial,
      SEI_Inv_OrgNo          = :txtOrgNo,      
      SEI_Inv_CountyCode     = :txtCountyCode,
      SEI_Inv_Crew           = :txtCrew,
      SEI_Inv_Room           = :txtRoom,
      SEI_Inv_PurchaseDate   = :txtPurchaseDate,
      SEI_Inv_VendorID       = :txtVendorID,
      SEI_Inv_EncNo          = :txtEncNo,
      SEI_Inv_Voucher        = :txtVoucher,
      SEI_Inv_Cost           = :txtCost,      
      SEI_Inv_AppValue       = :txtAppValue,
      SEI_Inv_TransDate      = :txtTransDate,
      SEI_Inv_TransNo        = :txtTransNo,
      SEI_Inv_FedCode        = :chkFederalCode,
      SEI_Inv_StatusCode     = :txtStatusCode,
      SEI_Inv_LastUpdate     = sysdate
   where SEI_Inv_ItemNumber = :txtItemNumber;

commit;
0
Comment
Question by:bek
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 5

Expert Comment

by:ser6398
Comment Utility
When you run an explicit Update statement directly, Forms doesn't know that there are changes to commit.  It only knows that there are changes if you change the values in the fields in the form itself and then hit Save.  The method you are using will work, but it goes against the way Forms should be used.  You should have a database block for the SEL_Inv table, make your changes on the form, and then hit Save.  Forms will create the update statement itself, run it, and then commit.

If you wish to continue with the current method, you can create a When-New-Form-Instance with the following code to suppress that warning message:

:SYSTEM.MESSAGE_LEVEL := 5;

0
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
to follow up on ser's comment, you may want to set the message level back to its original value after the commit statement (otherwise all minor messages will remain supressed).

e.g. When-Button-Pressed on Save button:

update SEI_Inv
  set SEI_Inv_Code          = :txtCode,
     SEI_Inv_ManufacturerID = :txtManufacturerID,
     SEI_Inv_TypeID         = :txtTypeID,
     SEI_Inv_Description    = :txtDescription,
     SEI_Inv_Serial         = :txtSerial,
     SEI_Inv_OrgNo          = :txtOrgNo,      
     SEI_Inv_CountyCode     = :txtCountyCode,
     SEI_Inv_Crew           = :txtCrew,
     SEI_Inv_Room           = :txtRoom,
     SEI_Inv_PurchaseDate   = :txtPurchaseDate,
     SEI_Inv_VendorID       = :txtVendorID,
     SEI_Inv_EncNo          = :txtEncNo,
     SEI_Inv_Voucher        = :txtVoucher,
     SEI_Inv_Cost           = :txtCost,      
     SEI_Inv_AppValue       = :txtAppValue,
     SEI_Inv_TransDate      = :txtTransDate,
     SEI_Inv_TransNo        = :txtTransNo,
     SEI_Inv_FedCode        = :chkFederalCode,
     SEI_Inv_StatusCode     = :txtStatusCode,
     SEI_Inv_LastUpdate     = sysdate
  where SEI_Inv_ItemNumber = :txtItemNumber;
:SYSTEM.MESSAGE_LEVEL := 5;
commit;
:SYSTEM.MESSAGE_LEVEL := 25;


regards,

JT
0
 
LVL 5

Expert Comment

by:ser6398
Comment Utility
By the way, if you use the method I described, then all you put in the Save button is:  COMMIT_FORM;
 You no longer need to have the update statement coded anywhere.  

This is the way Forms was meant to work:  you create a Database Data Block based on the SEI_Inv table and add all the columns you need to it, you add the fields that users can see/modify to the layout, you create a button with COMMIT_FORM in it, run the form, select Enter_Query, enter query criteria, select Execute_Query, Forms returns a set of rows, you go to the rows and make changes (as soon as you make changes Forms flags that row as needs to be updated), you delete a row (as soon as you delete a row Forms flags that row as needs to be deleted), you add some rows (as sonn as you add a row Forms flags that row as needs to be inserted), then you hit Save button.  Forms goes through the rows and builds the Insert, Update, and Delete statements to make the database reflect what was done in the Form, and runs these statements itself, then commits.  You should almost never have to explicitly write Insert/Update/Delete statements in a Form.  If you build the form correctly, Oracle Forms will do all of this for you.
0
 
LVL 5

Expert Comment

by:ser6398
Comment Utility
"SYSTEM.MESSAGE_LEVEL := 25;" is not the default.  If he wants to set the message level back to default, he should use:

:SYSTEM.MESSAGE_LEVEL := 0;

0
 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
oops...you're absolutely right...
In fact though zero may not be what it was previous to yoou updating it, so in fact unless you specifically want it to go back to zero, you're probably better off setting a variable to the old value, changing it to your desired vbalue and then resetting it back to what it was before you changed it...
e.g.
oldmsg := :System.Message_Level;
:System.Message_Level := '10';
--do your stuff
:System.Message_Level := oldmsg;
0
 

Author Comment

by:bek
Comment Utility
I set the message level both before and after my commit, as jtrifts recommended, yet I still get the error message.

Also, the *first* time I save, I don't get an error message--only the second time.  Can anyone offer any insight why this message doesn't show up the first time, and why I can't seem to suppress the message?

Thanks.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:jtrifts
Comment Utility
uncertain...but here is more detail on the message level thing...and you should consider what ser has noted re: normal forms behaviour.

Insert statements should not be required very often unless you have good reason for not basing blocks over base tables.

regards

JT

To control the messages that end users see when they use a Form Builder application, you can:

n     use the SYSTEM.MESSAGE_LEVEL system variable to suppress specific "severity levels" of messages

n     use On-Error and On-Message triggers to replace the standard processing of messages

n     use the SYSTEM.SUPPRESS_WORKING system variable to prevent the update of an end user's screen (by suppressing the "Working..." message)

Message Severity Levels

Forms Runtime messages are ranked by severity.  Use the SYSTEM.MESSAGE_LEVEL system variable to can control the minimum severity level that displays to end users.
There are six levels of message severity that you can affect, listed here in increasing order of severity.

Level     Message  Description
0     All types of messages from the other levels of severity.
5     Reaffirms an obvious condition.
10     Indicates that the end user has made a procedural mistake.
15     Declares that the end user is attempting to perform a function for which the form is not designed.
20     Indicates a condition where the end user cannot continue an intended action due to a problem with a trigger or another outstanding condition.
25     Indicates a condition that could result in the form performing incorrectly.
>25     Indicates a message severity level that you cannot suppress via the SYSTEM.MESSAGE_LEVEL system variable.
Severity levels of individual Forms Runtime messages are labelled with "Level" in the Form Builder online help system.

Message Types

To use On-Error and On-Message triggers to replace Forms Runtime messages, you need to be aware of the three types of Forms Runtime messages:

Informative      Informs end users of the present state of processing  (e.g.,  Last value retrieved.)  or provides end users with context-sensitive guidance  (e.g.,  Press [Accept] to enter answer.).  Use the On-Message trigger to suppress the appearance of these messages.
Error      Inform end users of error conditions that prevent the end user's actions  (e.g.,  Function key not allowed. Press [Show Function Keys] for list of valid keys.).  Use On-Error triggers to suppress the appearance of these messages.  However, you cannot suppress error messages that appear on the command line  (e.g.,  Too many arguments on command line.).

Working      Inform end users that Form Builder currently is processing  (e.g.,  Working...).  You cannot use On-Error or On-Message triggers, or the SYSTEM.MESSAGE_LEVEL system variable to suppress these messages.

Message types of individual Forms Runtime messages are labelled with "Type" in the Form Builder online help system.
0
 
LVL 5

Expert Comment

by:ser6398
Comment Utility
On the first time there is probably some information somewhere in a database block that is new or changed, and Forms tries to save those rows to the database (which may or may not be sucessful).  After this, there are no new changes to be made so further Saves cause the "no new data" message.

Try setting the message level to 5 in the When-New-Forms-Instance trigger.

0
 
LVL 5

Expert Comment

by:ser6398
Comment Utility
By the way, it is possible that Forms is updating the table in addition to your explicit Update statement.  When you use COMMIT, Forms uses it as COMMIT_FORM and tries to commit any changes in the Form.  So you may be doing the Update 2 times, with the same information each time.

For the SEI_inv block, is the Database Data Block property set to Yes?  If so, then just comment out the Update statement and replace COMMIT with COMMIT_FORM and see if it does the update for you.
0
 

Author Comment

by:bek
Comment Utility
I don't use any database blocks in my application--right now, they're all control blocks (no field is bound to the database).  This should be the only application I ever have to write in forms, and it's due next week, so I'd rather not go back and tinker with how I save.  Everything seems to work right now, except that I get this extra message.

I will try some of your ideas above to suppress this message.  Let me know if anyone has any new ideas.

Thanks.
0
 
LVL 27

Accepted Solution

by:
kretzschmar earned 75 total points
Comment Utility
maybe you could simple use

forms_ddl('COMMIT');
0
 

Author Comment

by:bek
Comment Utility
Kretzschmar,

This didn't work by itself, but by raising the error level to 25, then issuing the forms_ddl('COMMIT'), I was able to suppress the error message.  When I raised the error level to 25 and tried only a COMMIT, then I still encountered the error.  Any idea why?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
percentage remaining 6 40
Add 0 to end of Number 21 71
Need help converting some sql server sql to Oracle sql 9 61
Oracle Syntax 8 38
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now