[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2006-07-13
12
Medium Priority
?
1,698 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 200 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 800 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';
1
 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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.

650 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