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;