Home » RDBMS Server » Server Utilities » SQL Loader Bind Size
SQL Loader Bind Size [message #670505] Mon, 09 July 2018 18:32 Go to next message
vharish006
Messages: 35
Registered: August 2015
Location: Chicago
Member
Hi All,

I'm trying to Load data from Excel to a table.

There are 3 description columns which have huge notes.Hence Defined them as data type CLOB.

CREATE TABLE XXXXX
(Product_Long_Description CLOB,
APTPS_Prod_Search_Key_Words__c CLOB,
APTPS_Product_Status__c CLOB);

When trying the SQL LOADER it throwing below errors.



1)value used for ROWS parameter changed from 64 to 9
Record 1: Rejected - Error on table PQINF.XXPQ_PRODUCT_SUBJECTS, column PRODUCT_LONG_DESCRIPTION.
Field in data file exceeds maximum length



2)Record 4: Rejected - Error on table PQINF.XXPQ_PRODUCT_SUBJECTS, column FILE_NAME.
ORA-00984: column not allowed here


The Excel file does not have CREATION_DATE and FILE_NAME COLUMNS.I'm just passing default value in the CTL File




OPTIONS ( skip=1)
LOAD DATA
INFILE 'Dialog_prods_final2.csv'
INTO TABLE PQINF.XXPQ_PRODUCT_SUBJECTS
TRUNCATE
Fields Terminated by ","
OPTIONALLY ENCLOSED BY '"'
(ID
,ISACTIVE
,HYPERION_BU
,HYPERION_PF_DESCRIPTION
,HYPERION_SBU
,ORACLE_PRODUCT_ID
,ORDERTYPE
,NAME
,DESCRIPTION
,INTERNAL_INFORMATION
,PRODUCT_LONG_DESCRIPTION
,APTPS_PROD_SEARCH_KEY_WORDS__C
,APTPS_PRODUCT_STATUS__C
,The_Arts
,ARCHITECTURE
,DANCE
,DESIGN
,FASHION
,FILM
,MUSIC
,THEATRE_AND_DRAMA
,VISUAL_ARTS
,BUSINESS
,ACCOUNTING_AND_TAX
,FINANCE
,ECONOMICS
,MARKET_RESEARCH
,DISSERTATIONS_AND_THESES
,GENERAL_K12_AND_PUBLIC_LIBRARY
,CAREERS
,COOKING_AND_HOME_ECONOMICS
,CULTURE
,GENEALOGY
,GENERAL_REFERENCE
,HOW_TO
,TRAVEL_TOURISM
,GOVERNMENT_DOCUMENTS
,EXECUTIVE
,JUDICIARY
,LEGISLATIVE
,REGULATORY
,HEALTH_MEDICINE
,COUNSELING_AND_THERAPY
,DENTISTRY
,HEALTH_MANAGEMENT_AND_POLICY
,MEDICINE
,NURSING_AND_ALLIED_HEALTH
,PHARMACEUTICAL
,PSYCHOLOGY
,PUBLIC_HEALTH
,REHABILITATION_THERAPY
,VETERINARY_MEDICINE
,HISTORY
,BLACK_HISTORY
,EARLY_MODERN_HISTORY
,MILITARY_AND_DIPLOMATICHISTORY
,NATIVE_AMERICAN_HISTORY
,UK_HISTORY
,US_HISTORY
,WOMENS_HISTORY
,WORLD_HISTORY
,INTERDISCIPLINARY
,LITERATURE
,BIBLIOGRAPHY
,BLACK_WRITING
,CRITICISM
,DRAMA
,FICTION
,LANGUAGE
,POETRY
,WORLD_LITERATURE
,NEWS_AND_NEWSPAPERS
,CONTEMPORARY_NEWS
,HISTORICAL_NEWS
,OBITUARIES
,RECENT_NEWS
,PATENTS
,SCIENCE_AND_TECHNOLOGY
,AGRICULTURE
,BIOLOGY
,CHEMISTRY
,COMPUTER_SCIENCE_AND_IT
,EARTH_ATMOSPHERIC_AND_AQUATIC
,ENGINEERING
,ENVIRONMENTAL
,MATHEMATICS
,MILITARY
,PHYSICS
,SOCIAL_SCIENCE
,ANTHROPOLOGY
,COUNSELING_AND_SOCIAL_WORK
,CRIMINAL_JUSTICE
,EDUCATION
,GLOBAL_STUDIES
,LAW
,LIBRARY_SCIENCE
,LINGUISTICS
,POLITICAL_SCIENCE
,RELIGION_AND_PHILOSOPHY
,SOCIAL_STUDIES
,SOCIOLOGY
,WOMEN_AND_GENDER_STUDIES
,LIBRARY_SYSTEMS_AND_TOOLS
,SERVICES_AND_FEES
,CREATION_DATE "SYSDATE"
,FILE_NAME "Dialog_Prods_Finals")



Please Suggest how these errors can be resolved.Thanks
Re: SQL Loader Bind Size [message #670513 is a reply to message #670505] Mon, 09 July 2018 23:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8856
Registered: November 2002
Location: California, USA
Senior Member
If you do not specify a field size for your data, then the default is 255 and if your data is longer than that, then you get, "Field in data file exceeds maximum length." So, you need to specify whatever the maximum size is, in your control file, such as:

PRODUCT_LONG_DESCRIPTION CHAR(5000)

To eliminate the "column not allowed here, you need to add the CONSTANT keyword in your control file:

file_name CONSTANT "Dialog_Prods_Finals"
Re: SQL Loader Bind Size [message #670518 is a reply to message #670513] Tue, 10 July 2018 08:16 Go to previous message
vharish006
Messages: 35
Registered: August 2015
Location: Chicago
Member
Hi Barbara,

I found the answer for this on similar post on here but thanks for your quick response.It solved my issue.

Appreciate your help.
Previous Topic: SQLLDR Error : Field in data file exceeds maximum length
Next Topic: SQL Loader Error
Goto Forum:
  


Current Time: Wed Jul 18 17:39:06 CDT 2018