Missing Delimiter (. Inserted). { L.42
If you are using Oracle database, at some point you might have to deal with uploading data to the tables from a text file.
This article provides 10 applied examples on how to upload information from a flat file to Oracle tables.
Input data file for SQL*Loader
This is the input text file that contains the information that needs to be loaded into an oracle table. Each and every records needs to be in a split up line, and the column values should be delimited by some common delimiter character. For some of the examples mentioned below, we'll utilize the following employee.txt file to upload the information to the employee tabular array.
$ cat employee.txt 100,Thomas,Sales,5000 200,Jason,Engineering science,5500 300,Mayla,Applied science,7000 400,Nisha,Marketing,9500 500,Randy,Engineering,6000 501,Ritu,Accounting,5400
SQL*Loader Command File
This contains the instructions to the sqlldr utility. This tells sqlldr the location of the input file, the format of the input file, and other optional meta data information required by the sqlldr to upload the data into oracle tables.
$ true cat example1.ctl load data infile '/home/ramesh/employee.txt' into tabular array employee fields terminated by "," ( id, proper name, dept, salary )
The above control file indicates the following:
- infile – Indicates the location of the input data file
- into tabular array – Indicates the tabular array name where this data should be inserted
- fields terminated by – Indicates the delimiter that is used in the input file to separate the fields
- ( id, name, dept, salary ) – Lists the name of the column names in the tabular array into which the data should exist uploaded
i. Basic Upload Instance Using SQL*Loader
Outset, create the employee table as shown beneath.
SQL> create table employee ( id integer, proper name varchar2(10), dept varchar2(fifteen), salary integer, hiredon date )
Side by side create the control file that explains what needs to exist upload and where.
$ cat sqlldr-add-new.ctl load data infile '/home/ramesh/employee.txt' into table employee fields terminated past "," ( id, name, dept, salary )
Annotation: If you have the values inside the data file enclosed with double quote, use this in your control file: fields terminated by "," optionally enclosed past '"'
Note: If you don't have the tabular array created, you'll get the post-obit mistake message:
SQL*Loader-941: Error during describe of table EMPLOYEE ORA-04043: object EMPLOYEE does non exist
You lot can laissez passer the userid and countersign to the sqlldr command using any one of the following format. As yous encounter below, both of these will prompt y'all for control file location, equally it was not given in the command line.
$ sqlldr scott/tiger (or) $ sqlldr userid=scott/tiger control = SQL*Loader-287: No control file name specified.
Execute the sqlldr command to upload these new tape to the empty table past specifying both uid/pwd and the control file location as shown below.
$ sqlldr scott/tiger control=/abode/ramesh/sqlldr-add-new.ctl Commit point reached - logical record count 5
Verify the the records are created in the database
SQL> select * from employee; ID NAME DEPT Bacon HIREDON ---------- ---------- --------------- ---------- ------- 100 Thomas Sales 5000 200 Jason Technology 5500 300 Mayla Engineering 7000 400 Nisha Marketing 9500 500 Randy Technology 6000
This will create the output log file in the same name equally the data file, only with the .log extension (instead of .ctl). Partial output shown below.
$ cat sqlldr-add-new.log Command File: /home/ramesh/sqlldr-add-new.ctl Data File: /home/ramesh/employee.txt Table EMPLOYEE: 5 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows non loaded considering all fields were goose egg. Elapsed fourth dimension was: 00:00:00.04 CPU fourth dimension was: 00:00:00.00
If y'all are new to Oracle database, and like to install it, follow this Oracle 11g installation guide.
2. Inserting Additional Records
Let the states say you want to add two new employees to the employee table from the following newemployee.txt file.
$ vi newemployee.txt 600,Ritu,Bookkeeping,5400 700,Jessica,Marketing,7800
If you create a similar control file similar the previous example, you might get the following error bulletin.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-add-more.ctl SQL*Loader-601: For INSERT selection, table must be empty. Error on table EMPLOYEE
The in a higher place indicates that the tabular array should be empty before y'all can upload information using sql*loader.
If you like to insert more information to the tables without having to delete the existing rows, use the "append' command as shown in the post-obit command file.
$ 6 sqlldr-append-more.ctl load data infile '/dwelling house/ramesh/newemployee.txt' append into table employee fields terminated by "," ( id, name, dept, salary )
Now, if you exercise sqlldr this will append the information.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-suspend-more than.ctl Commit signal reached - logical record count two
Verify that the records are appended successfully
SQL> select * from employee; ID NAME DEPT Bacon HIREDON ---------- ---------- --------------- ---------- ------- 100 Thomas Sales 5000 200 Jason Applied science 5500 300 Mayla Technology 7000 400 Nisha Marketing 9500 500 Randy Technology 6000 600 Ritu Accounting 5400 700 Jessica Marketing 7800
3. Data inside the Command File using BEGINDATA
You can as well specify the data directly inside the control file itself using BEGINDATA keyword. i.eastward Anything that comes after BEGINDATA will be treated as data to be uploaded to the table as shown beneath.
$ cat sqlldr-add-new-with-data.ctl load data infile * into tabular array employee fields terminated past "," ( id, name, dept, salary ) begindata 100,Thomas,Sales,5000 200,Jason,Technology,5500 300,Mayla,Applied science,7000 400,Nisha,Marketing,9500 500,Randy,Technology,6000
Note: The infile will say '*' in this case, as there is no input data file name for this example.
Execute sqlldr to upload the data from the control file.
$ sqlldr scott/tiger control=/habitation/ramesh/sqlldr-add-new-with-data.ctl
4. Appointment format and Unlike Delimiter
This instance shows how to specify a date format in the control file and how to handle different delimiters in a information file
The following example has dissimilar delimiters ($ afterward name, ^ after department).
$ cat employee-date.txt 100,Thomas$Sales^5000,31-Jan-2008 200,Jason$Technology^5500,01-February-2005 300,Mayla$Engineering science^7000,10-Aug-2000 400,Nisha$Marketing^9500,12-Dec-2011 500,Randy$Technology^6000,01-JAN-2007
Create the following command file and indicate the field delimiters for each and every field using "terminated by" every bit shown beneath.
$ cat sqlldr-engagement.ctl load information infile '/dwelling/ramesh/employee-date.txt' into table employee fields terminated by "," ( id, name terminated past "$", dept terminated by "^", salary, hiredon DATE "dd-monday-yyyy" )
Load the data using sqlldr every bit shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-engagement.ctl
Verify that the information got loaded properly as shown below.
SQL> select * from employee; ID Proper noun DEPT Salary HIREDON ---------- ---------- --------------- ---------- --------- 100 Thomas Sales 5000 31-JAN-08 200 Jason Applied science 5500 01-FEB-05 300 Mayla Engineering science 7000 x-AUG-00 400 Nisha Marketing 9500 12-December-eleven 500 Randy Engineering 6000 01-JAN-07
5. Stock-still Length Information Upload
If you have a information file without data that are fixed length (i.due east without any delimiter), y'all can utilize this instance to upload this information.
For this example, let us utilize the following file which has information that are of stock-still length. For example, 1st iii characters are always employee number, Next v characters are always employee name, etc.
$ cat employee-stock-still.txt 200JasonTechnology5500 300MaylaTechnology7000 400NishaTechnology9500 500RandyTechnology6000
Create the following control file, where y'all specific the position of each and every field as shown below usig the "Position(kickoff:stop)" syntax.
$ cat sqlldr-fixed.ctl load data infile '/home/ramesh/employee-fixed.txt' into table employee fields terminated by "," ( id position(ane:3), name position(4:8), dept position(ix:18), bacon position(19:22) )
Load this fixed length data using the sqlldr equally shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-stock-still.ctl
Verify that the data got loaded.
SQL> select * from employee; ID NAME DEPT SALARY HIREDON ---------- ---------- --------------- ---------- --------- 200 Jason Technology 5500 300 Mayla Technology 7000 400 Nisha Applied science 9500 500 Randy Engineering 6000
half dozen. Change the information during upload
You can also massage the information and change it during upload based on certain rules.
In the following control file:
- id is incremented by 999 before uploading. i.e if the emp id is 100 in the data file, it will be loaded as 1099
- Convert the name to upper case and load it. This uses the upper function.
- If the department contains the value "Engineering" alter it to "Techies". This uses decode function
$ cat sqlldr-alter-information.ctl load data infile '/habitation/ramesh/employee.txt' into table employee fields terminated past "," ( id ":id+999", proper noun "upper(:name)", dept "decode(:dept,'Applied science','Techies', :dept)", salary )
Load the data using this control file which will massage the data before uploading it.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-change-data.ctl
Verify that the information got changed while loading as per our rules.
SQL> select * from employee; ID Proper name DEPT SALARY HIREDON ---------- ---------- --------------- ---------- --------- 1099 THOMAS Sales 5000 1199 JASON Techies 5500 1299 MAYLA Techies 7000 1399 NISHA Marketing 9500 1499 RANDY Techies 6000
seven. Load data from multiple files
To load data from multiple files, you just have to specify multiple infile in the control file.
The following control file loads data from two different data files (employee.txt and newemployee.txt) to the employee table.
$ sqlldr-add-multiple.ctl load data infile '/habitation/ramesh/employee.txt' infile '/habitation/ramesh/newemployee.txt' into table employee fields terminated by "," ( id, proper name, dept, salary )
Load the information using this control file which volition upload data from multiple data files as shown below.
$ sqlldr scott/tiger command=/dwelling/ramesh/sqlldr-add-multiple.ctl Commit indicate reached - logical record count 5 Commit indicate reached - logical record count 7
8. Load information to Multiple Tables
Create another tabular array called bonus which will take employee id and bonus columns.
create table bonus ( id integer, bonus integer );
Create the employee-bonus.txt data file that contains the fields: id, proper name, department, salary, bonus
$ cat employee-bonus.txt 100 Thomas Sales 5000 grand 200 Jason Technology 5500 2000 300 Mayla Technology 7000 2000 400 Nisha Marketing 9500 1000 500 Randy Technology 6000 3000
Create the command file as shown beneath, which volition upload the information from the above file to two different tables. As shown beneath, you should take 2 "into table" commands, and specify the position of the information which needs to be used to upload the data to that cavalcade.
$ cat sqlldr-multiple-tables.ctl load data infile '/home/ramesh/employee-bonus.txt' into table employee ( id position(1:iii), name position(5:x), dept position(12:21), bacon position(23:26)) into tabular array bonus ( id position(1:3), bonus position(28:31))
Load the information to multiple tables using this command file equally shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-multiple-tables.ctl
Verify that the data got loaded to multiple tables successfully.
SQL> select * from employee; ID Proper name DEPT SALARY HIREDON ---------- ---------- --------------- ---------- --------- 100 Thomas Sales 5000 200 Jason Technology 5500 300 Mayla Engineering science 7000 400 Nisha Marketing 9500 500 Randy Technology 6000 SQL> select * from bonus; ID BONUS ---------- ---------- 100 1000 200 2000 300 2000 400 1000 500 3000
ix. Handling Bad (Rejected) Records
In the post-obit example, we have two bad records. Employee id 300 and 500 has salary column which is not numeric.
$ true cat employee-bad.txt 100,Thomas,Sales,5000 200,Jason,Technology,5500 300,Mayla,Technology,7K 400,Nisha,Marketing,9500 500,Randy,Technology,6K
Use the following control file for this example.
$ cat sqlldr-bad.ctl load data infile '/abode/ramesh/employee-bad.txt' into tabular array employee fields terminated by "," ( id, proper noun, dept, bacon )
Load the data (including the invalid records) using this control file as shown below.
$ sqlldr scott/tiger control=/home/ramesh/sqlldr-bad.ctl Commit point reached - logical tape count five
Equally you lot see from the abvoe output, information technology yet says "logical record count 5", but yous should bank check the log files to come across if it has rejected any records.
The log file indicates that 2 records are rejected as shown below:
Control File: /dwelling house/ramesh/sqlldr-bad.ctl Data File: /abode/ramesh/employee-bad.txt Bad File: /home/ramesh/employee-bad.bad Discard File: none specified Table EMPLOYEE: three Rows successfully loaded. 2 Rows non loaded due to information errors.
By default the rejected records are stored in a file that has the same name every bit the data file (simply with .bad extension)
$ cat employee-bad.bad 300,Mayla,Technology,7K 500,Randy,Technology,6K
Every bit yous meet below, the employee table has only 3 records (as two of them were rejected).
SQL> select * from employee; ID Name DEPT SALARY HIREDON ---------- ---------- --------------- ---------- --------- 100 Thomas Sales 5000 200 Jason Applied science 5500 400 Nisha Marketing 9500
10. Load Specific Rows from a datafile
If you want to load only a specific records from a information file use the WHEN in the control file.
Add the line "when" next to "into table" line. In the following control file, the when clause indicates that it will load only the records that have dept as "Technology".
$ cat sqlldr-when.ctl load data infile '/home/ramesh/employee.txt' into table employee when dept = 'Engineering science' fields terminated past "," ( id, name, dept, bacon )
Load the selective information (simply the "Technology" records) using this control file equally shown below.
$ sqlldr scott/tiger control=/abode/ramesh/sqlldr-when.ctl Commit betoken reached - logical record count five
As you see from the in a higher place output, it still says "logical record count 5", but you lot should check the log files to see how many records were loaded, and how many records were discarded because it didn't friction match the when status.
The following from the log file shows that v records were read, and two of them were discarded every bit information technology didn't lucifer the when condition.
Discard File: none specified Full logical records read: 5 Total logical records discarded: 2
Verify that only the selective records were loaded into the table.
SQL> select * from employee; ID NAME DEPT Salary HIREDON ---------- ---------- --------------- ---------- --------- 200 Jason Technology 5500 300 Mayla Technology 7000 500 Randy Engineering science 6000
Source: https://www.thegeekstuff.com/2012/06/oracle-sqlldr/
0 Response to "Missing Delimiter (. Inserted). { L.42"
Post a Comment