[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Question Regarding sql loader

Posted on 2011-10-27
5
Medium Priority
?
402 Views
Last Modified: 2012-05-12
Hi,

Can anyone suggest me how to load the following txt file into oracle using sql loader. I tried something like  below but id not work.

8511322 
9804945 
0231553 
101260899 
101303436 
101503164 
101504249 
101533388 
101539482 
101539917 
10830110W 
1257113 
2542008W 
2542028W 
28411190W 
61450440W 
61541220W 
61560630W 
62960620W 
63011100W 
63341200W 
63341320W 

Open in new window


LOAD DATA
INFILE    '/usr/HOME/FILES/QA.txt'
BADFILE   '/usr/HOME/erro.bad'
DISCARDFILE '/usr/HOME/error.dsc' 
APPEND
INTO TABLE TEMP
TRAILING NULLCOLS
(
    ID "trim(:ID)",
    RECEIVED_DATE "SYSDATE",
    STATUS char "nvl(:STATUS,'N')"

)

Open in new window

0
Comment
Question by:new_perl_user
5 Comments
 

Author Comment

by:new_perl_user
ID: 37041361
was able to get it by implementing

ID "trim(:ID)" TERMINATED BY WHITESPACE
0
 

Author Comment

by:new_perl_user
ID: 37041395
One more help. The txt file I am specifying contains date in its name . How to specify it.

INFILE    '/usr/HOME/FILES/QA.txt'

Filename is  "QA_20111027000000.txt"

How to specify that filename  in ctl file

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37041498
>>ID "trim(:ID)" TERMINATED BY WHITESPACE

You sure this worked?  I don't see how you determine id from status using that control file.

>>How to specify that filename  in ctl file

Do not think you can once you are inside sqlloader.  You would need to do something fancy in the OS itself.
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 2000 total points
ID: 37042809
- if you were running this from command line/program/script, in your sqlloader syntax you can pass the filename without have to hardcoding it in the control file, DATA=<the infile filename> eg:

SQLLDR username/password control=yourctlfile.ctl data=QA_20111027000000.txt


- the following link provide you with other command line syntax:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_params.htm
0
 
LVL 21

Expert Comment

by:flow01
ID: 37043343
In ower unix enviroment in such a case
we would have a script with argument filename
that uses a parent ctl-file with tags

for example
filename '#filename#'

the scripts  creates a temporary ctl-file replacing the tag in the parentfile with the argument
and runs sqlloader with the temporary ctl-file

 
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

830 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