Home » SQL & PL/SQL » SQL & PL/SQL » Limit error on function (PL/SQL)
Limit error on function [message #680285] Mon, 04 May 2020 16:25 Go to next message
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 #680286 is a reply to message #680285] Mon, 04 May 2020 17:04 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Limit error on function [message #680288 is a reply to message #680285] Tue, 05 May 2020 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67363
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.
Re: Limit error on function [message #680290 is a reply to message #680288] Tue, 05 May 2020 01:24 Go to previous message
_jum
Messages: 573
Registered: February 2008
Senior Member
Duplicate post here.
Previous Topic: PL/SQL problem
Next Topic: Query needs to be tune
Goto Forum:
  


Current Time: Tue Sep 22 10:38:30 CDT 2020