Home » RDBMS Server » Server Utilities » SQL Loader Error (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| SQL Loader Error [message #670550] |
Thu, 12 July 2018 10:21  |
 |
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
|
Member |
|
|
Hi All,
I'm Trying to load data to a table from csv.
Below is the table and data type on it.
CREATE TABLE XX.XXX_XXX_XXX
(
XX_ID NUMBER(30),
XX_NAME VARCHAR2(400),
ACTIVITY_STATUS VARCHAR2(255),
ECONOMIC_MODEL VARCHAR2(255),
GOVERNANCE VARCHAR2(255),
"LEVEL" INTEGER,
MISSION VARCHAR2(4000),
NUMBER_OF_SITES VARCHAR2(255),
SELF_DESCRIPTION VARCHAR2(255),
REACH VARCHAR2(255),
SUBJECTS VARCHAR2(4000));
Below is the Control File.
OPTIONS ( skip=1,BINDSIZE=12557648,READSIZE=12557648)
LOAD DATA
INFILE 'PROQ_classifications_FULL_20180503.csv'
INTO TABLE PQINF.RINGGOLD_IDENTIFY_SUBJECTS
TRUNCATE
Fields Terminated by ","
OPTIONALLY ENCLOSED BY '"'
(RINGGOLD_ID
,RINGGOLD_NAME
,ACTIVITY_STATUS
,ECONOMIC_MODEL
,GOVERNANCE
,"LEVEL"
,MISSION CHAR(4000)
,NUMBER_OF_SITES
,SELF_DESCRIPTION
,REACH
,SUBJECTS CHAR(4000))
I'm encountering error on SUBJECTS Columns
"Field in data file exceeds maximum length"
Below is how the data looks in Subjects column.Just giving a sample as it has too much data
"Applied Sciences/Agriculture and Food/Animal Science/Fishery Science,Applied Sciences/Bioscience/Genetic Modification,Applied Sciences/Computer Science/Computer Programming/Software Development,Applied Sciences/Computer Science/Computing Information Science,Applied Sciences/Computer Science/Human Computer Interaction,Applied Sciences/Engineering/Architectural Engineering,Applied Sciences/Engineering/Chemical Engineering,Applied Sciences/Engineering/Civil Engineering,Applied Sciences/Engineering/Civil Engineering/Construction,Applied Sciences/Engineering/Civil Engineering/Engineering Infrastructure,Applied Sciences/Engineering/Electronics,Applied Sciences/Engineering/Electronics/Electronic Components and Devices,Applied Sciences/Engineering/Electronics/Electronic Components and Devices/Computer Hardware,Applied Sciences"
Please suggest if some thing needs to be added to the CTL file.
Apologize me if not not asking the question right way.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL Loader Error [message #670556 is a reply to message #670554] |
Thu, 12 July 2018 11:09  |
 |
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> CREATE TABLE RINGGOLD_IDENTIFY_SUBJECTS (
2 XX_ID NUMBER(30),
3 XX_NAME VARCHAR2(400),
4 ACTIVITY_STATUS VARCHAR2(255),
5 ECONOMIC_MODEL VARCHAR2(255),
6 GOVERNANCE VARCHAR2(255),
7 "LEVEL" INTEGER,
8 MISSION VARCHAR2(4000),
9 NUMBER_OF_SITES VARCHAR2(255),
10 SELF_DESCRIPTION VARCHAR2(255),
11 REACH VARCHAR2(255),
12 SUBJECTS clob);
Table created.
SQL> host type c:\s12.ctl
OPTIONS ( skip=1,BINDSIZE=12557648,READSIZE=12557648)
LOAD DATA
INFILE 'c:\PROQ_classifications_FULL_20180503.csv'
INTO TABLE RINGGOLD_IDENTIFY_SUBJECTS
TRUNCATE
Fields Terminated by ","
OPTIONALLY ENCLOSED BY '"'
(XX_ID
,XX_NAME
,ACTIVITY_STATUS
,ECONOMIC_MODEL
,GOVERNANCE
,"LEVEL"
,MISSION CHAR(4000)
,NUMBER_OF_SITES
,SELF_DESCRIPTION
,REACH
,SUBJECTS CHAR(100000))
SQL> host sqlldr michel/michel control=c:\s12.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Jeu. Juil. 12 18:08:07 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 1
SQL> set lines 120
SQL> select * from RINGGOLD_IDENTIFY_SUBJECTS;
XX_ID
----------
XX_NAME
------------------------------------------------------------------------------------------------------------------------
ACTIVITY_STATUS
------------------------------------------------------------------------------------------------------------------------
ECONOMIC_MODEL
------------------------------------------------------------------------------------------------------------------------
GOVERNANCE
------------------------------------------------------------------------------------------------------------------------
LEVEL
----------
MISSION
------------------------------------------------------------------------------------------------------------------------
NUMBER_OF_SITES
------------------------------------------------------------------------------------------------------------------------
SELF_DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
REACH
------------------------------------------------------------------------------------------------------------------------
SUBJECTS
------------------------------------------------------------------------------------------------------------------------
1004
Aalborg Universitet
Active
Non-Profit
Academia,Government
0
Research/Perform,Teaching/Adult Education,Teaching/Graduate Education,Teaching/Higher Education,Teaching/Professional Ed
ucation
Multiple
University
Regional
Applied Sciences/Agriculture and Food/Animal Science/Fishery Science,Applied Sciences/Bioscience/Genetic Modification,Ap
plied Sciences/Computer Science/Computer Programming/Software Development,Applied Sciences/Computer Science/Computing In
formation Science,Applied Sciences/Computer Science/Human Computer Interaction,Applied Sciences/Engineering/Architectura
l Engineering,Applied Sciences/Engineering/Chemical Engineering,Applied Sciences/Engineering/Civil Engineering,Applied S
ciences/Engineering/Civil Engineering/Construction,Applied Sciences/Engineering/Civil Engineering/Engineering Infrastruc
ture,Applied Sciences/Engineering/Electronics,Applied Sciences/Engineering/Electronics/Electronic Components and Devices
,Applied Sciences/Engineering/Electronics/Electronic Components and Devices/Computer Hardware,Applied Sciences/Engineeri
ng/Telecommunications,Applied Sciences/Environmental Science,Applied Sciences/Environmental Science/Sustainability,Appli
ed Sciences/Nanoscience/Nanotechnology,Applied Sciences/Pharmaceutical Science/Pharmaceutics,Arts and Humanities,Arts an
d Humanities/Philosophy,Business and Management,Business and Management/Business Administration,Business and Management/
Finance,Economics,Economics/Econometrics,Education/International and Comparative Education,Education/Teacher Education,E
ducation/Teaching and Learning,Education/Tertiary and Higher Education,Health,Health/Allied Health/Music Therapy,Health/
Allied Health/Public Health,Health/Clinical Medicine/Biomedical Research,Health/Clinical Medicine/Immunology,Health/Clin
ical Medicine/Medical Research,Health/Clinical Medicine/Neurology,Health/Clinical Medicine/Physical Medicine/Pain Manage
ment,Health/Clinical Medicine/Sports Medicine,Health/Clinical Medicine/Sports Medicine/Athletic Training in Sports Medic
ine,Language,Language/Linguistics,Mathematics,Mathematics/Applied Mathematics,Mathematics/Computational Mathematics,Math
ematics/Pure Mathematics,Mathematics/Statistics,Political Science,Political Science/Civil and Human Rights,Political Sci
ence/Comparative Politics,Political Science/Foreign Policy,Political Science/International Relations,Political Science/P
olitical Philosophy,Political Science/Political Theory,Political Science/Public Affairs and Public Administration,Pure S
ciences,Pure Sciences/Biology,Pure Sciences/Biology/Marine Biology,Pure Sciences/Chemistry/Analytical Chemistry,Pure Sci
ences/Chemistry/Biochemistry/Bioorganic Chemistry,Pure Sciences/Chemistry/Catalysis,Pure Sciences/Chemistry/Environmenta
l Chemistry,Social Sciences,Social Sciences/Anthropology/Archaeology,Social Sciences/Anthropology/Cultural Anthropology,
Social Sciences/Area Studies/European Studies,Social Sciences/Area Studies/Global Affairs,Social Sciences/Built Environm
ent/Architecture,Social Sciences/Built Environment/Architecture/Landscape Architecture,Social Sciences/Built Environment
/Architecture/Rural Architecture,Social Sciences/Built Environment/Architecture/Urban Architecture,Social Sciences/Built
Environment/Planning,Social Sciences/Cultural Studies/Communication Studies,Social Sciences/Cultural Studies/Communicat
ion Studies/Public Relations,Social Sciences/Cultural Studies/Critical Theory,Social Sciences/Media Studies,Social Scien
ces/Psychology,Social Sciences/Psychology/Behavioural Psychology,Social Sciences/Psychology/Developmental Psychology
------------------------------------------------------------------------------------------------------------------------
1 row selected.[Updated on: Thu, 12 July 2018 11:10] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: SQL Loader Error [message #670561 is a reply to message #670560] |
Thu, 12 July 2018 11:51 |
|
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Example there, 11g solution in its next post.
SQL> col subject format a80
SQL> select XX_ID, column_value subject_nb,
2 regexp_substr(SUBJECTS, '[^,]+', 1, column_value) subject
3 from RINGGOLD_IDENTIFY_SUBJECTS,
4 table(cast(multiset(select level from dual
5 connect by level <= regexp_count(SUBJECTS,',')+1)
6 as sys.odciNumberList))
7 order by 1, 2
8 /
XX_ID SUBJECT_NB SUBJECT
---------- ---------- --------------------------------------------------------------------------------
1004 1 Applied Sciences/Agriculture and Food/Animal Science/Fishery Science
1004 2 Applied Sciences/Bioscience/Genetic Modification
1004 3 Applied Sciences/Computer Science/Computer Programming/Software Development
1004 4 Applied Sciences/Computer Science/Computing Information Science
1004 5 Applied Sciences/Computer Science/Human Computer Interaction
1004 6 Applied Sciences/Engineering/Architectural Engineering
1004 7 Applied Sciences/Engineering/Chemical Engineering
1004 8 Applied Sciences/Engineering/Civil Engineering
1004 9 Applied Sciences/Engineering/Civil Engineering/Construction
1004 10 Applied Sciences/Engineering/Civil Engineering/Engineering Infrastructure
1004 11 Applied Sciences/Engineering/Electronics
1004 12 Applied Sciences/Engineering/Electronics/Electronic Components and Devices
1004 13 Applied Sciences/Engineering/Electronics/Electronic Components and Devices/Compu
ter Hardware
1004 14 Applied Sciences/Engineering/Telecommunications
1004 15 Applied Sciences/Environmental Science
1004 16 Applied Sciences/Environmental Science/Sustainability
1004 17 Applied Sciences/Nanoscience/Nanotechnology
1004 18 Applied Sciences/Pharmaceutical Science/Pharmaceutics
1004 19 Arts and Humanities
1004 20 Arts and Humanities/Philosophy
1004 21 Business and Management
1004 22 Business and Management/Business Administration
1004 23 Business and Management/Finance
1004 24 Economics
1004 25 Economics/Econometrics
1004 26 Education/International and Comparative Education
1004 27 Education/Teacher Education
1004 28 Education/Teaching and Learning
1004 29 Education/Tertiary and Higher Education
1004 30 Health
1004 31 Health/Allied Health/Music Therapy
1004 32 Health/Allied Health/Public Health
1004 33 Health/Clinical Medicine/Biomedical Research
1004 34 Health/Clinical Medicine/Immunology
1004 35 Health/Clinical Medicine/Medical Research
1004 36 Health/Clinical Medicine/Neurology
1004 37 Health/Clinical Medicine/Physical Medicine/Pain Management
1004 38 Health/Clinical Medicine/Sports Medicine
1004 39 Health/Clinical Medicine/Sports Medicine/Athletic Training in Sports Medicine
1004 40 Language
1004 41 Language/Linguistics
1004 42 Mathematics
1004 43 Mathematics/Applied Mathematics
1004 44 Mathematics/Computational Mathematics
1004 45 Mathematics/Pure Mathematics
1004 46 Mathematics/Statistics
1004 47 Political Science
1004 48 Political Science/Civil and Human Rights
1004 49 Political Science/Comparative Politics
1004 50 Political Science/Foreign Policy
1004 51 Political Science/International Relations
1004 52 Political Science/Political Philosophy
1004 53 Political Science/Political Theory
1004 54 Political Science/Public Affairs and Public Administration
1004 55 Pure Sciences
1004 56 Pure Sciences/Biology
1004 57 Pure Sciences/Biology/Marine Biology
1004 58 Pure Sciences/Chemistry/Analytical Chemistry
1004 59 Pure Sciences/Chemistry/Biochemistry/Bioorganic Chemistry
1004 60 Pure Sciences/Chemistry/Catalysis
1004 61 Pure Sciences/Chemistry/Environmental Chemistry
1004 62 Social Sciences
1004 63 Social Sciences/Anthropology/Archaeology
1004 64 Social Sciences/Anthropology/Cultural Anthropology
1004 65 Social Sciences/Area Studies/European Studies
1004 66 Social Sciences/Area Studies/Global Affairs
1004 67 Social Sciences/Built Environment/Architecture
1004 68 Social Sciences/Built Environment/Architecture/Landscape Architecture
1004 69 Social Sciences/Built Environment/Architecture/Rural Architecture
1004 70 Social Sciences/Built Environment/Architecture/Urban Architecture
1004 71 Social Sciences/Built Environment/Planning
1004 72 Social Sciences/Cultural Studies/Communication Studies
1004 73 Social Sciences/Cultural Studies/Communication Studies/Public Relations
1004 74 Social Sciences/Cultural Studies/Critical Theory
1004 75 Social Sciences/Media Studies
1004 76 Social Sciences/Psychology
1004 77 Social Sciences/Psychology/Behavioural Psychology
1004 78 Social Sciences/Psychology/Developmental Psychology
78 rows selected. |
|
|
|
| Re: SQL Loader Error [message #670562 is a reply to message #670561] |
Thu, 12 July 2018 11:57 |
|
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
|
Member |
|
|
You're awesome:)
I'm just trying something meanwhile before you posted like this.I will try to use yours and see how it works
SELECT a.RINGGOLD_ID,
trim(regexp_substr(a.subjects, '[^,]+', 1, lines.column_value)) SUBJECTS
FROM PQINF.RINGGOLD_IDENTIFY_SUBJECTS a,
TABLE (CAST (MULTISET
(SELECT LEVEL FROM dual
CONNECT BY instr(a.subjects, ',', 1, LEVEL - 1) > 0
) AS sys.odciNumberList ) ) lines
ORDER BY a.RINGGOLD_ID
/
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Feb 07 00:59:19 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
