Thanks for the question, Madhan.
Asked: August 03, 2016 - 2:37 pm UTC
Last updated: August 03, 2016 - 3:56 pm UTC
Version: oracle 12
Viewed 10K+ times! This question is
You Asked
Hi Team,
Below is the sql used
SELECT * FROM TABLE(pkg.fn_api('PR_LOAD_PL_PROT_TO_SITE','ROLE_ABBR')
Below is the code snippet of pkg
TYPE t_parm_val_txt_rec IS RECORD (
o_parm_value_txt VARCHAR2 (4000 BYTE)
);
TYPE t_get_param_tab IS TABLE OF t_parm_val_txt_rec;
FUNCTION fn_api(
ip_api_name IN VARCHAR2 (80 BYTE),
ip_param_name IN VARCHAR2 (50 BYTE)
)
RETURN t_get_param_tab PIPELINED
IS
o_parm_value_txt VARCHAR2 (4000 BYTE);
l_rec t_parm_val_txt_rec;
l_get_parameter_cur sys_refcursor;
BEGIN
o_parm_value_txt := NULL;
BEGIN
OPEN l_get_parameter_cur FOR
SELECT parm_value_txt
FROM portal_parm
WHERE api_nm = ip_api_name AND parm_nm LIKE ip_param_name || '%';
LOOP
FETCH l_get_parameter_cur
INTO l_rec.o_parm_value_txt;
EXIT WHEN l_get_parameter_cur%NOTFOUND;
PIPE ROW (l_rec);
END LOOP;
CLOSE l_get_parameter_cur;
EXCEPTION
WHEN OTHERS
THEN
raise_application_error
(-20101,
'ERROR: in fn_api(). '
|| SQLCODE
|| '; '
|| SQLERRM
);
CLOSE l_get_parameter_cur;
END;
RETURN;
END fn_api;
We are receiving an error ORA-06548: no more rows needed in 12.1.0.2.0 where as same code and same data it was working fine in 11.1.0.7.0.
When i google this issue, there is a note to handle exception when no_data_needed but i would like to check if there is any option available to avoid the code change in this case.
Expectation is - if the same code and same data working fine in oracle 11i then it should work in oracle 12. Any patch is available ? Please provide your thoughts on this....
and Chris said...
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment