Search code examples
oracle-databaseplsqloracle12c

Calculating number of rows in a file created by UTL_FILE

I have a PLSQL package, that writes out an extract from an Oracle table into multiple files. Each file can have maximum of 50000000 rows. Generally, 5 or 6 such files are created. I am using UTL_FILE functionality to create these extract files.

I have a requirement to log generated file names and number of rows in the generated file to an Oracle table.

I can log the file names, but how I can log the number of rows exported to a file?

Solution

  • How? Count them one-by-one.

    • create a local variable
    • increment it after each UTL_FILE.put_line call
    • log it after you're done

    I've just tested it, output (result of DBMS_OUTPUT.PUT_LINE) looks like e.g.

    ocit_4001_1.txt: 37465 row(s)
ocit_4001_2.txt: 37464 row(s)
ocit_4001_3.txt: 37462 row(s)