For this purpose we can use the following three methods
1. Oracle Application Express
One way which is very easy is to install Oracle Application Express (Formerly HTML DB). Within Application Express we can actually just copy and paste our Excel rows and it will import them to a table. Other then that Application Express is a very useful tool for other things too.
2. SQL Loader SQLLDR
One solution is to use the sqlloader to load any external data into the Oracle database. The problem with this is that we need to run the sqlloader (sqlldr) script every time we need an update.
3. External Table
The more elegant and faster method is to use external tables.
Now, with an external table we can initialize our table once and never have to worry about it anymore. Plus we can use the external table just like any other table in database and issue SQL commands to join the table (may be processing is bit slowly).
Steps required
a. Export your excel sheet to a tab. Delimited format, we call our file “data_2_import.txt”.
b. If you don’t have a directory alias set up within Oracle then create one now. The directory allows Oracle to read files from this directory on your hard drive.
You create a directory with the following commands:
Create or replace directory importdir as ‘C:\data_to_import’;
c. Now we only need to create the external table.
CREATE TABLE EXT_MEMBERS
(
ID VARCHAR2(20 CHAR),
NAME VARCHAR2(100 CHAR)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY importdir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY X’9′ (
ID char(20),
Name CHAR(100)
)
)
LOCATION (importdir:’data_2_import.txt’)
)
REJECT LIMIT 0
PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT)
NOMONITORING;
As we can see in the code above, we are creating a table with the filenames “id” and “name” which represents the order from our Excel file. We also tell the external table that every record is on a newline and that the fields are separated with tabs (X’9′)
In case we are using Comma delimited we use fields terminated by,’, in case we are using fixed field length then we use fields(field 1 position (1:4) char(4), field 2 position (5:30) char(30))
Now when we issue a select command on the external table Oracle will read in our data_2_import.txt file. Whenever there is an update of our data_2_import files we only need to replace the file with same formatting on the hard drive and the table is automatically updated within the database.
Note: If external tables are created with NOLOG then granting READ on the DIRECTORY object is sufficient. If an external table is created without the NOLOG syntax then both READ and WRITE must be granted to SELECT from it.
External tables are READ ONLY. Insert, update, and delete can not be performed
Oracle Application World 