Home » SQL & PL/SQL » SQL & PL/SQL » sql%rowcount not working in FORALL
sql%rowcount not working in FORALL [message #190862] Fri, 01 September 2006 10:42 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have:
create table t (obj_nm varchar2(100));

then:
declare
   type c_tab is table of varchar2(100);
   c_array   c_tab;

begin

  select owner
    bulk collect into c_array
    from all_objects;

  dbms_output.put_line (c_array.count);
    
  forall i in 1..c_array.count 
      insert into t 
      values (c_array(i));
    
  commit; 
  dbms_output.put_line ('rowcount '||sql%rowcount);

end;

This displays:
21309
rowcount 0

Why is the rowcount 0?? I checked the table, it has 21309 records!
icon9.gif  Re: sql%rowcount not working in FORALL [message #190865 is a reply to message #190862] Fri, 01 September 2006 11:04 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
When I interchange:
  commit; 
  dbms_output.put_line ('rowcount '||sql%rowcount);

It works:
declare
 
   type c_tab is table of varchar2(100);
   c_array   c_tab;
 
begin
  select owner
    bulk collect into c_array
    from all_objects;

  dbms_output.put_line (c_array.count);
    
  forall i in 1..c_array.count 
      insert into t 
      values (c_array(i));

  dbms_output.put_line ('rowcount '||sql%rowcount);   
  commit; 

end;


But it doesn't work in my program...:
declare

      type typ_col1_tab is table of A.col1%type;
      type typ_col2_id_tab is table of A.col2%type;

      v_col1_tab         typ_col1_tab;
      v_col2_tab         typ_col2_tab;
      v_count            number; 

  begin

      select col1, col2
        bulk collect into v_col1_tab, v_col2_tab  
        from A a
       where not exists (select 1
                           from B b
                          where a.col1 = b.col1
                            and a.col2 = b.col2); 
                            
                                  
      begin

          forall i in 1..v_col1_tab.count save exceptions
              insert into C
              ( my_id,
                col1,
                col2
              )
              values 
              ( my_seq.nextval,
                v_col1_tab(i),
                v_col2_tab(i)
              );

      v_count := sql%rowcount;
      dbms_output.put_line ('rowcount '||sql%rowcount); -- THIS DISPLAYS 0!! though there are inserted records..
      COMMIT;
      
      exception
           when others then
               v_error_count := sql%bulk_exceptions.count;
               for j in 1..v_error_count
               loop
                   if sql%bulk_exceptions(j).error_code = 1 then null;
                      dbms_output.put_line('Error = 1');
                   else
                      rollback;
                      dbms_output.put_line
                        ('Failed at col1 = ' ||               
                          v_col1_tab(sql%bulk_exceptions(j).error_index)||
                         ' with col2 = '||
                         v_col2_tab(sql%bulk_exceptions(j).error_index)|| 
                         ' - '||sql%bulk_exceptions(j).error_code);
                  end if;
               end loop;
      end;
 
 end;

[Updated on: Fri, 01 September 2006 11:20]

Report message to a moderator

Re: sql%rowcount not working in FORALL [message #190983 is a reply to message #190865] Sun, 03 September 2006 20:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
Sql%rowcount only applies to the last statemet. Once you commit, it is reset. The last code that you posted contains variable name mismatches and missing variables. Can you post an actual copy and paste of a run including the error message? Can you test with some simplified tables as shown below? Could it be that your code is going to your exception section?

SCOTT@10gXE> create table a
  2    (col1 number,
  3  	col2 number)
  4  /

Table created.

SCOTT@10gXE> insert all
  2  into a values (1, 2)
  3  into a values (3, 4)
  4  select * from dual
  5  /

2 rows created.

SCOTT@10gXE> create table b as
  2  select * from a where 1 = 2
  3  /

Table created.

SCOTT@10gXE> create table c
  2    (my_Id number,
  3  	col1 number,
  4  	col2 number)
  5  /

Table created.

SCOTT@10gXE> create sequence my_seq
  2  /

Sequence created.

SCOTT@10gXE> declare
  2    type typ_col1_tab is table of A.col1%type;
  3    type typ_col2_tab is table of A.col2%type;
  4    v_col1_tab	    typ_col1_tab;
  5    v_col2_tab	    typ_col2_tab;
  6    v_count		    number;
  7  begin
  8    select col1, col2
  9    bulk collect into v_col1_tab, v_col2_tab
 10    from A a
 11    where not exists (select 1
 12  			 from B b
 13  			 where a.col1 = b.col1
 14  			 and a.col2 = b.col2);
 15    begin
 16  	 forall i in 1..v_col1_tab.count save exceptions
 17  	   insert into C
 18  	     ( my_id,
 19  	       col1,
 20  	       col2
 21  	     )
 22  	   values
 23  	     ( my_seq.nextval,
 24  	       v_col1_tab(i),
 25  	       v_col2_tab(i)
 26  	     );
 27  	 v_count := sql%rowcount;
 28  	 dbms_output.put_line ('rowcount '||sql%rowcount);
 29  	 COMMIT;
 30    end;
 31  end;
 32  /
rowcount 2

PL/SQL procedure successfully completed.

SCOTT@10gXE> 

Re: sql%rowcount not working in FORALL [message #191079 is a reply to message #190983] Mon, 04 September 2006 07:12 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks Barbara. Your code is working. I cannot post my code as the tables are big and lots of columns are involved. But you are right, my code goes to the exception section. I tried to modify your code a bit:
create table a
      (col1 number,
    	col2 number);

 insert all
    into a values (1, 2)
    into a values (3, 4)
    into a values (10, 4.4)
    into a values (3.4, 4.4)    
    select * from dual;

create table b as
    select * from a where 1 = 2;

create table c
      (my_Id number,
    	col1 integer check (col1 < 5),
    	col2 number);

create sequence my_seq; 

When I run the block below, it goes to the exception section because of the value 10 (beyond the check which should be less than 5):
declare
     type typ_col1_tab is table of A.col1%type;
     type typ_col2_tab is table of A.col2%type;
     v_col1_tab	    typ_col1_tab;
     v_col2_tab	    typ_col2_tab;
     v_count		    number;
     v_error_count  number; 
   begin
     select col1, col2
     bulk collect into v_col1_tab, v_col2_tab
     from A a
     where not exists (select 1
   			 from B b
   			 where a.col1 = b.col1
   			 and a.col2 = b.col2);
     begin
   	 forall i in 1..v_col1_tab.count save exceptions
   	   insert into C
   	     ( my_id,
   	       col1,
   	       col2
   	     )
   	   values
   	     ( my_seq.nextval,
   	       v_col1_tab(i),
   	       v_col2_tab(i)
   	     );
   	 v_count := sql%rowcount;
   	 dbms_output.put_line ('rowcount '||sql%rowcount);
   	 COMMIT;

     exception
        when others then
           v_error_count := sql%bulk_exceptions.count;
            dbms_output.put_line ('error! ');
     end;
   end;


This displays:
error!


But still, there are 3 rows inserted in table C. Can sql%rowcount return that, even if it goes to the exception section???
Re: sql%rowcount not working in FORALL [message #191086 is a reply to message #191079] Mon, 04 September 2006 07:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
It goes to the exception section before it ever gets to output the sql%rowcount. If you want it to output the sql%rowcount when it goes to the exception section, then you need to put that in the exception section, as shown below.

SCOTT@10gXE> create table a
  2    (col1 number,
  3  	col2 number)
  4  /

Table created.

SCOTT@10gXE> insert all
  2  into a values (1, 2)
  3  into a values (3, 4)
  4  into a values (10, 4.4)
  5  into a values (3.4, 4.4)
  6  select * from dual
  7  /

4 rows created.

SCOTT@10gXE> create table b as
  2  select * from a where 1 = 2
  3  /

Table created.

SCOTT@10gXE> create table c
  2    (my_Id number,
  3  	col1 integer check (col1 < 5),
  4  	col2 number)
  5  /

Table created.

SCOTT@10gXE> create sequence my_seq
  2  /

Sequence created.

SCOTT@10gXE> declare
  2    type typ_col1_tab is table of A.col1%type;
  3    type typ_col2_tab is table of A.col2%type;
  4    v_col1_tab	    typ_col1_tab;
  5    v_col2_tab	    typ_col2_tab;
  6  begin
  7    select col1, col2
  8    bulk collect into v_col1_tab, v_col2_tab
  9    from A a
 10    where not exists (select 1
 11  			 from B b
 12  			 where a.col1 = b.col1
 13  			 and a.col2 = b.col2);
 14    begin
 15  	 forall i in 1..v_col1_tab.count save exceptions
 16  	   insert into C
 17  	     ( my_id,
 18  	       col1,
 19  	       col2
 20  	     )
 21  	   values
 22  	     ( my_seq.nextval,
 23  	       v_col1_tab(i),
 24  	       v_col2_tab(i)
 25  	     );
 26  	 dbms_output.put_line ('rowcount '||sql%rowcount);
 27  	 COMMIT;
 28    exception
 29  	 when others then
 30  	   dbms_output.put_line ('rowcount '||sql%rowcount);
 31  	   dbms_output.put_line ('errors ' || sql%bulk_exceptions.count);
 32    end;
 33  end;
 34  /
rowcount 3
errors 1

PL/SQL procedure successfully completed.

SCOTT@10gXE> SELECT * FROM C
  2  /

     MY_ID       COL1       COL2
---------- ---------- ----------
         1          1          2
         2          3          4
         3          3        4.4

SCOTT@10gXE>

Re: sql%rowcount not working in FORALL [message #191100 is a reply to message #191086] Mon, 04 September 2006 10:26 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks a lot, Barbara. It works!
Previous Topic: How to call c language function in pl/sql
Next Topic: get the first records in a query
Goto Forum:
  


Current Time: Mon Dec 05 21:20:02 CST 2016

Total time taken to generate the page: 0.11047 seconds