How do i get to count the number of records in a flat file(.csv/.dat) including the header and trailer records.The code was return in a plsql using utl_file.Can you suggest me the best method to implement the logic in Plsql.And the trailer record should print the count of the records to in the file.We take the dadat from DB tables.
You could use an external table with a preprocessor, eg
I have a batch file on Windows that counts the entries in a file:
@echo off
c:\bin\usr\local\wbin\wc.exe %1
So I can wrap that around an external table so I can pass the name of the file
SQL> drop table wc_tab;
Table dropped.
SQL>
SQL> CREATE TABLE wc_tab ( x varchar2(100) )
2 ORGANIZATION EXTERNAL
3 (
4 TYPE ORACLE_LOADER
5 DEFAULT DIRECTORY temp
6 ACCESS PARAMETERS
7 (
8 RECORDS DELIMITED BY NEWLINE
9 PREPROCESSOR temp: 'file_wc.bat'
10 FIELDS TERMINATED BY WHITESPACE
11 )
12 LOCATION ('topics.sql')
13 )
14 REJECT LIMIT UNLIMITED;
Table created.
SQL>
SQL> select * from wc_tab;
X
-----------------------------------------------------
2326