1. Memory Management -- should data be chunked with a size limitation for multiple batches?
Definitely. 500 million rows *might* fit in memory, it might not. In *any* solution, you read in batches.
3. Network / IO overhead -- which approach tops the other?
Neither if you run both on the database server.
4. Complexity of programming -- Debugging/Maintainability
PL/SQL is easier to code and maintain, but see (2) below
2. Processing speed -- which option between proc and pl/sql if there are minimal/heavy processing involved.
PL/SQL is fundamentally about data-processing not file processing. So whilst PLSQL and Pro*c (on the db server) will both run pretty much the same speed at *retrieving* your data, you are likely to have more options for performance tuning the file *writing* aspect. You can get an example of a Pro*C routine to unload data here
https://asktom.oracle.com/pls/asktom/asktom.search?tag=yet-another-ascii-dump-issue For a one-off exercise, I'd use PL/SQL (or even SQL Plus) because by the time I've written the Pro*c the unload will be done already! For repeated usage, I'd look at PL/SQL and then go to Pro*C if its too slow.