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