ORA-01422 / ORA-0651 but why? Please help! (merged 4) [message #388163] |
Mon, 23 February 2009 09:58  |
bjiggs
Messages: 1 Registered: February 2009
|
Junior Member |
|
|
I'll be the first to admit that I'm a bit of a PL/SQL newb but everything that I've read indicates that the following should work fine:
I have a function like this:
create or replace function TEST_STATUS(TEST_REQ_ID in number) return number is
Result number;
closed_date date;
due_date date;
begin
select test_completed_date,test_due_date into closed_date,due_date from test_requests where test_req_id = TEST_REQ_ID;
IF(closed_date is not NULL) THEN
Result := 1;
ELSIF(closed_date is NULL AND (SYSDATE < due_date)) THEN
Result := 0;
ELSE
Result := 2;
END IF;
return(Result);
end TEST_STATUS;
test_req_id is a unique primary key.
If I run the following query, it returns a single row:
select tr.test_req_id from test_requests tr
where tr.test_req_id = 24
Yet, the following will not work no matter what:
select tr.test_req_id,TEST_STATUS(tr.test_req_id)
from test_requests tr
where tr.test_req_id = 24
The error is:
Quote: |
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "xxx.TEST_STATUS",line 9
|
But it doesn't return more than one row. What gives?
I'm running Oracle 10g.
Thanks!
|
|
|
Re: ORA-01422 / ORA-0651 but why? Please help! [message #388167 is a reply to message #388163] |
Mon, 23 February 2009 10:27   |
szogu
Messages: 21 Registered: July 2008
|
Junior Member |
|
|
You need to change the parameter name on the function to something different from the column name TEST_REQ_ID.
Your select Statement is inside the function is trying to fetch all records on the table. Run in SQLPLUS the select statement you have inside the function
select test_completed_date,test_due_date
from test_requests
where test_req_id = TEST_REQ_ID;
Regards,
|
|
|
|
Re: ORA-01422 / ORA-0651 but why? Please help! [message #388267 is a reply to message #388167] |
Tue, 24 February 2009 01:34  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
szogu wrote on Mon, 23 February 2009 17:27 | You need to change the parameter name on the function to something different from the column name TEST_REQ_ID.
|
Although the reasoning is right, it is not necessary to rename the function-parameter.
You can alias the column with the function/procedure name:
SQL> create or replace procedure tst(ename in varchar2)
2 is
3 a varchar2(100);
4 begin
5 select ename into a from emp where emp.ename = tst.ename;
6 dbms_output.put_line('Found '||a);
7 end;
8 /
Procedure created.
SQL> exec tst('KING')
Found KING
PL/SQL procedure successfully completed.
|
|
|