Solved

SQL*LOADER -writing control file an execute sql loader from unix-easy questions

Posted on 2003-11-29
1
6,075 Views
Last Modified: 2013-12-12
HI all,
if my file is with extension csv file lets say aaa.csv
in the control file
i write this line
FIELDS TERMINATED BY '      '
my file is csv so what do i write after TERMINATED BY
is it ',' or "," or something else.
My secand qestion is ,when im in unix to execute the sqlldr
do i have to be in a spesific dirctory to operate the loader program?
when i write this command i get an error

sqlldr APPS/APPS@TEST  control=$PELE_TOP/bin/pel_load_shavit2000.ctl  data=$6/$5  log=$PELE_TOP/bin/PEL_LOAD_SHAVIT2000.log  bad=$PELE_TOP/bin/PEL_LOAD_SHAVIT2000.bad

instead of $variable i write what is neded and this is for sure not my problem.If u think my command line is ok what is the syntax error in my ctl file??? (now in my csv there are headers for each field how do i say to sqlloader not to relate to the record of the headers?


LOAD DATA
INTO TABLE MLM.MLM_AR_DATA
REPLACE
FIELDS TERMINATED BY '      '
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
  (CUSTOMER_NUM,
   INVOICE_DATE DATE "DD/MM/YYYY" ,
   JOURNAL_ENTRY_NUMBER,
   CURRENCY_CODE,
   ILS,
   USD,
   OTHER ,
   REFERENCE1 NULLIF (REFERENCE1="UNKNOWN") "SUBSTR(:REFERENCE1,1,20)",
   REFERENCE2 NULLIF (REFERENCE2 ="UNKNOWN") "SUBSTR(:REFERENCE2,1,20)",
   DESCRIPTION NULLIF (DESCRIPTION ="UNKNOWN") "SUBSTR(:DESCRIPTION,1,240)"
  )

0
Comment
Question by:yeitan
1 Comment
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 125 total points
Comment Utility
1)If it is a csv file,then as the name implies,you have a comma for seperating the fields.Hence your control file would be:

INTO TABLE <XXXXX>
fields terminated by ','

2)You need not be in specific header.However you need to set the Environment Variables:

export ORACLE_SID=DBSID
export ORACLE_BASE=/usr2/home/oracle/OraHome1
export ORACLE_HOME=/usr2/home/oracle/OraHome1

<What error are you getting?Echo all the variables that are being used to check if they contain proper values.For ex, echo $5>

3)To skip the Header,you can use the parameter:

skip -- Number of logical records to skip    (Default 0)

More on sqlloader at:

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96652/ch04.htm

HTH
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

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ā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

762 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

15 Experts available now in Live!

Get 1:1 Help Now