PL/SQL procedure to load CSV file into database table

In Oracle 9i, there is no direct way to execute an operating system command directly from PL/SQL. But you can write an external file using Perl, C or C++, using sys.DBMS_PIPE.

If you are using Oracle 10g, you can use sys.DBMS_SCHEDULER. The script below shows an example of how to set up a dbms_scheduler to execute the command '/app/oracle/x.sh':

BEGIN
  dbms_scheduler.create_job(job_name        => 'myjob',
                            job_type        => 'executable',
                            job_action      => '/app/oracle/x.sh',
                            enabled         => TRUE,
                            auto_drop       => TRUE);
END;
/

exec dbms_scheduler.run_job('myjob');