Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using Truncate in SQL LOADER

Posted on 2011-03-24
10
Medium Priority
?
763 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 77

Accepted Solution

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

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

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 32

Assisted Solution

by:awking00
awking00 earned 1000 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
 
LVL 77

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 32

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 77

Expert Comment

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

730 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