Oracle provides a file loader utility called SQLLDR to load data from file into a empty or populated table.
Employee Table: For loading data into table using sample sqlloader script.CREATE TABLE employee( emp_no INTEGER, emp_name VARCHAR(50), job_title VARCHAR(30), manager_id INTEGER, hire_date Date, salary DECIMAL(18,2), commission DECIMAL(18,2), dept_no INTEGER ) Primary key(emp_no);
LOAD DATA CHARACTERSET UTF8 length semantics char TRUNCATE INTO TABLE employee FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( emp_no ,emp_name ,job_title ,manager_id ,TYPE_IND ,hire_date "to_date(:hire_date, 'yyyy-mm-dd')" ,salary ,commission ,dept_no )
to_date(:hire_date, 'yyyy-mm-dd')
Error Field in data file exceeds maximum length
emp_name char(4000),
TEXT CHAR(1048544000),
LOAD DATA CHARACTERSET UTF8 length semantics char TRUNCATE INTO TABLE employee_1 FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( emp_no ,emp_name FILLER ,job_title ,manager_id ,TYPE_IND ,hire_date "to_date(:hire_date, 'yyyy-mm-dd')" ,salary ,commission ,dept_no ,updated_date "SYSDATE" ,updated_by "USER" ,processed_ind "1" )Note: Points to be noted for sqlloader file.
emp_name FILLER --2nd field in the file will be ignored since this is specified on 2nd position
updated_date "SYSDATE" --System date will be populated in updated_date column updated_by "USER" --System user value will be populated in updated_by column
processed_ind "1" -- Number 1 will be populated in processed_ind column.
sqlldr username/password@//host:port/sid control=[sqlloader_file] data=[datafile] Example: sqlldr tutorial_owner/tutorial_password@//host:1521/orcl control=ora_load.ctl data=sample_outfile.out
sqlldr username/password@'"(description=(address=(host=hostname)(protocol=tcp)(port=portNo))(connect_data=(sid=sidname)))"' control=ora_load.ctl data=sample_outfile.out Example: sqlldr tutorial_owner/tutorial_password@'"(description=(address=(host=hostname.com)(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))"' control=ora_load.ctl data=sample_outfile.out
sqlldr username/password@//host:port/sid control=[sqlloader_file] data=[datafile] direct=true Example: sqlldr tutorial_owner/tutorial_password@//host:1521/orcl control=ora_load.ctl data=sample_outfile.out direct=true