[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 800
  • Last Modified:

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')"

)
0
new_perl_user
Asked:
new_perl_user
  • 4
  • 3
  • 3
2 Solutions
 
slightwv (䄆 Netminder) 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
awking00Commented:
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now