In today’s article, we will be learning how to add data to our table using EXTERNAL TABLE and SQL LOADER.<\/p>\n
When adding our data to the table, we can follow two methods depending on the size of the data.<\/p>\n
The first is to use the external table method, and the other is to import the data with the sqlldr tool.<\/p>\n
1. We copy the file we will install with WinSCP somewhere in the operating system.<\/p>\n
2. We create a database directory for the directory where the file we copied is located.<\/p>\n
SQL> CREATE DIRECTORY CSV_DIR AS '\/kurulum\/db_import';<\/pre>\n3. The user is given read\/write rights to the created database directory.<\/p>\n
SQL> GRANT READ, WRITE ON DIRECTORY CSV_DIR TO OARDAHANLI;<\/pre>\n4. We create an external table as follows.<\/p>\n
CREATE TABLE EXTERNAL_ACCCOUNT\r\n(\r\naccount_id varchar2(20),\r\naccount_service_id varchar2(20)\r\n)\r\norganization external\r\n(\r\ndefault directory csv_dir\r\naccess parameters\r\n(\r\nrecords delimited by newline\r\nfields terminated by ','\r\n)\r\nlocation('acc.csv')\r\n)\r\nreject limit unlimited;<\/pre>\nBy querying, we see whether the external table is created correctly.<\/p>\n
SQL> SELECT COUNT(*) FROM EXTERNAL_ACCCOUNT;\r\n\r\n COUNT(*)\r\n----------\r\n 1897240<\/pre>\nWe cannot perform DML operation on external tables, we can also create this table as a normal table.<\/p>\n
NOTE:<\/strong> The default value of Reject Limit is 0.<\/p>\n
SQL> CREATE TABLE DB_LIST AS SELECT * FROM EXTERNAL_ACCCOUNT;<\/pre>\nB: SQL LOADER<\/h3>\n
1. Switch from the operating system to the oracle user.<\/p>\n
[root@DBONUR ~]# su - oracle<\/pre>\n2. Files are uploaded to any directory with WinSCP.<\/p>\n
3. A file named loader.ctl is created in that directory.<\/p>\n
[oracle@mwrapordb01 ~]$ vi loader.ctl<\/pre>\n4. The following is written into the file.<\/p>\n
load data\r\ninfile '\/kurulum\/acc.csv'\r\ninto table EARSLAN.AC\r\nfields terminated by \",\"\r\n(account_id, account_service_id)<\/pre>\n5. Then the following command is run.<\/p>\n
[oracle@mwrapordb01 ~]$ sqlldr oardahanli\/oardahanli@ONURDB control=loader.ctl<\/pre>\n<\/div>\n