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
Post a Comment