Using Truncate in SQL LOADER

Hi,
 I am using the below sql loader script to load data into DB.  One of the column in the script is TITLE I want to truncate this column to 500 characters while loading. How can we do this in the below loader script.

LOAD DATA
INFILE    '/usr/summary/load.txt'
BADFILE   '/usr/Scripts/error_book.bad'
DISCARDFILE '/usr/Scripts/error_book.dsc'
APPEND
INTO TABLE INFO_STAGE
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
    ID,
    TITLE,
    TOTA_FILES,
    STATUS char "nvl(:STATUS,'N')"

)
new_perl_userAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
In the control file try:

...
  TITLE 'substr(:title,1,500)',
...
0
 
awking00Commented:
slightwv,
I've always used double quotes like
...,
TITLE "substr(:TITLE,1,500)",
 ...
Will it work with the single quotes as well?
0
 
new_perl_userAuthor Commented:
sorry I forgot to mention TITLE column already have a TRIM function declared.


LOAD DATA
INFILE    '/usr/summary/load.txt'
BADFILE   '/usr/Scripts/error_book.bad'
DISCARDFILE '/usr/Scripts/error_book.dsc'
APPEND
INTO TABLE INFO_STAGE
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
   ID"trim(:NLM_UNIQUE_ID)",
    TITLE"TRIM(:TITLE)",
    TOTA_FILES,
    STATUS char "nvl(:STATUS,'N')"

)
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
awking00Connect With a Mentor Commented:
The functions can be combined -
TITLE "TRIM(SUBSTR(:TITLE,1,500))"

Also, the attribute name and bind variable must match -
ID "TRIM(:ID)" or NLM_UNIQUE_ID "TRIM(:NLM_UNIQUE_ID)"
0
 
new_perl_userAuthor Commented:
My column in DB is declared as

TITLE VARCHAR2(500 bytes)
 and when I implemented the above change  it is still showing up error as:

Record 1: Rejected - Error on table INFO_STAGE, column TITLE.
Field in data file exceeds maximum length
0
 
slightwv (䄆 Netminder) Commented:
>>Will it work with the single quotes as well?

My bad.  I think it will tell you to use double quotes.

>>Field in data file exceeds maximum length

Are you running sqlloader from the local database server or from a remote client?

There could be a characterset mismatch or a multibyte characterset issue.

I'm not a multi-byte characterset person so probably cannot be much help.
0
 
new_perl_userAuthor Commented:
Any other solutions please.
0
 
awking00Commented:
Can you attach a copy of the '/usr/summary/load.txt' datafile (or a reasonable portion therof) that we might use to test?
0
 
new_perl_userAuthor Commented:
sure. Here is the load .txt file.


load.txt
0
 
slightwv (䄆 Netminder) Commented:
Can I assume by closing this you no longer need assistance?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.