Solved

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

Posted on 2006-07-13
12
1,558 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
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: 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
 
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I rollback insert statements after commit in oracle? 7 124
database upgrade 8 72
Oracle dataguard 5 30
Creation date for a PDB 5 18
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

776 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