Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

911 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

15 Experts available now in Live!

Get 1:1 Help Now