Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.4K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 546 SQLcl
- 4K SQL Developer Data Modeler
- 187.1K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 443 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
insert data Sqlloader to oracle table using specfied format

Hi Team,
in SQL loaded that dat file contains the data as below format
1111320220124
1121420220124
Table structure
CREATE TABLE tmp_order(id number,sid number,exp_date date );
SQLLOADER CTL FILE
LOAD DATA
REPLACE
INTO TABLE tmp_order
TRAILING NULLCOLS
(
ID POSTION(1:3) INTEGER EXTERNAL(3),
SID POSTION(4:5) INTEGER EXTERNAL(2),
EXP_DATE POSTION(6:13) INTEGER EXTERNAL(8)
)
while load data into table look like below format
id sid exp_date
111 13 2022-01-24 00:00:00
112 14 2022-01-24 00:00:00
kindly help me how to create the ctl file and load the data as above format
Thanks
Best Answer
-
Hi, @User_IAP38
You can use this control file:
-- SQLLOADER CTL FILE LOAD DATA REPLACE INTO TABLE tmp_order DATE FORMAT "YYYYMMDD" TRAILING NULLCOLS ( ID POSITION(1:3) INTEGER EXTERNAL(3), SID POSITION(4:5) INTEGER EXTERNAL(2), EXP_DATE POSITION(6:13) DATE EXTERNAL(8) )
Make sure POSITION is spelled correctly
Answers
-
In that undelimited string of input data, what is the 'id', what is the 'sid', and what is the 'date'?
-
Hi, @User_IAP38
Whenever you have a problem, please post a complete test script that the people who want to help you can run to re-create the problem and test their ideas. In this case, include a CREATE TABLE statement and a complete data file, including any special cases you may need to handle, and as much of the control file as you can write. Always give your full Oracle version (e.g. 18.4.0.0.0).
in SQL loaded that csv file contains the data as below format
1111320220124
1121420220124
Is this really a csv file? There aren't any commas. Are you saying the csv file looks like this
A,1111320220124,1.6
B,1121420220124,.9
and the second column (always exactly 13 digits) needs to be broken into three columns (a three-digit NUMBER, a 2-digit NUMBER and a DATE)?
-
CSV = Character Separated Values
Your data = No characters separating anything.
Regardless, you can use position format in your SQL*Loader control file to specify the exact positions and sizes of each "field" in the data.
-
i have updated with actual code and details
Thanks
-
So, now you've updated it (which makes our answers look out of place, so don't do that, just add comments with further information if you must)... what's not working with the control file you've created?
-
Hi, @User_IAP38
You can use this control file:
-- SQLLOADER CTL FILE LOAD DATA REPLACE INTO TABLE tmp_order DATE FORMAT "YYYYMMDD" TRAILING NULLCOLS ( ID POSITION(1:3) INTEGER EXTERNAL(3), SID POSITION(4:5) INTEGER EXTERNAL(2), EXP_DATE POSITION(6:13) DATE EXTERNAL(8) )
Make sure POSITION is spelled correctly