In addition, I want know if SQL*Loader is recommended to load data from flat file as opposed to UTL_FILE package.
With respect to UTL_SMTP vs. UTL_TCP, both have their limitations. Depending on what exactly you want to do; for instance UTL_SMPT has the restriction of not being able to process attachment files. In addition, UTL_SMPT is just for sending e-mails and not receiving them. It sits on top of UTL_TCP; on the other hand UTL_TCP will let you interface with POP3 type mail servers.
With respect to UTL_FILE vs. SQL*Loader, again depends on what exactly you are trying to do.
SQL*Loader is a CPU-intensive utility and it is much faster than UTL_FILE; pre-Oracle9 days, UTL_FILE required a parameter UTL_FILE_DIR in the initialization file that would require a restart of the instance. But starting with Release 2 of Oracle9, you can dynamically allocate directories using the CREATE DIRECTORY command in the database without requiring a shutdown of the database.
For small scale I/Os UTL_FILE can be used as it gives you more programming flexibility.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Azim Fahmi
There are several ways to back up a database, including schema, table and full exports, as well as hot and cold backups. Here are descriptions of ... Continue Reading