Solved

Using Truncate in SQL LOADER

Posted on 2011-03-24
10
717 Views
Last Modified: 2012-05-11
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
Comment
Question by:new_perl_user
  • 4
  • 3
  • 3
10 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 35207739
In the control file try:

...
  TITLE 'substr(:title,1,500)',
...
0
 
LVL 31

Expert Comment

by:awking00
ID: 35207923
slightwv,
I've always used double quotes like
...,
TITLE "substr(:TITLE,1,500)",
 ...
Will it work with the single quotes as well?
0
 

Author Comment

by:new_perl_user
ID: 35208840
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
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 35208886
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
 

Author Comment

by:new_perl_user
ID: 35209113
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35209182
>>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
 

Author Comment

by:new_perl_user
ID: 35214871
Any other solutions please.
0
 
LVL 31

Expert Comment

by:awking00
ID: 35214934
Can you attach a copy of the '/usr/summary/load.txt' datafile (or a reasonable portion therof) that we might use to test?
0
 

Author Comment

by:new_perl_user
ID: 35215093
sure. Here is the load .txt file.


load.txt
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35217731
Can I assume by closing this you no longer need assistance?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to take different types of Oracle backups using RMAN.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now