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....