Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Capturing RowCount

637632Jan 29 2010 — edited Jan 29 2010
Hi,
I generate CSV files using spool command in SQL Plus.
This file is pipe delimited and use ||'|'|| between two records in the select statement.

I need a trailer record at the end of file which has format
TRAILER|<NO OF RECS RETURNED BY SELECT STATEMENT>

One way I thought is after the main select statement which returns the record,
add another select statement as:
SELECT 'TRAILER'||'|'||(SELECT COUNT(*) FROM <Same tables and conditions as used in main select> from dual;
But I dont like this approach as:
1. Select is executed twice, though second time it is just doing the count
2. Possibility of records being added between two select statements.

I also tried code below after the main select but no luck
select sql%rowcount from dual;

AND

select %rowcount from dual;
Due to license limitations I can't really do any development on the oracle database like procedure or anything else.

Is there a way to capture total no records returned by a select in a variable and use it again.




Please advice,
Thanks in advance,
Raja
This post has been answered by MichaelS on Jan 29 2010
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 26 2010
Added on Jan 29 2010
12 comments
7,196 views