sql%rowcount not working as expected. 0 rowcount? [message #287256] |
Tue, 11 December 2007 13:35  |
myother
Messages: 5 Registered: March 2007
|
Junior Member |
|
|
create or replace procedure mias ( i_region_id in region.region_id%TYPE )
IS
BEGIN
EXECUTE IMMEDIATE
'select * from region where region_id > :a '
using i_region_id;
dbms_output.put_line(sql%rowcount);
dbms_output.put_line(i_region_id);
END;
/
SQL> exec mias(2);
0
2
PL/SQL procedure successfully completed.
SQL>
SQL> select region_id from region;
REGION_ID
----------
0
1
2
3
4
5
6
7
8
9
10
REGION_ID
----------
11
12
13
14
31
32
33
34
35
39
21 rows selected.
SQL>
|
|
|
|
|
|
|
Re: sql%rowcount not working as expected. 0 rowcount? [message #287263 is a reply to message #287262] |
Tue, 11 December 2007 14:15   |
pablolee
Messages: 2882 Registered: May 2007 Location: Scotland
|
Senior Member |
|
|
MarcL wrote on Tue, 11 December 2007 20:01 | What you have done is compile your procedure.
To execute it from sqlplus, assuming there are no compilation errors:
|
myother wrote in his 1st post | exec mias(2);
|
Maybe if he'd formatted his post it would have been easier to spot. As usual, Michel has hit the nail on the head.
|
|
|
|
|
Re: sql%rowcount not working as expected. 0 rowcount? [message #287363 is a reply to message #287271] |
Wed, 12 December 2007 01:35   |
 |
Maaher
Messages: 7065 Registered: December 2001
|
Senior Member |
|
|
myother wrote on Tue, 11 December 2007 21:56 | Is there anyway to know if rows are returned by the cursor without a loop? Are there rows returned by executing the stored procedure without a loop?
|
pablolee wrote on Tue, 11 December 2007 22:02 | Yes, do a fetch, as has already been mentioned. What EXACTLY is it that you are trying to do?
|
I think pablolee means something like:
CREATE OR REPLACE PROCEDURE mias ( i_region_id in regions.region_id%TYPE )
IS
TYPE t_region IS TABLE OF regions%ROWTYPE;
v_t_region t_region;
v_rcnt PLS_INTEGER;
v_sql VARCHAR2(100);
v_refcur INTEGER;
BEGIN
EXECUTE IMMEDIATE 'select * from regions where region_id > :a '
BULK COLLECT INTO v_t_region
USING i_region_id;
-- use the pl/sql count instead of the sql rowcount:
dbms_output.put_line(v_t_region.COUNT);
dbms_output.put_line(i_region_id);
END;
/
sho err Dynamic SQL means that you pass a SQL string to the SQL engine and let the SQL engine return the result set. The SQL%ROWCOUNT you're printing is the one from the PL/SQL side. It has no idea what string you passed to the SQL engine via NDS.
My advice? Avoid dynamic SQL if you can.
MHE
[Updated on: Wed, 12 December 2007 01:35] Report message to a moderator
|
|
|
|
|
|