Solved

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

Posted on 2006-07-13
12
1,616 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Procedure syntax 5 64
date show only hh:mm 2 50
Need help figuring out which database the Oracle connection string is accessing? 13 39
oracle query 4 30
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
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.

734 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