Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01422 / ORA-0651 but why? Please help! (merged 4)
ORA-01422 / ORA-0651 but why? Please help! (merged 4) [message #388163] Mon, 23 February 2009 09:58 Go to next message
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 Go to previous messageGo to next message
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! (merged 4) [message #388168 is a reply to message #388163] Mon, 23 February 2009 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
test_req_id = TEST_REQ_ID

is like 1=1.
How could Oracle knows if test_req_id is from column name or parameter? It can't, it takes the column.

Regards
Michel

[Updated on: Mon, 23 February 2009 10:29]

Report message to a moderator

Re: ORA-01422 / ORA-0651 but why? Please help! [message #388267 is a reply to message #388167] Tue, 24 February 2009 01:34 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: CLOB access ORA-06550
Next Topic: Avoid Parallel Execution of a Procedure and DBMS_JOB (merged)
Goto Forum:
  


Current Time: Thu Dec 08 15:57:55 CST 2016

Total time taken to generate the page: 0.12621 seconds