Problem solve Get help with specific problems with your technologies, process and projects.

PL/SQL procedure to load CSV file into database table

I have a control file and a CSV file. I need help writing a PL/SQL procedure which can load the Excel file into the database table specified in the control file.

I have a control file and a CSV file, and now I have a form where I can browse these two files and on submission of this form I want to write a PL/SQL procedure which can actually perform the action of a SQL Loader and then load that Excel file into the database table specified in the control file.

Actually I can do that from the command prompt like this: c:sqlldr username/password control="control file path"

I want the process for how I can execute this command using a PL/SQL procedure. Thanks in advance.

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':

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

exec dbms_scheduler.run_job('myjob');

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.