| function returning more ... [message #8249] |
Wed, 06 August 2003 03:27  |
Mubeen
Messages: 44
Registered: February 2003
|
Member |
|
|
Hi All,
I have created a function with the following code
create or replace function q_lastname(v_jobid in employees.job_id%type)
return varchar2
is
v_lastname employees.last_name%type;
begin
select last_name into v_lastname from employees
where job_id = v_jobid;
return(v_jobid);
end;
Function created.
But i am getting the following error as the requested value is more than one row,so may i know how can i use cursors in a function and if possible please explain using the above function.
Elapsed: 00:00:00.00
SQL> execute :g_job_id := q_lastname('sa_rep')
BEGIN :g_job_id := q_lastname('sa_rep'); END;
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCOTT.Q_LASTNAME", line 6
ORA-06512: at line 1
thanx in advance
mubeen.
|
|
|
|
|
|
| Re: function returning more ... [message #8266 is a reply to message #8249] |
Wed, 06 August 2003 17:40  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
In addition to what Maaher said, you need to take a closer look at what your function is doing. As it is right now, it is accepting a job_id, selecting the last_name that corresponds to that job_id, but then attempting to return the job_id. Do you want it to accept a job_id and return a last_name or accept a last_name and return a job_id?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
