Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2006

    Unanswered: ORA-06502 - Bulk Bind: Truncated Bind


    I get the following error message, when executing the code at the bottom of this post:

    ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
    The additional problem about this error is, that it is only appearing when running the code on our test environment, but not in developement environment. Both machines are running Oracle 10g.

    The only information I was possible to get about this error-message via Google-Search was that one has to look out that the format of the table one is selecting from, has to be the same in which to bulk collect into.

    For example, table_1 has to have the same columns (count, types) like q_array. But as you can see in the code, I declare q_array as TABLE OF table_1%ROWTYPE. So it _is_ the same, isn't it?

    My questions now would be:
    - Have you encountered this error before?
    - Where is this error likely to be thrown? (at select time, or when processing the FOR-LOOP?)
    - What conclusion can I make, knowing that this code runs well on my developement environment, but not in testing environment (I can't debug on testing environment yet);
    - Do you know a way of how I can force this error to appear in my developement environment - for example a minimalistic code-snippet, which always raises this error (bulk bind: truncated bind)

    If necessary, I can provide additional code (call_procedure_1, call_procedure_2).

    Thank you very much for your help,



       TYPE t_q_array IS TABLE OF table_1%ROWTYPE;
       TYPE t_q_userviews IS TABLE OF user_views%ROWTYPE;   
     PROCEDURE do_something(i_user USER)
          q_array        t_q_array;
          q_userviews    t_q_userviews;
          l_i            PLS_INTEGER;
          IF i_user IS NOT NULL THEN
             SELECT *
             BULK COLLECT INTO q_array
               FROM table_1
              WHERE change_date >= g_some_date
                AND user_id = i_user;
             SELECT *
             BULK COLLECT INTO q_array
               FROM table_1
              WHERE change_date >= g_some_date;
          END IF;
          FOR l_i IN 1 .. q_array.COUNT
             g_err_string    :=
                                 'Error-causing statement: ' || q_array(l_i).name;
             call_procedure_1(q_array(l_i).name, q_array(l_i).sql_stmt);
          END LOOP;
    --	  END IF;
          g_some_date    := NULL;
          SELECT *
          BULK COLLECT INTO q_userviews
            FROM user_views
           WHERE view_name NOT IN(SELECT c_some_prefix || UPPER(name)
                                    FROM table_1)
             AND view_name LIKE c_some_view_name_prefix;
          FOR l_i IN 1 .. q_userviews.COUNT
             g_err_string    :=
                              'Error-causing statement: ' || q_userviews(l_i).view_name;
          END LOOP;
             g_err_string    := g_err_string || ' - ' || SQLERRM;

  2. #2
    Join Date
    Oct 2002
    Cape Town, South Africa
    Hi there,
    Unfortunately, never had this error before myself. But code looks 100%. I would look at the configuration differences between the two environments. I might suspect a memory related issue due to the fact that a bulk collect loads all the data to memory for processing. Also check the amount of data being returned in each env.

    Another thought, try changing your code to use a cursor with a bulk fetch with limit clause and see if you still get the error. Something like:
    open refcur for select * from table;
    fetch refcur bulk collect into q_array limit 250;
    exit when q_array.count = 0;
    for i in 1..q_array.count loop
    -- do whatever
    end loop;
    end loop;
    close refcur;
    If it starts working with this type of code, you will know the problem is with the amt. of data...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts