Home » SQL & PL/SQL » SQL & PL/SQL » sql%rowcount not working as expected. 0 rowcount?
sql%rowcount not working as expected. 0 rowcount? [message #287256] Tue, 11 December 2007 13:35 Go to next message
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 #287257 is a reply to message #287256] Tue, 11 December 2007 13:40 Go to previous messageGo to next message
myother
Messages: 5
Registered: March 2007
Junior Member
I've also tried it this way:

create or replace procedure mias (i_region_id number)
as
TYPE name_num_rc IS REF CURSOR;
cv name_num_rc;
BEGIN

OPEN cv FOR

select * from region
;
dbms_output.put_line(cv%rowcount);
dbms_output.put_line(sql%rowcount);

END;
/
Re: sql%rowcount not working as expected. 0 rowcount? [message #287260 is a reply to message #287256] Tue, 11 December 2007 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL%ROWCOUNT is set at fetch time not at open one.
As you didn't fetch anything it is normal to get 0.

Regards
Michel

[Updated on: Tue, 11 December 2007 13:55]

Report message to a moderator

Re: sql%rowcount not working as expected. 0 rowcount? [message #287261 is a reply to message #287257] Tue, 11 December 2007 14:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Section Managing Cursors in PL/SQL
Subsection %ROWCOUNT Attribute: How Many Rows Affected So Far?

Regards
Michel
Re: sql%rowcount not working as expected. 0 rowcount? [message #287262 is a reply to message #287257] Tue, 11 December 2007 14:01 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
What you have done is compile your procedure.

To execute it from sqlplus, assuming there are no compilation errors:

exec mias(parameter);


Re: sql%rowcount not working as expected. 0 rowcount? [message #287263 is a reply to message #287262] Tue, 11 December 2007 14:15 Go to previous messageGo to next message
pablolee
Messages: 2836
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:

exec mias(parameter);




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 #287269 is a reply to message #287256] Tue, 11 December 2007 14:56 Go to previous messageGo to next message
myother
Messages: 5
Registered: March 2007
Junior Member
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?
Re: sql%rowcount not working as expected. 0 rowcount? [message #287271 is a reply to message #287269] Tue, 11 December 2007 15:02 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Yes, do a fetch, as has already been mentioned. What EXACTLY is it that you are trying to do?
Re: sql%rowcount not working as expected. 0 rowcount? [message #287363 is a reply to message #287271] Wed, 12 December 2007 01:35 Go to previous messageGo to next message
Maaher
Messages: 7062
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

Re: sql%rowcount not working as expected. 0 rowcount? [message #287512 is a reply to message #287256] Wed, 12 December 2007 07:55 Go to previous messageGo to next message
myother
Messages: 5
Registered: March 2007
Junior Member
I want the stored procedure to return the result set to a java program.
Re: sql%rowcount not working as expected. 0 rowcount? [message #287514 is a reply to message #287512] Wed, 12 December 2007 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Return a REF CURSOR.

Regards
Michel
Re: sql%rowcount not working as expected. 0 rowcount? [message #287539 is a reply to message #287256] Wed, 12 December 2007 10:01 Go to previous message
myother
Messages: 5
Registered: March 2007
Junior Member
Thanks for everyones help. I am able to move forward.
Previous Topic: printing data in a specified format using utl_file
Next Topic: Help creating tab delimited file from sql
Goto Forum:
  


Current Time: Sat Dec 10 18:53:48 CST 2016

Total time taken to generate the page: 0.16999 seconds