Oracle SQL: Function with a cursor using no data found return value -


i trying write function extract code string, value in table based on code , return value lookup table.

this function:

   create or replace function maths_f     (id in varchar2)     return number    res_code varchar2(2);    points number(3,0);          cursor c1    select substr(substr(lcstring, instr(lcstring,'02')+2),1,2)     sdata s    s.id = id;  begin     open c1;    fetch c1 res_code;     select c_points points lcresults l     l.lc_code = res_code;  return points;  close c1;  exception when no_data_found    return 99;  end; 

i running statement test function:

select id, lcstring, substr(substr(lcstring, instr(lcstring,'02')+2),1,2) result, maths_f(id) points sdata id = 's00101620' ; 

the result of query

s00101620    11ot02ov29hv05ot03ox30oq  ov   99 

there value of 35 in c_points in lcresults table lc_code 'ov'... why function finding no data?


Comments

Popular posts from this blog

toolbar - How to add link to user registration inside toobar in admin joomla 3 custom component -

linux - disk space limitation when creating war file -

How to provide Authorization & Authentication using Asp.net, C#? -