1 2 Назад Вперед 20 Ответы Последний ответ: 23.03.2010 9:09, автор: Asheesh Ambardar

    Bulk collect Error

    Apple
      SQL> ed
      Wrote file afiedt.buf

      1 Declare
      2 Type t_emp_type Is Table Of emp%Rowtype Index By Binary_Integer;
      3 t_emp_tab t_emp_type;
      4 e_bulk Exception;
      5 Pragma Exception_Init(e_bulk, -20000);
      6 Begin
      7 Select * Bulk Collect Into t_emp_tab From emp Order By empno;
      8 Forall i In t_emp_tab.First..t_emp_tab.Last save Exceptions
      9 Insert Into emp_bulk Values t_emp_tab(i);
      10 Exception
      11 When e_bulk Then
      12 For j In 1..Sql%bulk_exceptions.Count
      13 Loop
      14 Dbms_Output.put_Line (t_emp_tab(Sql%bulk_exceptions(j).error_index).empno
      15 || ' , ' ||t_emp_tab(Sql%bulk_exceptions(j).error_index).ename
      16 ||' , (sal ='||t_emp_tab(Sql%bulk_exceptions(j).error_index).Sal
      17 || ' ) :' ||Sqlerrm (-Sql%bulk_exceptions(j).error_code)
      18 );
      19 End Loop;
      20* End;
      SQL> /
      ERROR:
      ORA-24381: error(s) in array DML
      ORA-06512: at line 8

      where i am wrong?
        • 1. Re: Bulk collect Error
          Tubby
          Easier to point you to working examples (in Morgan's Library) than to try to cipher through unformatted code (in the future please use
           tags around your code, and provide your Oracle version, select * from v$version)
          
          
          http://www.morganslibrary.org/reference/array_processing.html                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
          • 2. Re: Bulk collect Error
            Prazy
            What's you DB version?

            I tested on my DB 11gR1 and it worked as it should.
            PRAZY@11gR1> create table emp_bulk as select * from emp where 1=2;
            
            Table created.
            
            Elapsed: 00:00:00.03
            PRAZY@11gR1> ed
            Wrote file afiedt.buf
            
             Declare
            Type t_emp_type Is Table Of emp%Rowtype Index By Binary_Integer;
            t_emp_tab t_emp_type;
            e_bulk Exception;
            Pragma Exception_Init(e_bulk, -20000);
            Begin
            Select * Bulk Collect Into t_emp_tab From emp Order By empno;
            Forall i In t_emp_tab.First..t_emp_tab.Last save Exceptions
            Insert Into emp_bulk Values t_emp_tab(i);
            Exception
            When e_bulk Then
            For j In 1..Sql%bulk_exceptions.Count
            Loop
            Dbms_Output.put_Line (t_emp_tab(Sql%bulk_exceptions(j).error_index).empno
            || ' , ' ||t_emp_tab(Sql%bulk_exceptions(j).error_index).ename
            ||' , (sal ='||t_emp_tab(Sql%bulk_exceptions(j).error_index).Sal
            || ' ) :' ||Sqlerrm (-Sql%bulk_exceptions(j).error_code)
            );
            End Loop;
            End;
            PRAZY@11gR1> /
            
            PL/SQL procedure successfully completed.
            
            Elapsed: 00:00:00.01
            PRAZY@11gR1> select * from emp_bulk;
            
                 EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
            ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
                  7369 SMITH      CLERK           7902 17-DEC-80        800                    20
                  7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
                  7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
                  7566 JONES      MANAGER         7839 02-APR-81       2975                    20
                  7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
                  7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
                  7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
                  7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
                  7839 KING       PRESIDENT            17-NOV-81       5000                    10
                  7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
                  7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
                  7900 JAMES      CLERK           7698 03-DEC-81        950                    30
                  7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
                  7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
            
            14 rows selected.
            
            Elapsed: 00:00:00.00
            PRAZY@11gR1>
            Regards,
            Prazy

            Edited by: Prazy on Mar 22, 2010 5:29 PM
            • 3. Re: Bulk collect Error
              Asheesh Ambardar
              The code snippet attached by you works fine on my db instance (Oracle 10.2.0.3).
              It does not look like a code issue. Please share the table structure for emp_bulk+ .
              There is a good chance, this exception is getting encountered because of some datatype mismatch.

              Cheers,
              AA
              • 4. Re: Bulk collect Error
                Apple
                my db version is 10.2.0.1.0
                • 5. Re: Bulk collect Error
                  Prazy
                  I do not find anything wrong with the code you posted and it should work flawlessly.. But still I do not have 10g to test the code. per Asheesh, he didn't find any issues either on his 10g.

                  And just to cross check.. are the table structures are same between emp and emp_bulk?

                  Also, how many rows you are trying to insert?

                  Regards,
                  Prazy

                  Edited by: Prazy on Mar 23, 2010 11:12 AM
                  • 6. Re: Bulk collect Error
                    Asheesh Ambardar
                    Hi Apple,
                    As requested, please share the structure for emp_bulk+ table.
                    How have you created it?
                    Is it a plain
                    CREATE TABLE emp_bulk AS SELECT * FROM emp WHERE 1=2;
                    OR+
                    It was created differently?

                    Execute the following line, to get the table object structure:
                    desc emp_bulk
                    Cheers,
                    AA
                    • 7. Re: Bulk collect Error
                      009
                      Hi,
                      Refer ORA-24381: error(s) in array DML and go to topic ORA-24381: error(s) in array DML


                      *009*
                      • 8. Re: Bulk collect Error
                        Prazy
                        If the DML is processing more than 65536 (2^16) rows, you would likely to hit by the error that you have specified. This limit has been increased in 10.1.0.2 to 2^32.

                        Hence, use a limit clause in bulk fetch and perform DML.

                        Try this
                        Declare
                        Type t_emp_type Is Table Of emp%Rowtype Index By Binary_Integer;
                        t_emp_tab t_emp_type;
                        e_bulk Exception;
                        Pragma Exception_Init(e_bulk, -20000);
                        cursor c1 is select * from emp Order By empno;
                        Begin
                        open c1;
                        loop
                        fetch c1 bulk collect into t_emp_tab limit 10000;
                        exit when  t_emp_tab.count=0;
                        Forall i In t_emp_tab.First..t_emp_tab.Last save Exceptions
                        Insert Into emp_bulk Values t_emp_tab(i);
                        end loop;
                        close c1;
                        Exception
                        When e_bulk Then
                        For j In 1..Sql%bulk_exceptions.Count
                        Loop
                        Dbms_Output.put_Line (t_emp_tab(Sql%bulk_exceptions(j).error_index).empno
                        || ' , ' ||t_emp_tab(Sql%bulk_exceptions(j).error_index).ename
                        ||' , (sal ='||t_emp_tab(Sql%bulk_exceptions(j).error_index).Sal
                        || ' ) :' ||Sqlerrm (-Sql%bulk_exceptions(j).error_code)
                        );
                        End Loop;
                        End;
                        Regards,
                        Prazy
                        • 9. Re: Bulk collect Error
                          Apple
                          I used 11G version but it showed again these errors.

                          SQL> ed
                          Wrote file afiedt.buf

                          1 declare
                          2 type emp_type is table of emp%rowtype index by binary_integer;
                          3 emp_tab emp_type;
                          4 e_bulk exception;
                          5 Pragma Exception_Init(e_bulk, -20000);
                          6 begin
                          7 select * bulk collect into emp_tab from emp order by empno;
                          8 forall i in emp_tab.first..emp_tab.last save exceptions
                          9 insert into emp_test values emp_tab(i);
                          10 exception
                          11 when e_bulk then
                          12 for j in 1..sql%bulk_exceptions.count
                          13 loop
                          14 dbms_output.put_line (emp_tab(sql%bulk_exceptions(j).error_index).empno
                          ;
                          15 /* || ' --' ||
                          16 emp_tab(sql%bulk_exceptions(j).error_index).ename
                          || ' -- ' ||
                          17 '(sal = ' ||emp_tab(sql%bulk_exceptions(j).error_index)
                          sal || ':)' ||
                          18 sqlerrm(-sql%bulk_exceptions(j).error_code)
                          19 );*/
                          20 end loop;
                          21* end;
                          22 /
                          ERROR:
                          ORA-24381: error(s) in array DML
                          ORA-06512: at line 8


                          SQL> desc version
                          ERROR:
                          ORA-04043: object version does not exist


                          SQL> desc V$version
                          Name
                          Null? Type
                          ------------------------------------------------------------------------------
                          ---- -------- --------------------------------------------------------
                          BANNER
                          VARCHAR2(80)

                          SQL> select * from v$version
                          2 /

                          BANNER
                          -------------------------------------------------------------------------------

                          Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
                          PL/SQL Release 11.1.0.7.0 - Production
                          CORE 11.1.0.7.0 Production
                          TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
                          NLSRTL Version 11.1.0.7.0 - Production
                          • 10. Re: Bulk collect Error
                            Prazy
                            Have you tried the code that I specified above? if so what is the result?

                            And, use \
                             pair tags to cover-up your code to escape auto format.
                            
                            Regards,
                            Prazy                                                                                                                                                                                                                                                                                                                                                
                            • 11. Re: Bulk collect Error
                              Apple
                              i used your query also but it showed error
                              • 12. Re: Bulk collect Error
                                Apple
                                i created emp_bulk without records same in emp(21 records), i checked on condition sal > 1500 thats all
                                • 13. Re: Bulk collect Error
                                  Prazy
                                  Hi,

                                  can you do this and post the result in \
                                   tag
                                  desc emp;

                                  desc emp_bulk;

                                  select count(*) from emp;

                                  select count(*) from emp_bulk;
                                  Regards,
                                  Prazy                                                                                                                                                                                                                                                                                                                                                                                    
                                  • 14. Re: Bulk collect Error
                                    Apple
                                    without check constraint it is working fine. but main purpose to handle exception using bulk collect.


                                    SQL> desc emp
                                    Name Null? Type
                                    ----------------------------------------- -------- --------------------

                                    EMPNO NOT NULL NUMBER(4)
                                    ENAME VARCHAR2(10)
                                    JOB VARCHAR2(9)
                                    MGR NUMBER(4)
                                    HIREDATE DATE
                                    SAL NUMBER(7,2)
                                    COMM NUMBER(7,2)
                                    DEPTNO NUMBER(2)


                                    SQL> desc emp_test
                                    Name Null? Type
                                    ----------------------------------------- -------- ------------------

                                    EMPNO NOT NULL NUMBER(4)
                                    ENAME VARCHAR2(10)
                                    JOB VARCHAR2(9)
                                    MGR NUMBER(4)
                                    HIREDATE DATE
                                    SAL NUMBER(7,2)
                                    COMM NUMBER(7,2)
                                    DEPTNO NUMBER(2)



                                    SQL> alter table emp_test
                                    2 modify check (sal>1000);

                                    if i removed these check constraint is working fine. but it must for me to handle exceptions
                                    1 2 Назад Вперед