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  |
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!
|
|
|
|
 Re: sql%rowcount not working in FORALL [message #190865 is a reply to message #190862] |
Fri, 01 September 2006 11:04  |
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 |
 |
Barbara Boehmer
Messages: 9100
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 |
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:
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 |
|
Barbara Boehmer
Messages: 9100
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>
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Nov 16 08:22:31 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
