Home » SQL & PL/SQL » SQL & PL/SQL » Problem with bulk collect limit (Oracle 10.2.0.1.0)
Problem with bulk collect limit [message #278154] Fri, 02 November 2007 02:15 Go to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
DECLARE
     TYPE v_tab_typ1  IS TABLE OF sd_deposit_mst.customerid%TYPE;
     TYPE v_tab_typ2  IS TABLE OF sd_deposit_mst.accountid%TYPE;
     TYPE v_tab_typ3  IS TABLE OF sd_deposit_mst.seq_id%TYPE;
     TYPE v_tab_typ4  IS TABLE OF sd_deposit_mst.branchid%TYPE;
     TYPE v_tab_typ5  IS TABLE OF sd_deposit_mst.schemeid%TYPE;
     TYPE v_tab_typ6  IS TABLE OF sd_deposit_mst.currencyid%TYPE;
     TYPE v_tab_typ13 IS TABLE OF sd_deposit_mst.uplddwld_id%TYPE;
     l_typ_var1                    v_tab_typ1;
     l_typ_var2                    v_tab_typ2;
     l_typ_var3                    v_tab_typ3;
     l_typ_var4                    v_tab_typ4;
     l_typ_var5                    v_tab_typ5;
     l_typ_var6                    v_tab_typ6;
     l_typ_var13                   v_tab_typ13;
     errors                        number (5);
     err_cd                        NUMBER (5);

     CURSOR c1 IS
          SELECT customerid,
                 accountid,
                 seq_id,
                 branchid,
                 schemeid,
                 currencyid,
                 uplddwld_id
          FROM   sd_deposit_mst;
BEGIN
     OPEN c1;

     LOOP
          FETCH c1
          BULK COLLECT INTO l_typ_var1,
                 l_typ_var2,
                 l_typ_var3,
                 l_typ_var4,
                 l_typ_var5,
                 l_typ_var6,
                 l_typ_var13 LIMIT 50;

          DBMS_OUTPUT.put_line ('LIMIT = '||l_typ_var1.COUNT);
          FORALL i IN 1 .. l_typ_var1.COUNT SAVE EXCEPTIONS
               INSERT INTO sd_deposit_hst
                           (customerid,
                            accountid,
                            seq_id,
                            branchid,
                            schemeid,
                            currencyid,
                            uplddwld_id
                           )
               VALUES      (l_typ_var1 (i),
                            l_typ_var2 (i),
                            l_typ_var3 (i),
                            l_typ_var4 (i),
                            l_typ_var5 (i),
                            l_typ_var6 (i),
                            l_typ_var13 (i)
                           );
     END LOOP;

     CLOSE c1;
EXCEPTION
     WHEN OTHERS THEN
          ERRORS := SQL%BULK_EXCEPTIONS.COUNT;
          DBMS_OUTPUT.PUT_LINE ('ERRORS =  ' || ERRORS);

          FOR j IN 1 .. ERRORS LOOP
               err_cd := SQL%BULK_EXCEPTIONS (j).ERROR_INDEX;
               DBMS_OUTPUT.put_line (l_typ_var3 (err_cd));
          END LOOP;
END;


There are 100000 record in table SD_DEPOSIT_MST
when I insert the data from SD_DEPOSIT_MST into SD_DEPOSIT_HST
then bulk exception limit terminates with limit = 50.

Not inserting all rows and not returning all the errors.


The output is like 



LIMIT = 50
ERRORS =  8
2401588
2401590
2401592
2401601
2401602
2401603
2401604
2401622



Please help

Regards
Trivendra



Re: Problem with bulk collect limit [message #278158 is a reply to message #278154] Fri, 02 November 2007 02:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It would help if your exception block actually told you what the error it had hit was.

Change it to do that and get back to us with this extra piece of information.
Re: Problem with bulk collect limit [message #278159 is a reply to message #278158] Fri, 02 November 2007 02:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oh, and why are you doing this awkward Bulk Collect/Forall Insert when you could just do:
INSERT INTO sd_deposit_hst
                           (customerid,
                            accountid,
                            seq_id,
                            branchid,
                            schemeid,
                            currencyid,
                            uplddwld_id
                           )
SELECT customerid,
       accountid,
       seq_id,
       branchid,
       schemeid,
       currencyid,
       uplddwld_id
FROM   sd_deposit_mst;

Re: Problem with bulk collect limit [message #278161 is a reply to message #278154] Fri, 02 November 2007 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With slight modifications (adding a condition to get out of loop and closing the cursor in exception block), it works for me:
SQL> create table t1 (id integer, val varchar2(10));

Table created.

SQL> create table t2 (id integer, val number);

Table created.

SQL> insert into t1 
  2    select level, decode(mod(level,3),0,chr(ascii('A')+level),level) 
  3    from dual connect by level <= 10
  4  /

10 rows created.

SQL> select * from t1 order by id;
        ID VAL
---------- ----------
         1 1
         2 2
         3 D
         4 4
         5 5
         6 G
         7 7
         8 8
         9 J
        10 10

10 rows selected.

SQL> declare
  2    type t_id is table of t1.id%type;
  3    type t_val1 is table of t1.val%type;
  4    l_id  t_id;
  5    l_val t_val1;
  6    cursor c1 is select id, val from t1;
  7    errors pls_integer;
  8    err_cd pls_integer;
  9  begin
 10    open c1;
 11    loop
 12      fetch c1 bulk collect into l_id, l_val limit 50;
 13      dbms_output.put_line('LIMIT='||l_id.count);
 14      forall i in 1..l_id.count save exceptions
 15        insert into t2 (id, val) values (l_id(i), l_val(i));
 16      errors := sql%bulk_exceptions.count;
 17      exit when c1%notfound;
 18    end loop;
 19    close c1;
 20  exception
 21    when others then
 22      errors := sql%bulk_exceptions.count;
 23      dbms_output.put_line ('ERRORS='||errors);
 24      for j in 1..errors loop
 25        err_cd := sql%bulk_exceptions(j).error_index;
 26        dbms_output.put_line (
 27          'ID='||l_id(err_cd)||' error='||sqlerrm(-1*sql%bulk_exceptions(j).error_code));
 28      end loop;
 29      if c1%isopen then close c1; end if;
 30  end;
 31  /
LIMIT=10
ERRORS=3
ID=3 error=ORA-01722: invalid number
ID=6 error=ORA-01722: invalid number
ID=9 error=ORA-01722: invalid number

PL/SQL procedure successfully completed.

SQL> select * from t2 order by id;
        ID        VAL
---------- ----------
         1          1
         2          2
         4          4
         5          5
         7          7
         8          8
        10         10

7 rows selected.

But you can do the same thing in a whole with an insert select with LOG ERRORS clause.

Regards
Michel
Re: Problem with bulk collect limit [message #278169 is a reply to message #278154] Fri, 02 November 2007 03:58 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks Michel,

Your table T1 has 10 records

The same problem remains when I make LIMT=5 in your block.


DECLARE
   TYPE t_id IS TABLE OF t1.ID%TYPE;
   TYPE t_val1 IS TABLE OF t1.val%TYPE;
   l_id                t_id;
   l_val               t_val1;

   CURSOR c1 IS
      SELECT ID,val FROM t1;
   ERRORS              PLS_INTEGER;
   err_cd              PLS_INTEGER;
BEGIN
   OPEN c1;
   LOOP
      FETCH c1
      BULK COLLECT INTO l_id,
             l_val LIMIT 5;

      DBMS_OUTPUT.put_line ('LIMIT=' || l_id.COUNT);
      FORALL i IN 1 .. l_id.COUNT SAVE EXCEPTIONS
         INSERT INTO t2(ID,val)
              VALUES (l_id (i),l_val (i));
      ERRORS         := SQL%BULK_EXCEPTIONS.COUNT;
      EXIT WHEN c1%NOTFOUND;
   END LOOP;

   CLOSE c1;
EXCEPTION
   WHEN OTHERS THEN
      ERRORS         := SQL%BULK_EXCEPTIONS.COUNT;
      DBMS_OUTPUT.put_line ('ERRORS=' || ERRORS);

      FOR j IN 1 .. ERRORS LOOP
         err_cd         := SQL%BULK_EXCEPTIONS (j).ERROR_INDEX;
         DBMS_OUTPUT.put_line ('ID=' || l_id (err_cd) || ' error='
		                             || SQLERRM (-1 * SQL%BULK_EXCEPTIONS (j).ERROR_CODE));
      END LOOP;
	  
	  IF c1%ISOPEN THEN
         CLOSE c1;
      END IF;
END;

The Output is 

LIMIT=5
ERRORS=1
ID=3 error=ORA-01722: invalid number



SQL> select * from t2;

        ID        VAL
---------- ----------
         1          1
         2          2
         4          4
         5          5

The total row inserted are 4.

SQL> select count(*) from t2;

  COUNT(*)
----------
         4



Error Log table will be common to every main table with specific columns as there will be many table( May be 100) which I will use in Exception block;

Here DBMS_ERRLOG will not useful as it will make an error table foreach table.
Re: Problem with bulk collect limit [message #278180 is a reply to message #278169] Fri, 02 November 2007 05:18 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Please provide the output for the following.
DESC t1
DESC t2
SELECT *
  FROM t1;
By
Vamsi
Re: Problem with bulk collect limit [message #278182 is a reply to message #278180] Fri, 02 November 2007 05:20 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Please see Michel example above.
Re: Problem with bulk collect limit [message #278189 is a reply to message #278182] Fri, 02 November 2007 05:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your problem is that the error ORA-24381 is raised by the FORALL statement every time you execute it and there are errors in the DML.

If you'd added code to your exception block like I told you to, you'd have seen this yourself.

To fix it, you need to wrap the FORALL in an exception handler and catch and handle this behaviour.

declare
  type t_id is table of t1.id%type;
  type t_val1 is table of t1.val%type;
  l_id  t_id;
  l_val t_val1;
  cursor c1 is select id, val from t1;
  errors pls_integer;
  err_cd pls_integer;
  
  e_forall_error   exception;
  pragma exception_init(e_forall_error, -24381);
begin
  delete t2;
  open c1;
  loop
    fetch c1 bulk collect into l_id, l_val limit 5;
    dbms_output.put_line('LIMIT='||l_id.count);
    begin
      forall i in 1..l_id.count save exceptions
        insert into t2 (id, val) values (l_id(i), l_val(i));
      errors := errors + sql%bulk_exceptions.count;
    exception
      when e_forall_error then
        dbms_output.put_line('Error '||sqlerrm);
        errors := sql%bulk_exceptions.count;
        dbms_output.put_line ('ERRORS='||errors);
        for j in 1..errors loop
          err_cd := sql%bulk_exceptions(j).error_index;
          dbms_output.put_line ('ID='||l_id(err_cd)||' error='||sqlerrm(-1*sql%bulk_exceptions(j).error_code));
        end loop;      
      end;
    exit when c1%notfound;
  end loop;
  close c1;
end;
/
Output:

LIMIT=5
Error ORA-24381: error(s) in array DML
ERRORS=1
ID=3 error=ORA-01722: invalid number
LIMIT=5
Error ORA-24381: error(s) in array DML
ERRORS=2
ID=6 error=ORA-01722: invalid number
ID=9 error=ORA-01722: invalid number
LIMIT=0
Re: Problem with bulk collect limit [message #278218 is a reply to message #278154] Fri, 02 November 2007 07:24 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Thanks JRowbottom
Smile

This code really works fine

Regards
Trivendra

Previous Topic: ORA-12053: this is not a valid nested materialized view
Next Topic: How to change the format
Goto Forum:
  


Current Time: Mon Dec 05 05:04:01 CST 2016

Total time taken to generate the page: 0.08352 seconds