Limit error on function - help required

Ram43

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

Frank Kulash

Hi,

Ram43 wrote:Hi All,I am new to PL/SQL and this forum and require some assistance with an issue. 

Welcome to the Forum!

Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

If you're asking about a DML operation, such as UPDATE, then the INSERT statements you post should show what the tables are like before the DML, and the results will be the contents of the changed table after the DML.

Explain, using specific examples, how you get those results from that data.

Always say what version of Oracle you're using (e.g. 12.2.0.2.0).

See the forum FAQ:

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? 

Whenever you have a question concerning an error, post the full error message, including line numbers.

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

Show the definition of ID_TABLE.

Look at the condition

WHERE web_id = web_id

That will always be true (unless web_id is NULL).

I'll bet you meant to compare a column in the table to the second argument of the function.  In that case, give the argument a different name from the column, such as web_id_arg.  (In fact, it's never a good idea to have variables with exactly the same name as a column referenced in the procedure.)

What's the purpose of the first argument, mode?  I don't see it used anywhere in the function.

jaramill

EXCEPTION WHEN NO_DATA_FOUND THEN

      NULL;

    END;

Get rid of above code.....read why here --> http://www.orafaq.com/wiki/WHEN_OTHERS

Paulzip

When bulk collecting, you generally apply a limit clause to prevent loading huge amounts of data into memory (Program Global Area), using all of it up. You don't have to, but it's a wise thing to do. Typically a limit in the order of 100 or so is used. It turns out fetching more gives little advantage.

In your case you're returning a collection instance, which might restrict the usefulness of a limit clause, but often bulk collect is done in a cursor loop with limit. The items collected are then processed, then the next batch of items is fetched from the cursor. When the number of collection items is zero the cursor content is finished. Note, bulk collect doesn't return NO_DATA_FOUND if there's no data. You have to check the collection count.

What are you trying to achieve? A limit based solution could be demonstrated if you gave some context, and table creation script and inserts, we could advise you better.

mathguy

jaramill wrote:EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END;Get rid of above code.....read why here --> http://www.orafaq.com/wiki/WHEN_OTHERS

WHEN NO_DATA_FOUND  !=  WHEN OTHERS

What the OP did may very well be OK (depending on the business problem he is trying to solve). Whatever someone has to say about WHEN OTHERS is irrelevant here.

mathguy

A bit of clarification on the following:

 SELECT web_grant_access INTO   v_grant_access FROM   web_users WHERE  web_id = web_id  AND  web_deleted_yn = 'N';

When a name may mean more than one thing, there are rules for "resolving" the name. If the name is qualified, there should be no confusion. If it is not, in a SELECT statement it is taken to mean "column name from the table in the FROM clause." Only if there is no column by that name in the table, the next choice is "a variable from the calling function." Since you do have a column named WEB_ID in the table, the name (on BOTH sides of the equality condition) are interpreted as being the column name. So, the condition is equivalent to "WEB_ID IS NOT NULL".

The common workaround is to give your function parameter a different name.

But that is not the only option. Another option (which in my opinion is equally good, although I expect that many experts here will disagree) is to qualify the parameter name. You could write your condition as:

WHERE  web_id = get_a.web_id

or (perhaps even better, so there's no possible confusion for anyone reading the code):

WHERE  web_users.web_id = get_a.web_id

The first WEB_ID references a column from the WEB_USERS table; the second WEB_ID is the name of a parameter in the GET_A function.

This is what we normally do when we join two tables on a column that has the same name in both tables. I don't see why we should treat functions and procedures any differently.

jaramill

mathguy wrote:jaramill wrote:EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END;Get rid of above code.....read why here --> http://www.orafaq.com/wiki/WHEN_OTHERSWHEN NO_DATA_FOUND != WHEN OTHERSWhat the OP did may very well be OK (depending on the business problem he is trying to solve). Whatever someone has to say about WHEN OTHERS is irrelevant here.

Ah I see the NDF.......my mistake, but I will say that putting "NULL" is meaningless, unless this is just a test exercise