Skip to Main Content

Load External Data in PL/SQL

Rafiq DDec 7 2015 — edited Jan 11 2016

I have used SQLLDR, External Tables and Datapump for some data loading activities. Recently however, after working on the same tasks using a different tool, I realized that some options in the SQLLDR could be stripped down to simplify the loading of text and csv files via PL/SQL. I do not know all available features of the Oracle database so if what I am suggesting is already available, please point me to it. I must also admit that these ideas are largely inspired by the other tool that I used.

In PL/SQL, I want to be able to load data into a table from an external source or vice versa using a command similar to this:

LOAD EXTERNAL DATA (TO 1|FROM 2) directory (INTO 2|FROM 1) TABLE  (SELECT|VIEW|TABLE[column1,column2,column3,...]) file_name = 'sample_input_file.csv' fields terminated by ','  skip [n] header rows [USE TABLE COLUMNS AS HEADER];

The first into option (INTO 1) will map to FROM 1 in the second option and a selection made of a table, view or select as the source or recipient of the data. The rest are self explanatory and already exist in the SQLLDR. For example, to load a csv file name orders.csv in a directory pointed to by a directory object orders_dir into (col1,col2,col3,col4) of an orders table, I could issue this statement within a PL/SQL block:

LOAD EXTERNAL DATA FROM orders_dir INTO TABLE orders(col1,col2,col3,col4) file_name = 'orders.csv' fields terminated by ','  skip 2 header rows;

The reverse will be as follows:

LOAD EXTERNAL DATA TO orders_dir FROM TABLE (SELECT col1,col2,col3,col4 FROM orders WHERE product_id = 234 FETCH FIRST 10 ROWS WITH TIES) file_name = 'orders.csv' fields terminated by ','  USE TABLE COLUMNS AS HEADER;

Thanks

Comments
Post Details
Added on Dec 7 2015
4 comments
703 views