Limit error on function [message #680285] |
Mon, 04 May 2020 16:25 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/8b2e48517850e66098f758cdcd0e9305?s=64&d=mm&r=g) |
ram43
Messages: 1 Registered: May 2020
|
Junior Member |
|
|
Hi All,
I am new to PL/SQL and require some assistance with an issue. I have a function which is using a 'BULK COLLECT' and causing a limit error, can you help suggest how I would overcome this issue? I have tested a few different changes I found from Google searches but none have worked so far. Below is the query causing the error:
FUNCTION get_a
(
mode VARCHAR2,
web_id NUMBER
)
RETURN ID_TABLE
AS
v_a_ids ID_TABLE;
v_grant_access VARCHAR2(1);
BEGIN
BEGIN
SELECT web_grant_access
INTO v_grant_access
FROM web_users
WHERE web_id = web_id
AND web_deleted_yn = 'N';
IF v_grant_access = 'Y' THEN
SELECT sav_id
BULK COLLECT INTO v_a_ids
FROM saved_quotats;
RETURN v_a_ids;
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
END;
Any help would be appreciated.
Thanks
[Updated on: Tue, 05 May 2020 00:38] by Moderator Report message to a moderator
|
|
|
|
Re: Limit error on function [message #680288 is a reply to message #680285] |
Tue, 05 May 2020 00:45 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68653 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
What is the Oracle error?
What is "ID_TABLE"?
An "END" is missing at the end.
A function returns a value or raise an exception, your exception clause doesn't.
Post the whole code.
Use SQL*Plus and copy and paste your session, the WHOLE session.
|
|
|
|