13 Replies Latest reply on May 16, 2013 2:24 PM by BluShadow

    Problem with error 06502 and cursor

    908996
      Hello.

      I have a problem with my code. I'll put an example:

      PROCEDURE Example() IS
      cursor example_cursor is select example_field1, example_field2,... from example_table;
      example_var example_cursor%rowtype;
      BEGIN
      for example_var in example_cursor loop
      dbms_output.put_line(example_var.example_field1);
      end loop;
      END;

      When I call the procedure from an anonimous block, the procedure execution fails giving me the ora-06502 Bulk Bind Truncated Bind error in the "for example_var in example_cursor loop" line.

      But, when I execute only the code in an anonimous block with declare cursor... begin for... end; it works.

      I don't know what can I do, or what must I searf for, to fix this problem.

      Thanks.
        • 1. Re: Problem with error 06502 and cursor
          €$ħ₪
          as you are not loading bulk data set you can ignore the declaration part for example_var

          Its just row by row display , try again
          • 2. Re: Problem with error 06502 and cursor
            Tubby
            905993 wrote:
            Hello.

            I have a problem with my code. I'll put an example:

            PROCEDURE Example() IS
            cursor example_cursor is select example_field1, example_field2,... from example_table;
            example_var example_cursor%rowtype;
            BEGIN
            for example_var in example_cursor loop
            dbms_output.put_line(example_var.example_field1);
            end loop;
            END;

            When I call the procedure from an anonimous block, the procedure execution fails giving me the ora-06502 Bulk Bind Truncated Bind error in the "for example_var in example_cursor loop" line.

            But, when I execute only the code in an anonimous block with declare cursor... begin for... end; it works.

            I don't know what can I do, or what must I searf for, to fix this problem.

            Thanks.
            Life becomes a lot easier for everyone when you show actual examples of your problems, copy / paste is pretty easy to do. Knowing your Oracle version is also typically a very important thing
            select * from v$version;
            The error code you posted does not jive with the error string you are quoting.
            ORA-06502: PL/SQL: numeric or value error
            is the complete error number/message.

            So most likely, you are having a conversion issue when DBMS_OUTPUT.PUT_LINE is attempting to convert your data type into a string (it needs to do this since it only displays strings).

            Do an explicit conversion on your data types and see if that makes a difference. If not, please put together a simple test case that we can reproduce your problem with (shouldn't take much time on your part).

            Cheers,
            • 3. Re: Problem with error 06502 and cursor
              908996
              Yes, I forgot to mention it. I've already tried to simply don't declarate the example_var, but the result is the same, it didn't work.

              The complete error code is:
              ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

              The code is:

              PROCEDURE insert_fact(sec number, fe date) IS
              cursor products_temp is select rowid, id, product, group from wproducts where sector=sec and fe_date=fe;
              reg1 products_temp%rowtype;

              BEGIN

              -- I capture error on delete
              begin
              delete from mproducts where fe_date=fe;
              exception when others then
              send_mail('error in delete finals',sqlerrm); --this is another procedure that works fine
              end;

              for reg1 in products_temp loop
              -- I capture error in the insert
              begin
              insert into mproducts (id, product, group,fe_date) values (reg1.id, reg1.product, reg1.group,fe);
              -- I capture error only in delete temporary
              begin
              delete from wproducts where rowid=reg1.rowid;
              exception when others then
              send_mail('error in delete temporary', sqlerrm); --this is another procedure that works fine
              end;
              exception when others then
              send_mail('error in insert',sqlerrm); --this is another procedure that works fine
              end;
              end loop;
              EXCEPTION WHEN OTHERS THEN
              send_mail('error in cursor', sqlerrm); --this is another procedure that works fine
              END;

              I have to say that this procedure have been working fine for years, and it started to fail recently.

              I know that the error is not on the deletes or the insert, because the email that I receive is with title 'error in cursor' and the sqlerrm is 'ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind'

              Hope it can help some more.

              Thanks.

              Edited by: 905993 on 04-ene-2012 11:39

              Edited by: 905993 on 04-ene-2012 11:41

              Edited by: 905993 on 04-ene-2012 11:57

              Edited by: 905993 on 05-ene-2012 1:12
              • 4. Re: Problem with error 06502 and cursor
                €$ħ₪
                Then try giving alias to each column in the cursor set
                • 5. Re: Problem with error 06502 and cursor
                  Tubby
                  Well, you've posted a good chunk of code, but failed to post the exact error message (we have no idea what line your code fails at).

                  How about you remove ALL instances of the exception catchers like this
                  905993 wrote:
                  EXCEPTION WHEN OTHERS THEN
                  send_mail('error in cursor', sqlerrm); --this is another procedure that works fine
                  END;
                  And run your procedure, and then copy/paste the error message so we have an idea where your code is actually failing?
                  • 6. Re: Problem with error 06502 and cursor
                    908996
                    The error is ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind and the execution finish. The line is what I said before, the for... cursor loop.
                    • 7. Re: Problem with error 06502 and cursor
                      Tubby
                      905993 wrote:
                      The error is ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind and the execution finish. The line is what I said before, the for... cursor loop.
                      Okay, i'll take your word for that.

                      Please take my word as well. There's no problem with your code ... everything is just fine and okay.

                      If you'd like to post a reproducible test case for us i'd be happy to help you resolve your issue, aside from that i'm done guessing ... maybe someone else will jump in with their crystal ball to help you out.

                      Cheers,
                      • 8. Re: Problem with error 06502 and cursor
                        905993 wrote:
                             I have to say that this procedure have been working fine for years, and it started to fail recently.
                        Then something else had to have changed. Have there been any recent changes to the OS or DB that you know of?

                        If it has worked for years the DB must have been upgraded to a more recent release. What DB release are you on?
                        • 9. Re: Problem with error 06502 and cursor
                          damorgan
                          ora-06502 Bulk Bind Truncated Bind error in the "for example_var in example_cursor loop"
                          The code sample you have provided does not contain a bulk bind. Please provide the real code, the complete database version number, and the full and complete actual error message.
                          • 10. Re: Problem with error 06502 and cursor
                            908996
                            The version of the db is:
                            Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
                            PL/SQL Release 10.2.0.2.0 - Production
                            CORE     10.2.0.2.0     Production
                            TNS for Linux: Version 10.2.0.2.0 - Production
                            NLSRTL Version 10.2.0.2.0 - Production

                            The error is what I put before. In fact, if I put an exception like WHEN VALUE ERROR THEN just after the end loop; , the execution flow goes around it instead of WHEN OTHERS THEN. I'm sure because I was debugging and I can confirm that.

                            I've been trying to find why the code fails (the procedure haven't changed, it's always the same) and I think I've found something. Usually the temporary table wproducts contains products that need to be inserted in the final table mproducts. The procedure is launched for every single date that we can find in the fe_date field of wproducts table. This means that if I have 5000 rows for dates such as '01/01/12', '02/01/10', etc... we will have as many procedure calls as distinct fe_date stored in the wproducts table like that:

                            cursor fe_dates is select distintc date from wproducts; and then for da in fe_dates loop insert_fact(5,da); end loop; --number 5 is the sector, it's fixed.

                            Now, a new kind of group is started to be loaded. Usually the group is 'Passenger Cars' or 'Industrial Vehicles' and might be something like 'Motorcycles' or 'Advertising' but this year we have started to load products with group 'Transportation Institutional Advertising'. The procedure fails when it finds a row with this kind of group, if the temporary table doesn't have any row with this group, it works perfectly but when I insert one row with this group, it fails. I've checked that.

                            The datatypes of the temporary and final table are for the field group a varchar2(50), the others fields match too.

                            Edited by: 905993 on 05-ene-2012 1:14

                            Edited by: 905993 on 05-ene-2012 1:16
                            • 11. Re: Problem with error 06502 and cursor
                              908996
                              I've found the error.

                              I was debugging and I realize that reg1.group datatype is VARCHAR2(30), but I have reg1 declared as cursor products_temp%rowtype, and the field group in the wproducts table (where the cursor makes the select from) is varchar(50), I don't understand why is happening this.

                              wproducts.group varchar2(50)
                              mproducts.group varchar2(50)

                              reg1.group varchar2(30)

                              When the cursor fetchs the group 'Transportation Institutional Advertising' it doesn't fix in the group field of the reg1 variable and it crashes.

                              Why the reg1 products_temp%rowtype doesn't work only with the field group? The rest of the datatype fields in reg1 match with the datatype fields of wproducts.

                              Edited by: 905993 on 05-ene-2012 4:27
                              • 12. Re: Problem with error 06502 and cursor
                                1009376
                                I had a similar problem... fetch from SYS_REFCURSOR, bulk collect into a collection, where one of the fields in the collection was a varchar2(32), but the string returned in the cursor was longer than 32 chars. Solution was to increase to varchar2(64) to match what the proc was returning.
                                • 13. Re: Problem with error 06502 and cursor
                                  BluShadow
                                  Please don't drag up old threads unnecessarily. There are plenty of current issues that people need help with, without having the forums cluttered with old posts.