Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

Yet another reason not to use "when others"!

You get the NO_DATA_NEEDED when your pipelined function produces more rows than your query fetches from it. Tom discusses this in detail at:

http://asktom.oracle.com/Misc/nodataneeded-something-i-learned.html

If you didn't have the "when others" clause, this would be silently ignored. But you do, so you're seeing it.

So you have a choice:

- Remove the when others OR
- Add a when NO_DATA_NEEDED handler that closes the cursor OR
- Both ;)

You're probably seeing this when going from 11.1 -> 12.1 because there was a bug related to this. Exceptions raised in a WHEN OTHERS clause of a pipelined function were ignored:

https://support.oracle.com/epmos/faces/DocumentDisplay?id=13088409.8&displayIndex=5

Bug 13088409 was fixed in 11.2.0.2.

Ref:

https://marogel.wordpress.com/2014/11/08/why-is-it-not-possible-to-raise-an-exception-when-handling-no_data_needed/

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Matthias Rogel, October 17, 2017 - 6:10 pm UTC