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 Go to next message
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 #670551 is a reply to message #670550] Thu, 12 July 2018 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please post an actual example of data we can reproduce your problem.

Note that if data exceeds 4000 bytes I don't see how you could insert it in a 4000 byte field, anyway you try to manage it.

Re: SQL Loader Error [message #670552 is a reply to message #670551] Thu, 12 July 2018 10:51 Go to previous messageGo to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
Hi Michel,

Uploaded a csv with one row
  • Attachment: 12.csv
    (Size: 3.63KB, Downloaded 2415 times)
Re: SQL Loader Error [message #670553 is a reply to message #670552] Thu, 12 July 2018 11:00 Go to previous messageGo to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
Now I changed the Data type on the column from VARCHAR2(4000) to CLOB on the table definition but still the same error.
Re: SQL Loader Error [message #670554 is a reply to message #670553] Thu, 12 July 2018 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Now you have to change CHAR(4000) to CHAR(100000) for instance.

Re: SQL Loader Error [message #670555 is a reply to message #670554] Thu, 12 July 2018 11:06 Go to previous messageGo to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
Hi Michel,

Thanks for the quick response.

I have tried increasing the char(4000) to Char(6000) and then to char(12000) there are more rows loading but still some failing.

Any idea what is the maximum size i can increase the CHAR(XXXX).

I assume it will solve the issue.
Re: SQL Loader Error [message #670556 is a reply to message #670554] Thu, 12 July 2018 11:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 #670557 is a reply to message #670555] Thu, 12 July 2018 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As far as I remember in 11g the maximum is 1000000.

Re: SQL Loader Error [message #670558 is a reply to message #670557] Thu, 12 July 2018 11:26 Go to previous messageGo to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
Hi Michel,

I have increased to CHAR(25000) and it seems too be loading now.

Any idea how can i transpose the data in Subject to multiple rows ??

I want to have a SELECT query for the transpose for this specific column.

You are savior Michel,other day i have some issue on SQL loader and stumbled on one of your post that helped me resolved the issue:)
Re: SQL Loader Error [message #670559 is a reply to message #670558] Thu, 12 July 2018 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Any idea how can i transpose the data in Subject to multiple rows ??
How do you want to cut it? At each "/"?

Re: SQL Loader Error [message #670560 is a reply to message #670559] Thu, 12 July 2018 11:34 Go to previous messageGo to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
No want to cut it at ,

E.g: Applied Sciences/Agriculture and Food/Animal Science/Fishery Science,Applied Sciences/Bioscience/Genetic Modification,

Applied Sciences/Agriculture and Food/Animal Science/Fishery Science
Applied Sciences/Bioscience/Genetic Modification
Re: SQL Loader Error [message #670561 is a reply to message #670560] Thu, 12 July 2018 11:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
You're awesome:)Smile

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
/
Re: SQL Loader Error [message #670563 is a reply to message #670561] Thu, 12 July 2018 12:06 Go to previous messageGo to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
Michel,

Is there any possibility to include a where to get data for specific ID.

Re: SQL Loader Error [message #670564 is a reply to message #670563] Thu, 12 July 2018 12:13 Go to previous messageGo to next message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
Please ignore above update.I included WHERE
Re: SQL Loader Error [message #670565 is a reply to message #670561] Thu, 12 July 2018 12:58 Go to previous message
vharish006
Messages: 70
Registered: August 2015
Location: Chicago
Member
xx

[Updated on: Thu, 12 July 2018 13:49]

Report message to a moderator

Previous Topic: SQL Loader Bind Size
Next Topic: ORA-39014: One or more workers have prematurely exited. ORA-31671: Worker process DW00 had an unhand
Goto Forum:
  


Current Time: Thu Mar 28 13:38:29 CDT 2024