In today’s article, we will be learning how to add data to our table using EXTERNAL TABLE and SQL LOADER.
When adding our data to the table, we can follow two methods depending on the size of the data.
The first is to use the external table method, and the other is to import the data with the sqlldr tool.
EXTERNAL TABLE
1. We copy the file we will install with WinSCP somewhere in the operating system.
2. We create a database directory for the directory where the file we copied is located.
|
1 |
SQL> CREATE DIRECTORY CSV_DIR AS '/kurulum/db_import'; |
3. The user is given read/write rights to the created database directory.
|
1 |
SQL> GRANT READ, WRITE ON DIRECTORY CSV_DIR TO OARDAHANLI; |
4. We create an external table as follows.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE TABLE EXTERNAL_ACCCOUNT ( account_id varchar2(20), account_service_id varchar2(20) ) organization external ( default directory csv_dir access parameters ( records delimited by newline fields terminated by ',' ) location('acc.csv') ) reject limit unlimited; |
By querying, we see whether the external table is created correctly.
|
1 2 3 4 5 |
SQL> SELECT COUNT(*) FROM EXTERNAL_ACCCOUNT; COUNT(*) ---------- 1897240 |
We cannot perform DML operation on external tables, we can also create this table as a normal table.
NOTE: The default value of Reject Limit is 0.
|
1 |
SQL> CREATE TABLE DB_LIST AS SELECT * FROM EXTERNAL_ACCCOUNT; |
B: SQL LOADER
1. Switch from the operating system to the oracle user.
|
1 |
[root@DBONUR ~]# su - oracle |
2. Files are uploaded to any directory with WinSCP.
3. A file named loader.ctl is created in that directory.
|
1 |
[oracle@mwrapordb01 ~]$ vi loader.ctl |
4. The following is written into the file.
|
1 2 3 4 5 |
load data infile '/kurulum/acc.csv' into table EARSLAN.AC fields terminated by "," (account_id, account_service_id) |
5. Then the following command is run.
|
1 |
[oracle@mwrapordb01 ~]$ sqlldr oardahanli/oardahanli@ONURDB control=loader.ctl |
