Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Limit error on function - help required

Ram43May 4 2020 — edited May 5 2020

Hi All,

I am new to PL/SQL and this forum 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,

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

Message was edited by: Ram43

Comments

Post Details

Added on May 4 2020
6 comments
332 views