attention at ROWCOUNT with SELECT
Franz Holzinger, August 20, 2001 - 4:19 am UTC
In the "PL/SQL Users Guide and Reference" Chapter 5-36 there is a wrong example:
LOOP
FETCH c1 INTO my_ename, my_deptno;
IF c1%ROWCOUNT > 10 THEN
...
END IF;
...
END LOOP;
I tried this without success. Now I know why.
August 20, 2001 - 9:13 am UTC
What is wrong with that? My example above:
tkyte@ORA734.US.ORACLE.COM> declare
2 cursor c1 is select empno from emp;
3 begin
4 for x in c1
5 loop
6 dbms_output.put_line( c1%rowcount || ' rows selected' );
7 end loop;
8 end;
9 /
1 rows selected
2 rows selected
3 rows selected
...
14 rows selected
PL/SQL procedure successfully completed.
shows that C1%rowcount works -- SQL%rowcount does not.
how to do the sql%rowcount with open x for 'select * from emp'
A reader, September 27, 2002 - 1:47 pm UTC
Hi I want to check the # of rows "selected" with and implicit cursor and if the rows < 100 return the REFCURSOR
other wise raise user defined exception and return message
right now we are running two stmts.
1) select count(*) from emp;
to count
if count < 100 then
2.) open rc for select * from emp;
can you suggest one query solution
thanks,
September 27, 2002 - 2:52 pm UTC
Why not just return the first 100 rows?
open rc for select * from (select * from emp) where rownum < 100;
Else, to make your thing more "efficient", you would:
select count(*) from emp where rownum <= 100;
(but it looks like you are trying to do this to "reduce the workload on your machine". If that is the case -- ALL of the counting things will INCREASE it. I suppose one way to approach this might be:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function fail_at_threshold( p_number in number, p_threshold in number )
2 return number
3 as
4 begin
5 if ( p_number >= p_threshold )
6 then
7 raise_application_error( -20002, 'Query exceeds ' || p_threshold || ' rows' );
8 end if;
9 return p_number;
10 end;
11 /
Function created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.*, fail_at_threshold( count(*) over (), 5 )
2 from ( select *
3 from (<b> select empno, ename
4 from scott.emp
5 where ename like '%KIN%' </b>)
6 where rownum <= 5 ) A
7 /
EMPNO ENAME FAIL_AT_THRESHOLD(COUNT(*)OVER(),5)
---------- ---------- -----------------------------------
7839 KING 1
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.*, fail_at_threshold( count(*) over (), 5 )
2 from ( select *
3 from ( <b>select empno, ename
4 from scott.emp )
5 where rownum <= 5</b> ) A
6 /
select a.*, fail_at_threshold( count(*) over (), 5 )
*
ERROR at line 1:
ORA-20002: Query exceeds 5 rows
ORA-06512: at "OPS$TKYTE.FAIL_AT_THRESHOLD", line 7
ORA-06512: at line 1
ops$tkyte@ORA817DEV.US.ORACLE.COM>
the text in bold is your query you want to run - just wrap the rest around it (and in 8i, you'll need to use dynamic sql for the analytics in PLSQL).
That'll process upto N rows and if the result is in fact N rows -- it'll "fail" the query.
You'll do less query work then you do now and achieve the same net result.
Context Switching
Ik, September 27, 2002 - 3:50 pm UTC
Tom,
Would selecting just COUNT(*) OVER () and then within the PL/SQL block (after FETCH) checking if it equals 5 be a better option than using a database function? That way also, you can RAISE error and exit after the first record is fetched.
Function would involve context switching and also would be an overhead if the actual rows are less than 100.
Am i correct?
September 27, 2002 - 4:00 pm UTC
No, you would have to run the query and then re-run the query.
For 100 or less rows, the overhead of calling plsql from sql will be marginal at best.
Tell me -- would you rather run the entire (possibly very complex) query 2 times -- or call this tiny function 100 or less times?
Thinking harder, I see we could OPTIMIZE this via:
ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.*,
2 decode(rownum,1,fail_at_threshold( count(*) over (), 5 ),0)
3 from ( select *
4 from ( select empno, ename
5 from scott.emp
6 where ename like '%K%' )
7 where rownum <= 5 ) A
8 /
EMPNO ENAME DECODE(ROWNUM,1,FAIL_AT_THRESHOLD(COUNT(*)OVER(),5),0)
---------- ---------- ------------------------------------------------------
7698 BLAKE 3
7782 CLARK 0
7839 KING 0
ops$tkyte@ORA817DEV.US.ORACLE.COM>
now it only gets called once per query -- even better.
Gradual refinement, thats what its all about...
Pardon me if iam getting it wrong...
Ik, September 27, 2002 - 4:32 pm UTC
Tom,
This is what i was looking at. Dunno if i have the syntax right. (Don't have Oracle on this machine)
open rc for
'select empno, ename, COUNT(*) OVER () cnt
from scott.emp
where ename = ''XYZ'';
LOOP
FETCH rc INTO var1, var2, var3;
IF var3 = 5 THEN
RAISE <exception>;
END IF;
END LOOP;
Why would this run and then re-run? It will fail after fetching first record right?
September 27, 2002 - 4:53 pm UTC
Ok, so now say the count(*) over () comes back with 4.
What then -- you just fetched the first row, the result set is useless to the end user.
You now have to close rc, and OPEN IT ALL OVER AGAIN -- doing all of the work -- again.
Use the decode. One query, one function call, least amount of code, I cannot think of a more efficient way to do it.
Sorry for dragging this conversation on...but
Ik, September 27, 2002 - 5:28 pm UTC
I missed the ROWNUM part.
open rc for
'select empno, ename, COUNT(*) OVER () cnt from scott.emp
where ename = ''XYZ''
AND ROWNUM <= 5';
LOOP
FETCH rc INTO var1, var2, var3;
IF var3 = 5 THEN
RAISE <exception>;
ELSE
<Do regular processing>
END IF;
END LOOP;
He wants only data less than 5. If it has 5 or more then ...boom else loop through and process the data.
September 27, 2002 - 6:35 pm UTC
the requirement:
a) check to make sure query returns less then 100 rows
b) if it does, return the 100 rows to the client via a ref cursor
Your <do regular procesing> is re-running the query from the get go for the client.
Use decode( rownum, 1, function, 0 ).
dbms_output
Anuradha, September 28, 2002 - 2:58 am UTC
Dear Tom,
I get the following error while running the PL/SQL code .What does this error message implies.
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_OUTPUT", line 57
ORA-06512: at line 47
Is there some other package which displays output that doesn't throw the buffer overflow error.My requirement is I want to update composite value in a table whenever rate is amended and I do batch processing. I want to check if all data are considered or not.When using dbms_output buffer overflow occurs and the procedure aborts.
Thanks for your great work.
September 28, 2002 - 7:23 am UTC
dbms_output has a 255 bytes/line limit. You are exceeding it in a large way:
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( rpad('*',1000,'*') );
BEGIN dbms_output.put_line( rpad('*',1000,'*') ); END;
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_OUTPUT", line 57
ORA-06512: at line 1
It should (and in later releases does) throw:
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec dbms_output.put_line( rpad('*',256,'*') );
BEGIN dbms_output.put_line( rpad('*',256,'*') ); END;
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 84
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
ORA-06512: at line 1
Remedy?
Anuradha, September 29, 2002 - 11:48 pm UTC
Dear Tom,
In continuation of my previous post, can you please tell me how can I avoid that error and also tell me if there is some other package which displays output that
doesn't throw the buffer overflow error. Will rtrim avoid this error message ? When using dbms_output buffer overflow occurs and the procedure aborts.
Thankyou very much
Anuradha
September 30, 2002 - 7:17 am UTC
To avoid the error
a) don't dbms_output.put_line lines over 255 bytes wide!
create or replace procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
end;
/
a small routine like that can be useful.
You should read about UTL_FILE as well. Or search this site for "MY_DBMS_OUTPUT"
alternative to dbms_output
Anuradha, September 29, 2002 - 11:55 pm UTC
Dear Tom,
Sorry to progressing the above post . Is there any alternative package to dbms_output.put_line.This package displays at the completion of the procedure but I want to display as and when the varible is asked to display.
Thanks a lot
Anuradha
September 30, 2002 - 7:19 am UTC
You will need pipelined functions in Oracle9iR1 and up in order to do that.
Or, you can use UTL_FILE and do a tail -f on the file in another window.
Or, you can use dbms_application_info to set values in v$session or v$session_longops to monitor from another session.
Prior to that -- when you call a procedure, it goes away to the server to run it and doesn't come back until its done. There is no chance to "see" what it is printing out until it is DONE.
A reader, September 30, 2002 - 2:41 pm UTC
Tom, Can you please show with an example of how to achieve this with Oracle 9i Pipelined functions? Thanks.
October 01, 2002 - 9:27 am UTC
ops$tkyte@ORA9I.WORLD> create or replace type myMsgType as table of varchar2(50)
2 /
Type created.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> create or replace function show_me_NOW( p_n in number ) return myMsgType
2 PIPELINED
3 as
4 begin
5 for i in 1 .. p_n
6 loop
7 pipe row( 'The Time is ' || systimestamp );
8 dbms_lock.sleep(1);
9 end loop;
10 return;
11 end;
12 /
Function created.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> set arraysize 1
ops$tkyte@ORA9I.WORLD> select * from TABLE( show_me_NOW(5) );
COLUMN_VALUE
--------------------------------------------------
The Time is 01-OCT-02 09.38.54.782566000 AM -04:00
The Time is 01-OCT-02 09.38.55.830566000 AM -04:00
The Time is 01-OCT-02 09.38.56.860433000 AM -04:00
The Time is 01-OCT-02 09.38.57.890444000 AM -04:00
The Time is 01-OCT-02 09.38.58.920509000 AM -04:00
ops$tkyte@ORA9I.WORLD>
You cannot tell -- but I saw the first output seconds before the last -- setting the arraysize is RELEVANT, else the client will wait for all 5 rows before showing the first.
A reader, January 19, 2003 - 10:31 am UTC
Hi Tom,
I saw your reply on PIPLINED function on google forum.
I tried it .It is working like charm.
I want to see explain plan and statistics for this.
For arraysize 100 it is working fine but for arraysize 1 it is giving some errors.
SQL> set autotrace traceonly
SQL> set arraysize 100
SQL> select * from TABLE(foo);
22 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COLLECTION ITERATOR (PICKLER FETCH) OF 'FOO'
Statistics
----------------------------------------------------------
12 recursive calls
0 db block gets
36 consistent gets
0 physical reads
0 redo size
1898 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
22 rows processed
SQL> set arraysize 1
SQL> select * from TABLE(foo);
SP2-0612: Error generating AUTOTRACE report
22 rows selected.
SP2-0612: Error generating AUTOTRACE report
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 COLLECTION ITERATOR (PICKLER FETCH) OF 'FOO'
SP2-0612: Error generating AUTOTRACE STATISTICS report
SQL>
Why it is giving error on statistics?
Please advise.
Thanks,
January 19, 2003 - 10:51 am UTC
set arraysize to 2 -- I've noticed that -- with an arraysize of 1, plus and autotrace has an issue. You want the arraysize bigger then one nominally anyway.
rowcount
mo, January 19, 2003 - 12:38 pm UTC
January 19, 2003 - 1:23 pm UTC
it is explictly implicit.
It is technically an EXPLICIT cursor (by the book) that we are using implicitly in a cursor for loop.
technically, explicit
as used, more implicit than explicit.
A reader, January 23, 2003 - 10:49 am UTC
Hi Tom,
Is it possible to raise and exception if an implicit cursor does not return any rows?
Eg:
begin
for i in (select f1 from t)
loop
---
--
end loop;
exception
When no_rows_returned_by_this_implicit_cursor then
---
---
end;
I tried using sql%rowcount, when_no_data_found etc. But nothing works. Can you please help. Thanks.
January 23, 2003 - 11:14 am UTC
declare
l_found boolean := false;
begin
for x in ( select ... )
loop
l_found := true;
...
end loop;
if ( NOT l_found ) then raise NO_DATA_FOUND; end if;
Reset SQL%ROWCOUNT to zero
Robert, July 03, 2003 - 8:08 pm UTC
Hi, Tom
Quick, how do I reset SQL%ROWCOUNT to zero before
the next SQL ?
(if not possible , pls suggest the cheapest way to do this)
Thanks !
July 04, 2003 - 7:35 am UTC
UMM -- why? what could the possible logic be.
COUNT(*) OVER ()
A reader, July 04, 2003 - 9:43 am UTC
In the above question, COUNT(*) OVER () has been used a lot. How is the over() helping here. Can you please explaining.
July 04, 2003 - 9:49 am UTC
without it, the query fails:
scott@ORA920> select dname, count(*) from dept;
select dname, count(*) from dept
*
ERROR at line 1:
ORA-00937: not a single-group group function
with it, you get the count of the result set "joined" to each row
scott@ORA920> select dname, count(*) OVER () from dept;
DNAME COUNT(*)OVER()
-------------- --------------
ACCOUNTING 4
RESEARCH 4
SALES 4
OPERATIONS 4
scott@ORA920>
it is called an analytic function, the coolest thing to happen to sql since "SELECT"
Count or rows?
Kamal Kishore, July 04, 2003 - 12:53 pm UTC
Hi Tom,
At our client site, we have a packaged procedure that returns user query results in batches (25 each). When users click "next" from the web client, we return the next 25 rows.
Now, the users are asking for getting the COUNT of rows that will eventually be returned by the query. They want to know from the start, as to how many total rows are there (that is, if they keep clicking next, how many next clicks will it take for the entire resultset to be displayed).
We told them that it will be too resource intensive to do a count and then also do the actual query to return the results (will take more time and resources to return the answer), but they insist on seeing something like "25 of 1234 rows" when they query from the web client.
1). One way would be to perform a Count(*) on the same query WHERE clause and then do the same query WHERE clause to actually get rows returned to the user (we can do this COUNT(*) only the first time, not every time user clicks next).
2). Other is what you showed in the examples above - to use analytic functions. Since our development and test servers are Oracle9i and production is at least 8.1.7, we could also use the approach shown by you to return the count along with the query results. If we do this way, we would be returning the COUNT every time when user clicks next from the web client.
My question is, which one would be better? 1) or 2).
Will it be too much overhead calling the analytic function to get the count for each "next" click as opposed to only once (only first time)?
Which apporoach will be cheaper on our server resources?
(or if there is a hidden approach #3, that i missed).
Thanks,
July 04, 2003 - 2:05 pm UTC
well, tell them to buy approximately 50 times the amount of hardware and other resources then they have now, they are telling you to do hundreds times more work. Make sure they benchmark this before doing it -- seriously.
1) well, hope the data doesn't change but yes, this is what you would want to do if they actually make the bad decision to do this.
2) #1
Yes, But...
Kamal Kishore, July 04, 2003 - 2:55 pm UTC
Hi Tom,
That is exactly what we tried to put across them about NOT getting the count of rows.
But, Unfortunately, Long ago a fat client application was provided to them (which is now a web based solution) which had this facility of showing the COUNT of total number of rows that they can expect.
Now the question they have is, well we had that in the fat client application that we have been using and have gotton used to it. Whay suddenly its not possible? (just because its a web application?).
The situation is, when the fat client application was developed the volume of data that was being handled was negligible and not too many users. No one cared about the performance impact and decision was made to show them the total count (I wasn't around at this site at that time).
Now that the volume of data has grown tremendously and number of users have outgrown the handful of users that the client had before, it might (or rather will be) a huge performance impact if we still provided that feature.
We already had several sessions explaining about the benefits of NOT getting the total count, but it keeps coming back and back and ...
July 04, 2003 - 4:02 pm UTC
when will they file the bug report against google? google lies on its counts -- and not only that, it stops giving results out after a while (try to goto page 200)...
Your logic is sound, client server limited the user population cause you had to install stuff. Think about how many people would use amazon.com if you had to install version 5.4.23 of the amazon client supported on windows version XXX -- no one (anyone remember when UPS or FedEx used to install a hard wired computer at large businesses so they could run an application...)
Put it on the web and boom -- you've got an unlimited population of users suddenly. And they'll all want you to add 5 gazillion new features.
How about this as a compromise? Instead of saying "there was 1235 hits", add a link that says "click here to waste computer resources and see how many hits your query might return" (you can use a nicer message -- but the concept is sound, it is the way forms, our client server tool, did it -- you went "menu -> count hits")
Tracking progress of procedure.
Saibabu Devabhaktuni, October 27, 2003 - 2:12 pm UTC
Tom,
I have a package which does the batch processing every day on our database. I have included dbms_output.put_line in all of procedures in that package to report progress of processing in a logfile. It is reporting dbms_output buffer only after executing procedure in full. For example:
create or replace procedure test_p as
begin
dbms_output.put_line('Starting....');
execute immediate('create table test(a number)');
dbms_output.put_line('Created test table');
execute immediate('create table test2(a number)');
dbms_output.put_line('Created test2 table');
end;
/
How can I get dbms_output to write output after creating each table???
Thanks,
Sai.
October 27, 2003 - 6:31 pm UTC
you cannot -- that is not the way it works.... (if you want the gory details on exactly how dbms_output works -- i have it all in my book "Expert one on one Oracle"
but -- search for
dbms_application_info.set_session_longops
on this site to see how to achieve what you want.
A reader, May 14, 2004 - 10:56 am UTC
Is there a way to test a refcursor status?
A reader, October 19, 2004 - 10:28 am UTC
Can you tell if a refcursor that has been opened has found records? or how many records are in it?
Thanks!
October 19, 2004 - 10:50 am UTC
a) fetch from it
b) fetch the last row from it.
until you do that -- NO ONE (not even the database) knows.
super
A reader, October 19, 2004 - 11:27 am UTC
So what am I doing wrong here?
sql> var x refcursor
sql>
sql> create or replace procedure reftest (rc1 out sys_refcursor)
2 is
3 v_var number;
4
5 begin
6 open rc1 for select count(*) from dual;
7 loop
8 FETCH rc1 INTO v_var;
9 exit when rc1%notfound;
10 end loop;
11 dbms_output.put_line('The number of rows selected = '||rc1%rowcount);
12 end;
13 /
Procedure created.
sql>
sql> exec reftest(:x);
The number of rows selected = 1
PL/SQL procedure successfully completed.
ERROR:
ORA-01002: fetch out of sequence
no rows selected
October 19, 2004 - 11:33 am UTC
you must have "autoprint on", it is attempting to print out the results of a cursor you already processed.
Sort of like you've read to the end of file in a file and are trying to read the next record - there isn't one. "fetch out of sequence".
once fetched -- it is in fact "fetched", exhausted, used up, empty.
happy happy joy joy
A reader, October 19, 2004 - 11:38 am UTC
You rule.
How to join piplined functions with another table?
Tony, November 30, 2004 - 3:43 am UTC
How to join piplined function with another table?
Please given me an example.
November 30, 2004 - 7:43 am UTC
just join????
select * from table( f(x) ) T1, T2 where .....
nothing "special"
sql%rowcount in other situations
Rick, December 16, 2004 - 5:51 pm UTC
Going back to the original response, you say that sql%rowcount only works for insert, update and delete and then you present a clever way of getting it to work for select as well. I've noticed that a CTAS (create table as select) will also return a sql%rowcount but, bizarrely, a CTAS of an IOT (index-organized table) does not.
Do you have any idea why not, and how we can somehow get the rowcount of the CTAS of an IOT?
Great site!
December 16, 2004 - 9:11 pm UTC
what release?
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
2 execute immediate 'create table t as select * from all_users';
3 dbms_output.put_line( sql%rowcount );
4 end;
5 /
22
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> begin
2 execute immediate 'create table t (user_id primary key, username, created ) as select * from all_users';
3 dbms_output.put_line( sql%rowcount );
4 end;
5 /
22
PL/SQL procedure successfully completed.
IOT and sql%rowcount
Rick, December 17, 2004 - 3:56 am UTC
Hi Tom, thanks for the response.
Your example wasn't an IOT, unless I'm missing something here. How about this:
SQL> select * from v$version
2 /
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for HPUX: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> set serveroutput on
SQL>
SQL> begin
2 execute immediate 'create table t
3 (user_id primary key, username,
4 created ) as select * from all_users';
5 dbms_output.put_line( sql%rowcount );
6 end;
7 /
19
PL/SQL procedure successfully completed.
SQL>
SQL> REM But that's not an index-organized table
SQL>
SQL> select iot_type from user_tables
2 where table_name = 'T'
3 /
IOT_TYPE
------------
SQL>
SQL> REM This one is:
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> begin
2 execute immediate 'create table t
3 (user_id, username,created,
4 constraint iot_plsql_pk primary key(user_id))
5 organization index
6 as select * from all_users';
7 dbms_output.put_line( sql%rowcount );
8 end;
9 /
0
PL/SQL procedure successfully completed.
SQL>
SQL> select iot_type from user_tables
2 where table_name = 'T'
3 /
IOT_TYPE
------------
IOT
SQL>
SQL> REM Take away the "organization index" and sql%rowcount works again:
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> begin
2 execute immediate 'create table t (user_id, username,created,
3 constraint iot_plsql_pk primary key(user_id))
4 as select * from all_users';
5 dbms_output.put_line( sql%rowcount );
6 end;
7 /
19
PL/SQL procedure successfully completed.
SQL>
SQL> select iot_type from user_tables
2 where table_name = 'T'
3 /
IOT_TYPE
------------
If you can think of a workaround, that would be great.
We could plunder the rows_processed column in v$sqlarea
but that's getting a bit messy.
December 17, 2004 - 2:29 pm UTC
you are right, I did forget the organization index clause (but should get bonus points for remembering the primary key :)
the only approach I can see is to two step it, create the table, populate via insert.
IOT and sql%rowcount
Rick, December 17, 2004 - 7:01 pm UTC
You can have the bonus points. Me, I've always been mystified about how an IOT can be an index and still have even more indexes defined on it.
Creating and then inserting seems to be much more labour intensive, because we're filling an index on the way. I'm not willing to do that just for the row count. Inserting into a table sans index is quicker, but then we wouldn't have an IOT. The word from Support is that we're lucky that CTAS even gives us a rowcount (DDL isn't supposed to) so we mustn't grumble. Oh well, back to rows_processed.
Cheers!
December 18, 2004 - 8:46 am UTC
if you are doing an IOT you are writing an index regardless of the approach, it is true that the CTAS can avoid more redo, but at the end of the day, you have, well, an index.
SQLPLUS output
A reader, February 03, 2005 - 2:30 pm UTC
Tom,
In SQLPLUS, how could I spool the result of the SELECT statement to one OS file and the number of row selected to another OS file?
Thanks
February 04, 2005 - 1:20 am UTC
not sure that you can. don't know of anyway to do it myself.
well, maybe - you can do something like this:
column rnum new_val r noprint
set feedback off
spool x
select a.*, rownum rnum from (select * from scott.dept) a;
spool off
set verify off
spool y
prompt &r
spool off
select * from scott.dept is your query (with order by, whatever in it). then:
ops$tkyte@ORA9IR2> @test
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
4
ops$tkyte@ORA9IR2> !cat x.lst
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
ops$tkyte@ORA9IR2> !cat y.lst
4
Need to have the number of rows modified returned.
Ryan, May 31, 2005 - 9:07 pm UTC
I'm doing an Update statement which will modifiy 1 or more records of a database. I need to know the number of records modified by the Update statement. I've tried returning the value of SQL%ROWCOUNT but it didn't work. Here is the sql I used.
BEGIN
UPDATE Table_Name SET FieldName1 = :1 WHERE FieldName2 = :2 RETURNING SQL%ROWCOUNT INTO nRowsUpdated;
END;
I've also tried adding colon to beginning of nRowsUpdated and this didn't help.
Please help.
June 01, 2005 - 8:51 am UTC
begin
update t set ...;
nRowsUpdated := sql%rowcount;
end;
sort of like the examples from the top of the page. sql%rowcount is a variable accessible to you right after the update, you don't "return" it.
Thanks again Tom.
Ryan, June 01, 2005 - 1:17 pm UTC
This works. I really appreciate the response.
A reader, June 08, 2005 - 11:57 am UTC
In a procedure i have both insert, update statements.
After update I want to know if records were affected, for this i am thinking to use sql%rowount. Something like this:
INSERT INTO T VALUES(100,'TEST');
UPDATE T SET STR='UPDATE' WHERE ID=100;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
My concern is will the SQL%rowcount be with respect to update (last DML executed) or to insert (previous DML).
I am thinking it is for last DML executed, want to confirm with you.
Thanks.
June 08, 2005 - 1:05 pm UTC
last implicit cursor that was used. last "sql". best to capture it immediately after the statement into your own variable and use that.
sql_statement;
my_var := sql%rowcount;
use my_var ....
that way, it is always what you think it is.
difference in SQL%ROWCOUNT on 8i and 9i
Khalid, December 01, 2005 - 1:54 am UTC
Tom,
We are facing a situation after migrating some of our databases from 8i to 9i. It appears that in 9i SQL%ROWCOUNT is set to 0 after a COMMIT or a ROLLBACK but in 8i it remains unchanged. This might require us to modify some of our code. Could you let us know what could be the main reason for doing such a change between the versions ?
Thanks,
Khalid
December 01, 2005 - 12:26 pm UTC
I do not know the main reason, other than it was like a bug fix for 8i, sql%rowcount should always have been moved into a local variable of your own immedaitely after the SQL statement to avoid side effects by other things. What happens to it after a commit/rollback is not documented - other than to say "it is not restored to the pre-transaction value"
</code>
http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1285 <code>
sql%rowcount with systimestamp
Madhu, January 30, 2006 - 1:13 am UTC
Greetings Tom,
To get 'milli seconds' in the time display, SYSDATE was replaced by SYSTIMESTAMP. And all the pl/sql programs degraded wherever the SYSTIMESTAMP is used between DML/Bulk Select execution & corresponding sql%rowcount reference. SYSTIMESTAMP is used for logger to print start & end of the SQL execution only.
I was in the impression that it is an extended version 'pseudo column' SYSDATE. But surprisingly sql%rowcount was affected by this, am I missing thing?
Does SYSTIMESTAMP executes any SQLs inside?
Thanks in Advance.
January 30, 2006 - 2:07 am UTC
I don't know what you mean. what do you mean sql%rowcount was affected - provide full example. define "degraded"
Madhu, January 30, 2006 - 6:33 am UTC
I was, indeed, bit nervous in my first post as it was to my Oracle Guru Tom :) .
Sorry for using local jargon. Hope the following conveys you what I mean.
procedure proc_t1_t2(P_KEY_VAL in TABLE_1.KEY_COL%type) is
type T_COL1 is table of TABLE_1.COL1%type;
type T_COL2 is table of TABLE_1.COL2%type;
VC_COL1 T_COL1;
VC_COL2 T_COL2;
begin
proc_log('bulk read TABLE_1 start');
--read TABLE_1 values into collection variables
select COL1, COL2 /* 1-sql */
bulk collect into VC_COL1, VC_COL2
from TABLE_1
where KEY_COL = P_KEY_VAL
order by COL1;
proc_log('bulk read TABLE_1 end; KEY_COL = '||P_KEY_VAL || '; rowcount '||sql%rowcount); /* 1 */
--if data found in TABLE_1 table, then update/delete TABLE_2 table
if sql%rowcount != 0 then /* 2 */
proc_TABLE_2(VC_COL1(1));
-- some process on VC_COL1
end if;
exception
when others then
--PASSING EXCEPTION TO CALLING METHOD
saveErrorTrace(DBMS_UTILITY.format_error_stack,
DBMS_UTILITY.FORMAT_CALL_STACK,
false,
sqlcode);
raise;
end proc_t1_t2;
/**** proc_log procedure with TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS')***********/
procedure proc_log(p_MSG varchar2) /* OK */
as
begin
gm_DebugStr := gm_DebugStr
|| TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') || '->' || p_MSG
|| chr(10);
exception
when others then
null;
END proc_log;
/**** proc_log procedure with TO_CHAR(SYSTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS:FF3')***********/
procedure proc_log(p_MSG varchar2) /* NG */
as
begin
gm_DebugStr := gm_DebugStr
|| TO_CHAR(SYSTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS:FF3') || '->' || p_MSG /* 3 */
|| chr(10);
exception
when others then
null;
END proc_log;
=========================================
(both the versions of proc_log for easy reference.)
rowcount is zero when the above 1-sql was executed, with earlier proc_log (with SYSDATE), sql%rowcount @ /* 2 */ doesn't change, it remains zero as expected by the programmer, and was working fine.
When we changed proc_log to use SYSTIMESTAMP (for millisec), sql%rowcount @ /* 2 */ is 1 and the program aborts with ORA-06533.
After debugging we found sql%rowcount is changed @ /* 3 */. I assumed that SYSTIMESTAMP is using some implicit cursor.
Initially I suggested to use a java store procedure to get the date/time upto millisec format. But, in the development site where I work, its a looong process to get permission to add new object to DB. Finally we decided to go forTO_CHAR(SYSTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS:FF3') as someone suggested us that it is the elegant way to meet the requirement.
And there are hundreds of pl/sql programs that use this proc_log. Now we need to check these hundreds of pl/sql programs to make sure that sql%rowcount is used immediately after the intended SQL STATEMENT (or before using this proc_log)
From google, I understood that the SYSTIMESTAMP is a function (earlier I was in the impression of psuedo column like SYSDATE).
Do you suggest any better workaround.
Thanks in advance Tom.
January 30, 2006 - 8:45 am UTC
You should ALWAYS immediately capture sql%rowcount (or sql% anything) into your own local variable and use that. Period - that is a bug waiting to happen in all cases.
Counting Rows Affected : Select Case
Luis Tamariz, January 30, 2006 - 7:26 am UTC
You can Use Analytic Function FIRST_VALUE in the select case :
SELECT A.*, FIRST_VALUE(ROWNUM) OVER (ORDER BY ROWNUM DESC ROWS UNBOUNDED PRECEDING) AS TOTAL_RECORDCOUNT FROM
( Your Query ) A
In Others Case use SQL%ROWCOUNT
January 30, 2006 - 9:03 am UTC
well, actually a safe way would be
select a.*, count(*) over () cnt
from ....
but it would be up there in the "not too efficient, waiting for the last row to be materialized before getting the first row".
sql%rowcount
Raj, August 07, 2006 - 1:29 pm UTC
Tom ,
Is there a way to know how many rows got updated and how many got inserted using the merge statement ?
Thanks ,
Raj
August 07, 2006 - 9:41 pm UTC
yes, merge reports back the number of rows updated and inserted :)
can you find out how many "updated" vs "inserted" - no, not really.