Solved

Keep getting error FRM-40735: PRE_SAVE trigger raised unhandled exception ORA-20000

Posted on 2006-07-13
12
1,511 Views
Last Modified: 2008-01-09
In my Oracle forms program, when the user commits the form, the current values in the form are saved to the table. However, I also want to update a value in another record at the same time, i.e.

 update t
    set
      t_status_ind = 'I',
      t_activity_date = sysdate               
   where
    t_code = :t.t_code and t_seqno = :t.t_seqno-1
    and t_status_ind = 'A';

I tried putting this code in the post_update trigger but nothing happens. When I put the code in the pre_save trigger I get the error: PRE_SAVE trigger raised unhandled exception ORA-20000. I do not know if I am going about it in the right away or putting the code in the correct location. I would greatly appreciate your help!
0
Comment
Question by:geeta_m9
  • 6
  • 5
12 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17101140
Try putting this code in the ON-COMMIT trigger
0
 
LVL 12

Assisted Solution

by:jwahl
jwahl earned 50 total points
ID: 17101301
Updates in the POST-COMMIT trigger are after the COMMIT (as the name says), that means you have to commit a second time after the update code to make the changes permanent.

if you put the code in the ON-COMMIT trigger, be sure to insert also COMMIT or COMMIT_FORM (because the on-commit trigger overwrites default commit logic of forms).
0
 

Author Comment

by:geeta_m9
ID: 17101306
I tried just now, but keep getting the same error.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17101360
Please post the complete error message. There should be something after the ORA-20000
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 200 total points
ID: 17101400
Also check if there are any database level triggers on your table "t".
20000 is normally a user defined exception raised by using RAISE_APPLICATION_ERROR
0
 

Author Comment

by:geeta_m9
ID: 17101401
Should I put the COMMIT_FROM before the code?
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 14

Expert Comment

by:sathyagiri
ID: 17101407
Run this query on SQLPLUS to find if there are any database level triggers on table T
set long 99999
select trigger_name,trrigger_body from user_triggers where table_name = 'T';
0
 

Author Comment

by:geeta_m9
ID: 17101438
Yes, there is a database level trigger, I forgot about that. That must be what is causing the problem.
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17101613
Yes see what causes the database trigger to raise an exception. That's what is causing your form to throw an error.
0
 

Author Comment

by:geeta_m9
ID: 17101637
You're correct! I had to modify the database trigger, so now it is saving properly. Should I leave the code in the ON_COMMIT trigger or would it be better to put it in the POST_DATABASE_COMMIT trigger?
0
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17101650
I think ON_COMMIT should be fine
0
 

Author Comment

by:geeta_m9
ID: 17101717
Thanks for your help!
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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

760 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

21 Experts available now in Live!

Get 1:1 Help Now