Solved

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

Posted on 2003-11-29
1
6,132 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
ID: 9848265
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.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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.  …
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 recover a database from a user managed backup

803 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