6 Replies Latest reply on Nov 24, 2006 1:51 AM by 543662

    ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

    436317
      Hi,
      does anyone know whether a FORALL with SAVE EXCEPTIONS have limitations
      on how many exceptions can be saved? Does that depend on to PGA size?
      We want to insert between 1 and 6 Mio. rows.

      Greetings Rob
        • 1. Re: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
          543662
          If you like to insert..say 10 million of rows... you should do it in parts.

          Eg.:

          DECLARE
          CURSOR cur IS
          SELECT here;
          BEGIN
          OPEN cur;
          FETCH cur BULK COLLECT INTO t_array LIMIT 100 ;
          BEGIN
          FORALL i IN 1..t_array.LAST SAVE EXCEPTIONS
          INSERT here;
          EXCEPTION
          .....
          END;
          COMMIT;
          EXIT WHEN cur%NOTFOUND;
          END;
          /

          Do it in this performance way and forget about the limitations of the SAVE EXCEPTIONS.
          • 2. Re: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
            457460
            You can use an error logging table (as of 10g I think) :

            INSERT INTO targettable
            SELECT ... FROM sourcetable
            LOG ERRORS INTO errlogtable REJECT LIMIT ...

            for a complete example: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BCEGDJDJ

            Should be better for performance reasons than trying to do it in plsql.
            • 3. Re: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
              436317
              Thanks for your replies, but i think you've misunderstood. I am using FORALL ....
              SAVE EXCEPTIONS, but when i execute it i get those ORA-06502 message!
              In another thread i've read that for these exceptions a collection is used. My question is whether there is a maximum for this collection.
              We're using 10.1.0.4 (forgot to mention that).
              • 4. Re: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
                543662
                The maximum of the collection depends of your PGA memory.

                My recomendation is to do it with a CURSOR and BULK COLLECT with the LIMIT clause.
                • 5. Re: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
                  436317
                  Hi Leonardo,
                  this is how we execute it:

                  OPEN curCustomer;
                  LOOP
                  FETCH curCustomer BULK COLLECT
                  INTO VarCustomer LIMIT lLimit; <== 1000
                  BEGIN
                  FORALL i IN 1 .. VarCustomer.COUNT
                  SAVE EXCEPTIONS
                  INSERT INTO CUSTOMER VALUES VarCustomer(i);
                  lCount := lCount + VarCustomer.COUNT;
                  COMMIT;
                  EXCEPTION WHEN OTHERS THEN
                  errors := SQL%BULK_EXCEPTIONS.COUNT;
                  FOR i IN 1..errors LOOP
                  error_mesg :=
                  SQLERRM(-SQL%BULK_EXCEPTION(i).ERROR_CODE);
                  G99PR_ERROR_HANDLING('G10PA_IMPORT.InsertCustomer',
                  'CUSTOMER', SQLCODE, error_mesg,0);
                  END LOOP;
                  lCount := lCount + VarCustomer.COUNT - errors;
                  END;
                  EXIT WHEN curCustomer%NOTFOUND;
                  END LOOP;
                  CLOSE curCustomer;

                  After approximately 8000 exceptions (avg. rowlength 305) the error appears.
                  • 6. Re: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
                    543662
                    You say that when you execute the code, you get the ORA-06502 error.

                    ORA-06502:     PL/SQL: numeric or value error string
                    Cause:     An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
                    Action:     Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

                    What is the procedure G99PR_ERROR_HANDLING?? Why do you pass to this procedure the parameter SQLCODE? If you like the error code of the FORALL you must pass -SQL%BULK_EXCEPTION(i).ERROR_CODE.