Total Pageviews

September 30, 2016

9/30/2016 01:20:00 AM


Oracle sql*Loader Issues
Oracle Applications :SQL Loader


Please specify the max size of what the data could be, like char(2000) otherwise the default is 255.

SQL*Loader does not have the concept of different character datatypes. Incoming character data is assumed to be type char with a default length of 255. Thus, if the character string in the source Data File is more than 255 characters you will receive this error.

Action Plan:

Change controlfile to define column as CHAR (2000)
Example:
The source Data File has data to be loaded into the table "xxemp"

This table has four columns:

emp_id number
ename varchar2
addresss varchar2
other_info varchar2

The data for comments in the source Data File 500 characters.

Oracle SQL* Loader and issues



Oracle Sql*Loader Control File
Oracle SQL: SQL* Loader


Original Control File:

load data
infile 'data.dat'
into table xxemp
fields terminated by ','
(emp_id,
ename,
addresss,
other_info)

This would fail because the incoming data into pj_notes is assumed to be char datatype AND thus it defaults to 255 characters.

Correct Control File:

load data
infile 'data.dat'
into table gvieo_paymnets
fields terminated by ','
(emp_id,
ename,
addresss,
other_info)

As a result of adding the datatype mapping for the cpj_notes column, the string will not be too large.

0 comments:

Post a Comment