chalie001
asked on
FRM-40735:KEY-COMMIT trigger raised unhandled exception ORA-01422
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;
ASKER
How can I re-write the query
ASKER
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
- 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);
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;
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;
ASKER
this is the constraint
ALTER TABLE employee ADD PRIMARY KEY (empacc, idno) DISABLE;
ALTER TABLE employee MODIFY (empid NOT NULL ENABLE);
ALTER TABLE employee ADD PRIMARY KEY (empacc, idno) DISABLE;
ALTER TABLE employee MODIFY (empid NOT NULL ENABLE);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rownum=1
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