Solved

sqlldr- combine and insert two values - position(1:10) and position(20:30)  into one column

Posted on 2012-04-03
2
1,036 Views
Last Modified: 2012-04-03
i have to load a text file into oracle table using sqlldr.
I want to load characters from position(1:10) and position(20:30) into one column into my oracle table
I donot want to load characters from 10-20 since they are some invalid charactesr(like carriage returns etc) and it messes up my dataload of characters from position-20:30)
 
How can i do that using sqlldr
I tried
options( SKIP = 0 )
load data
infile 'myfile.txt' "str '\n'"
insert INTO
Table mytable
TRAILING NULLCOLS
 (  
allcols    position(1:10,20:30)  
 )

but that failed, I also tried using || concatenator but it did not work
pl help
0
Comment
Question by:at999
[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
2 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37803440
use boundfiller.

Here is a working example:

drop table tab1 purge;
create table tab1(col1 varchar2(10));

Open in new window


OPTIONS ( DIRECT=TRUE)
LOAD DATA
INFILE *
INTO TABLE TAB1
APPEND
FIELDS TERMINATED BY ','
(
part1 boundfiller position(1:3),
part2 boundfiller position(8:12),
col1  ":part1 || :part2"
)
begindata
abcdefghijklmnop

Open in new window

0
 

Author Closing Comment

by:at999
ID: 37803846
thanks a lot its working
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

734 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