Home » Developer & Programmer » Forms » FRM-40735 WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01422
FRM-40735 WHEN-BUTTON-PRESSED trigger raised unhandled exception ORA-01422 [message #412388] Thu, 09 July 2009 02:11 Go to next message
simcky
Messages: 38
Registered: July 2009
Member
hey guy's i just a newbie to oracle6i i made a form using datablock wizard in which i had taken three blocks using only one database table the structure of table is as follows
desc prmamed
Describing prmamed....
NAME Null? Type

--------------------------------------------------------------------------------
---------
--------------------------------------------------------------------------------
EMPNO NOT NULL VARCHAR2(8)
EARNINGDEDUCTION NOT NULL VARCHAR2(5)
PAYPERIOD NOT NULL VARCHAR2(6)
REFNO NOT NULL VARCHAR2(15)
REFDATE DATE
AMT NUMBER(5,0)
PAYCALPERIOD VARCHAR2(6)
BILLNO VARCHAR2(8)
CHANGEDATE DATE
USERID VARCHAR2(30)

and in form my
first block name is med and in this i m taking only empno and earningdeduction item's
second block name is pramamed1 and in this block i m taking payperiod,refno,refdate,amt,paycalperiod and in this block i m only fetching the data from database to display
and third block name is prmamed2 and in this block i m taking payperiod,refno,refdate,amt,paycalperiod and i m trying to save and update data from form to database in this block.
when on a push button i m writting the trigger which is as follows
declare
v_payperiod varchar2(6);
v_refno VARCHAR2(15);
v_refdate DATE;
v_amt NUMBER(5,0);

begin
go_block('prmamed2');
first_record;
go_item('prmamed2.payperiod');
select payperiod,refno,refdate,amt into v_payperiod,v_refno,v_refdate,v_amt from PRMAMED where empno=:med.empno and earningdeduction=:med.earningdeduction and paycalperiod is null;
loop
if
:prmamed2.payperiod <> v_payperiod
then
update PRMAMED
set payperiod=:prmamed2.payperiod
where empno=:med.empno and earningdeduction=:med.earningdeduction and paycalperiod is null;
elsif
:prmamed2.refno<>v_refno
then
update PRMAMED
set refno=:prmamed2.refno
where empno=:med.empno and earningdeduction=:med.earningdeduction and paycalperiod is null;
elsif
:prmamed2.refdate<>v_refdate
then
update PRMAMED
set refdate=:prmamed2.refdate
where empno=:med.empno and earningdeduction=:med.earningdeduction and paycalperiod is null;
elsif
:prmamed2.amt<>v_amt
then
update PRMAMED
set amt=:prmamed2.amt
where empno=:med.empno and earningdeduction=:med.earningdeduction and paycalperiod is null;
EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE' ;
next_record;
commit_form;
end if;
end loop;
end;
i m getting the error i.e frm-40735 when-button-pressed trigger raised unhandled exception ORA-01422
please help me out to remove this prob............


[MERGED by LF]

[Updated on: Fri, 10 July 2009 02:14] by Moderator

Report message to a moderator

Re: frm-40735 when-button-pressed trigger raised unhandled exception ORA-01422 [message #412391 is a reply to message #412388] Thu, 09 July 2009 02:16 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle
ORA-01422: exact fetch returns more than requested number of rows

Cause: The number specified in exact fetch is less than the rows returned.

Action: Rewrite the query or change number of rows requested

This error is probably caused by this statement:
select payperiod, refno, refdate, amt 
into v_payperiod, v_refno, v_refdate, v_amt 
from PRMAMED 
where empno = :med.empno 
  and earningdeduction = :med.earningdeduction 
  and paycalperiod is null;

You *must* make it return a single record; this can usually be done by adding another condition(s) into the WHERE clause.
Re: frm-40735 when-button-pressed trigger raised unhandled exception ORA-01422 [message #412419 is a reply to message #412388] Thu, 09 July 2009 04:23 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Is there any particular reason you're not using forms default update behaviour here?
Because you appear to just be updating the record in the database that corresponds to the one in your datablock if any of the values have been changed in form.
Forms will do that for you without you having to write any code.
Re: frm-40735 when-button-pressed trigger raised unhandled exception ORA-01422 [message #412429 is a reply to message #412419] Thu, 09 July 2009 05:36 Go to previous messageGo to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
Oracle gives some facilities like- updating, deleting, inserting.
You dont have to write any code for it.
Before coding get used to the basics of working in forms my dear friend.
updating, deleting and inserting [message #412567 is a reply to message #412388] Fri, 10 July 2009 00:39 Go to previous messageGo to next message
simcky
Messages: 38
Registered: July 2009
Member
hii

Oracle gives some facilities like- updating, deleting, inserting
could someone please give clue to me for this how to do updating,deleting and inserting in forms....
Re: updating, deleting and inserting [message #412574 is a reply to message #412567] Fri, 10 July 2009 01:06 Go to previous messageGo to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
just code a form, and run it.
At the top you will see many icons like save, exit, enter query mode, execute query mode, exit query mode, insert, delete, etc.

To Insert data:
just click on insert button, enter data in fields and press the save button.

to execute and delete:
press the enter query button, give the id or whatsoever in the field and press execute query. now press the delete button to delete it.


like wise you can perform many tasks. just explore.
Re: updating, deleting and inserting [message #412597 is a reply to message #412574] Fri, 10 July 2009 02:05 Go to previous messageGo to next message
simcky
Messages: 38
Registered: July 2009
Member
hii shaz thanks for the help but dear if we are making form for clients who don't the use of all these icon's than at that tme we will have to write trigger like which i posted before is there any other solution to this ........
Re: frm-40735 when-button-pressed trigger raised unhandled exception ORA-01422 [message #412600 is a reply to message #412429] Fri, 10 July 2009 02:12 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
There's no "clue" - these functionalities are built-in into Forms and you don't have to code querying, inserting, deleting or updating.

[EDIT: this was an answer to a question posted in another topic; now they have been merged]

Why your clients wouldn't use toolbar? There's really no need to reinvent the wheel. You'd rather use all built-in functionalities then coding them by yourself.

[Updated on: Fri, 10 July 2009 02:18]

Report message to a moderator

Re: frm-40735 when-button-pressed trigger raised unhandled exception ORA-01422 [message #412602 is a reply to message #412600] Fri, 10 July 2009 02:22 Go to previous message
shaz
Messages: 182
Registered: June 2009
Senior Member
you have to educate your clients. No body codes for inserting updating or deleting in oracle.
I was .NET programmer earlier and I was also amazed by such a lovely feature of oracle but after one month or so i got used to it. I also educated my clients about it. Now every thing is working fine. Smile
Previous Topic: functionality of when-validate-item trigger
Next Topic: Istallation Guide Oracle 10g (9.0.4)
Goto Forum:
  


Current Time: Fri Dec 02 20:54:40 CST 2016

Total time taken to generate the page: 0.26320 seconds