?
Solved

FRM-40735:KEY-COMMIT trigger raised unhandled exception ORA-01422

Posted on 2016-11-14
7
Medium Priority
?
220 Views
Last Modified: 2016-11-15
hi am having this error when saving this is my block
 BEGIN
        select emp_adress,
               emp_name,
               emp_surname,
        into   ws_address,
               ws_name,
               ws_surname
        from   employee
        where  empid    = :p_empid      and
               (empacc  = :p_accnr or
                idno    = :p_idno);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
             message('Employee account for this acc No: '||:p_accnr||
                      ' or '||'ID No: '||:p_idno);	
             raise form_trigger_failure;
    END;

Open in new window

0
Comment
Question by:chalie001
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:Helena Marková
ID: 41887459
This is the error:
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

You can change your code:
BEGIN
        select emp_adress,
               emp_name,
               emp_surname,
        into   ws_address,
               ws_name,
               ws_surname
        from   employee
        where  empid    = :p_empid      and
               (empacc  = :p_accnr or
                idno    = :p_idno);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
             message('Employee account for this acc No: '||:p_accnr||
                      ' or '||'ID No: '||:p_idno);      
             raise form_trigger_failure;
       WHEN TOO_MANY_ROWS THEN
             message('Too many rows for this acc No: '||:p_accnr||
                      ' or '||'ID No: '||:p_idno);      
             raise form_trigger_failure;

    END;

Here are predefined exceptions:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS00703
0
 

Author Comment

by:chalie001
ID: 41887582
How can I re-write the query
0
 

Author Comment

by:chalie001
ID: 41887650
how can i do this
- collect all the records returned by SELECT in collection (bulk collect)
- open a cursor for select and loop through it
- if you need only one record, add rownum = 1

if data is like this
CREATE TABLE employee 
   (emp_adress VARCHAR2(10 BYTE), 
	emp_name VARCHAR2(13 BYTE), 
	emp_surname VARCHAR2(8 BYTE), 
	empid VARCHAR2(5 BYTE), 
	empacc VARCHAR2(35 BYTE), 
	idno VARCHAR2(2 BYTE)
   ) 
Insert into employee (empacc,idno,empid,emp_surname,emp_name,emp_adress) values ('90415373',null,'PTE','MS','ADAMS',null);
Insert into employee (empacc,idno,empid,emp_surname,emp_name,emp_adress) values ('90415373',null,'PTE','MS','ADAMS',null);

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:Helena Marková
ID: 41887654
select emp_adress,
                emp_name,
                emp_surname,
         into   ws_address,
                ws_name,
                ws_surname
         from   employee
         where  empid    = :p_empid      and
                (empacc  = :p_accnr or
                 idno    = :p_idno) AND rownum<2;
0
 

Author Comment

by:chalie001
ID: 41887672
this is the constraint
ALTER TABLE employee ADD PRIMARY KEY (empacc, idno) DISABLE;
 ALTER TABLE employee MODIFY (empid NOT NULL ENABLE);
0
 
LVL 22

Accepted Solution

by:
Helena Marková earned 2000 total points
ID: 41887683
What is the problem now ? Doesn't AND rownum<2; in my last post work, does it ?
0
 

Author Closing Comment

by:chalie001
ID: 41887928
Rownum=1
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
What we learned in Webroot's webinar on multi-vector protection.
Via a live example, show how to take different types of Oracle backups using RMAN.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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