|
|
| Re: How to use %rowcount [message #129750 is a reply to message #129680] |
Tue, 26 July 2005 09:01   |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
One option is to include a rownum r in your select list of your query and use that as your "loop counter".
You can also see this:
| Quote: |
%ROWCOUNT Attribute: How Many Rows Affected So Far?
%ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0 if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. In the following example, you use %ROWCOUNT to take action if more than ten rows have been deleted:
DELETE FROM emp WHERE ...
IF SQL%ROWCOUNT > 10 THEN -- more than 10 rows were deleted
...
END IF;
If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query.
Guidelines for Using Implicit Cursor Attributes
The values of the cursor attributes always refer to the most recently executed SQL statement, wherever that statement is. It might be in a different scope (for example, in a sub-block). To save an attribute value for later use, assign it to a Boolean variable immediately. Doing other operations, such as procedure calls, might change the value of %NOTFOUND before you can test it.
The %NOTFOUND attribute is not useful in combination with the SELECT INTO statement:
If a SELECT INTO statement fails to return a row, PL/SQL raises the predefined exception NO_DATA_FOUND immediately, interrupting the flow of control before you can check %NOTFOUND.
A SELECT INTO statement that calls a SQL aggregate function always returns a value or a null. After such a statement, the %NOTFOUND attribute is always FALSE, so checking it is unnecessary.
|
[Updated on: Tue, 26 July 2005 09:36] Report message to a moderator |
|
|
|
|
|
|
|
| Re: How to use %rowcount [message #130089 is a reply to message #129750] |
Thu, 28 July 2005 02:42 |
soni_7
Messages: 33
Registered: July 2005
|
Member |
|
|
Hi smartin,
I would like to know what number is returned for the %rowcount in case of select into returning more than 1 row. You have mentioned it as 1. As per me , its 2 . Can you get this clarified.
Regards
Soni
|
|
|
|
| Re: How to use %rowcount [message #130151 is a reply to message #129680] |
Thu, 28 July 2005 08:02 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
That is a bit of a special situation, because with select into you are only expected to have one row. If you have more than one, it throws an exception. From the docs quoted above:
| Quote: |
If a SELECT INTO statement returns more than one row, PL/SQL raises the predefined exception TOO_MANY_ROWS and %ROWCOUNT yields 1, not the actual number of rows that satisfy the query.
|
And in case you don't believe the docs (or would like to see it for yourself):
MYDBA@ORCL >
MYDBA@ORCL > create table test(a number);
Table created.
MYDBA@ORCL > insert into test values (1);
1 row created.
MYDBA@ORCL > insert into test values (2);
1 row created.
MYDBA@ORCL > commit;
Commit complete.
MYDBA@ORCL >
MYDBA@ORCL > declare
2 l_a number;
3 begin
4 select a into l_a from test;
5 dbms_output.put_line('After the select, rowcount is: ' || sql%rowcount);
6 exception
7 when too_many_rows then
8 dbms_output.put_line('Too Many Rows Exception, rowcount is: ' || sql%rowcount);
9 end;
10 /
Too Many Rows Exception, rowcount is: 1
PL/SQL procedure successfully completed.
MYDBA@ORCL >
MYDBA@ORCL > drop table test;
Table dropped.
MYDBA@ORCL >
MYDBA@ORCL > set echo off;
MYDBA@ORCL >
|
|
|
|
| Re: How to use %rowcount [message #130293 is a reply to message #130151] |
Fri, 29 July 2005 03:14 |
soni_7
Messages: 33
Registered: July 2005
|
Member |
|
|
Hi,
What you have given is true. It works out to give 1 only , not 2.
But i have read in oracle 9i complete reference book that it should be 2. Just check this out and please explain.If you have the pdf file of the same , do check page no.850.
Thanks in advance.
|
|
|
|
| Re: How to use %rowcount [message #133120 is a reply to message #129680] |
Wed, 17 August 2005 14:55 |
bella13
Messages: 90
Registered: July 2005
|
Member |
|
|
Ok..so for a select..into we could evaluate how many rows are returned by using sql%rowcount.
how about a regular select statement. In a procedure i have a am calling a ref cursor...and defining a select for that cursor.
..
Begin
..
open my_refcur For
select ...
from abc ;
exception
when no_data_found
...
end;
/
i have noticed if no rows were returned by the above query ..it does not trigger the no_data_found.
sql%rowcount does not work too
How do we evaluate in that case?
thnx
|
|
|
|
| Re: How to use %rowcount [message #133123 is a reply to message #133120] |
Wed, 17 August 2005 15:02 |
bella13
Messages: 90
Registered: July 2005
|
Member |
|
|
ok..
i made a typo. if i add the below code
If my_refcursor%rowcount = 0 then
raise no_data_found;
end if;
however without the %rowcount code it will never trigger when no data found. is there any other method of finding out if no rows were returned.
Thnx
|
|
|
|
| Re: How to use %rowcount [message #133134 is a reply to message #129680] |
Wed, 17 August 2005 15:56 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'm sure this is all documented, but what the heck:
MYDBA@ORCL >
MYDBA@ORCL > start cursor_attributes;
MYDBA@ORCL >
MYDBA@ORCL > create table test(a number);
Table created.
MYDBA@ORCL > insert into test values (1);
1 row created.
MYDBA@ORCL > commit;
Commit complete.
MYDBA@ORCL >
MYDBA@ORCL > declare
2 rc sys_refcursor;
3 x test%rowtype;
4 begin
5 dbms_output.put_line('BEFORE DOING ANYTHING');
6
7 if rc%isopen is null then
8 dbms_output.put('cursor status is NULL ');
9 elsif rc%isopen = true then
10 dbms_output.put('cursor status is OPEN ');
11 elsif rc%isopen = false then
12 dbms_output.put('cursor status is CLOSED ');
13 end if;
14
15 begin
16 if rc%notfound is null then
17 dbms_output.put('notfound is NULL ');
18 elsif rc%notfound = true then
19 dbms_output.put('notfound is TRUE ');
20 elsif rc%notfound = false then
21 dbms_output.put('notfound is FALSE ');
22 end if;
23 exception when invalid_cursor then
24 dbms_output.put('cursor exception on notfound ');
25 end;
26
27 begin
28 if rc%rowcount is null then
29 dbms_output.put_line('rowcount is NULL');
30 else
31 dbms_output.put_line('rowcount is ' || rc%rowcount);
32 end if;
33 exception when invalid_cursor then
34 dbms_output.put_line('cursor exception on rowcount');
35 end;
36
37 open rc for 'select * from test where a = 2';
38
39 dbms_output.put_line('AFTER DOING OPEN');
40
41 if rc%isopen is null then
42 dbms_output.put('cursor status is NULL ');
43 elsif rc%isopen = true then
44 dbms_output.put('cursor status is OPEN ');
45 elsif rc%isopen = false then
46 dbms_output.put('cursor status is CLOSED ');
47 end if;
48
49 if rc%notfound is null then
50 dbms_output.put('notfound is NULL ');
51 elsif rc%notfound = true then
52 dbms_output.put('notfound is TRUE ');
53 elsif rc%notfound = false then
54 dbms_output.put('notfound is FALSE ');
55 end if;
56
57 if rc%rowcount is null then
58 dbms_output.put_line('rowcount is NULL');
59 else
60 dbms_output.put_line('rowcount is ' || rc%rowcount);
61 end if;
62
63 fetch rc into x;
64
65 dbms_output.put_line('AFTER DOING A SINGLE FETCH RETURNING NO ROWS');
66
67 if rc%isopen is null then
68 dbms_output.put('cursor status is NULL ');
69 elsif rc%isopen = true then
70 dbms_output.put('cursor status is OPEN ');
71 elsif rc%isopen = false then
72 dbms_output.put('cursor status is CLOSED ');
73 end if;
74
75 if rc%notfound is null then
76 dbms_output.put('notfound is NULL ');
77 elsif rc%notfound = true then
78 dbms_output.put('notfound is TRUE ');
79 elsif rc%notfound = false then
80 dbms_output.put('notfound is FALSE ');
81 end if;
82
83 if rc%rowcount is null then
84 dbms_output.put_line('rowcount is NULL');
85 else
86 dbms_output.put_line('rowcount is ' || rc%rowcount);
87 end if;
88
89 close rc;
90
91 dbms_output.put_line('AFTER DOING CLOSE');
92
93 if rc%isopen is null then
94 dbms_output.put('cursor status is NULL ');
95 elsif rc%isopen = true then
96 dbms_output.put('cursor status is OPEN ');
97 elsif rc%isopen = false then
98 dbms_output.put('cursor status is CLOSED ');
99 end if;
100
101 begin
102 if rc%notfound is null then
103 dbms_output.put('notfound is NULL ');
104 elsif rc%notfound = true then
105 dbms_output.put('notfound is TRUE ');
106 elsif rc%notfound = false then
107 dbms_output.put('notfound is FALSE ');
108 end if;
109 exception when invalid_cursor then
110 dbms_output.put('cursor exception on notfound ');
111 end;
112
113 begin
114 if rc%rowcount is null then
115 dbms_output.put_line('rowcount is NULL');
116 else
117 dbms_output.put_line('rowcount is ' || rc%rowcount);
118 end if;
119 exception when invalid_cursor then
120 dbms_output.put_line('cursor exception on rowcount');
121 end;
122
123 end;
124 /
BEFORE DOING ANYTHING
cursor status is CLOSED cursor exception on notfound cursor exception on rowcount
AFTER DOING OPEN
cursor status is OPEN notfound is NULL rowcount is 0
AFTER DOING A SINGLE FETCH RETURNING NO ROWS
cursor status is OPEN notfound is TRUE rowcount is 0
AFTER DOING CLOSE
cursor status is CLOSED cursor exception on notfound cursor exception on rowcount
PL/SQL procedure successfully completed.
MYDBA@ORCL >
MYDBA@ORCL > drop table test;
Table dropped.
MYDBA@ORCL > set echo off;
MYDBA@ORCL >
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
