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
Solved

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

Posted on 2003-11-29
1
6,139 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

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 post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 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