Solved

Using Truncate in SQL LOADER

Posted on 2011-03-24
10
736 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 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
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 32

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
 
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 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 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.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

773 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