RE: Sqlloader question
Roland,
There are 2 techniques you can use. The first is what I call a 'dynamic
control file' - other posters have given you examples of that one, using
sed.
The next is create a 'loop loader'; essentially a script file that fires off
sqlldr for each loadable file. Here's an overly simple example:
export FILESPEC=/d31/datafiler/sema/incoming/konkurrenter.*
for FILENAME in $( ls ${FILESPEC} )
do
sqlldr ${USER-PW} control=${CTL} data=${FILENAME}
log=${LOGPATH}/${FILENAME}.log
done
Both techniques are explained in my IOUG paper SQL*Loader CAN Do That!. A
Windows example is also given. If you can't get to the IOUG web site and
would like a copy of the paper, send me an email.
David Scott
[EMAIL PROTECTED]
From: [EMAIL PROTECTED]
Date: Thu, 11 Sep 2003 14:12:49 +0200
Subject: Sqlloader question
Hallo,
Anyone whom could tell me if it is in an sqlloader possible to write for
instance this if you dont know the exactly name of file.
infile '/d31/datafiler/sema/incoming/konkurrenter.*'
If there are going to be files with different extensions fo rinstance the
first time a file is going to be inserted will be konkurrenter.txt.1
and the next time the file will have the name konkurrenter.txt.2 etc.
How should I handle this?
Thanksin advance
Roland
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Scott
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Sqlloader question
Hallo, Anyone whom could tell me if it is in an sqlloader possible to write for instance this if you dont know the exactly name of file. infile '/d31/datafiler/sema/incoming/konkurrenter.*' If there are going to be files with different extensions fo rinstance the first time a file is going to be inserted will be konkurrenter.txt.1 and the next time the file will have the name konkurrenter.txt.2 etc. How should I handle this? Thanksin advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Sqlloader question
The way I handled a similar situation was to create a default SQL*Loader file with something recognizable in the place that you want to do the substitution (in your case, the infile filename). Then, I wrote a shell script that created the correct filename and copied the default SQL*Loader file to the one that was going to be run, substituting the created name for the default name. In my case, I used it to determine which partition should be loaded, but the concept should still work. I used sed to do that: sed 6s/partition()/partition(`echo $IN_DATE_FMT`)/g em_contact_hist_ld.orig em_contact_hist_ld.ctl In the script, this was followed by a call to sqlldr with the control file em_contact_hist_ld.ctl. Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it. - Tom Lehrer. -Original Message- Sent: Thursday, September 11, 2003 9:14 AM To: Multiple recipients of list ORACLE-L Hallo, Anyone whom could tell me if it is in an sqlloader possible to write for instance this if you dont know the exactly name of file. infile '/d31/datafiler/sema/incoming/konkurrenter.*' If there are going to be files with different extensions fo rinstance the first time a file is going to be inserted will be konkurrenter.txt.1 and the next time the file will have the name konkurrenter.txt.2 etc. How should I handle this? Thanksin advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Scott Canaan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Faster Sqlloader Question
How can I do to sqlloader work faster. I tryed the DIRECT option, same result. I tryed DIRECT and PARALLEL option, same result I am working with an Oralce 8.1.6.3.0 patched sqlloader I loaded 20 000 lines in 1 minute and I wish to load 400 000 lines in the same time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Faster Sqlloader Question
Hi, Network performance could be a bottleneck. Do you run loader from server or from remote host? Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 30, 2002 2:58 PM How can I do to sqlloader work faster. I tryed the DIRECT option, same result. I tryed DIRECT and PARALLEL option, same result I am working with an Oralce 8.1.6.3.0 patched sqlloader I loaded 20 000 lines in 1 minute and I wish to load 400 000 lines in the same time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Faster Sqlloader Question
U can set the Buffer size to increase the work faster -Original Message- Sent: Tuesday, July 30, 2002 6:29 PM To: Multiple recipients of list ORACLE-L How can I do to sqlloader work faster. I tryed the DIRECT option, same result. I tryed DIRECT and PARALLEL option, same result I am working with an Oralce 8.1.6.3.0 patched sqlloader I loaded 20 000 lines in 1 minute and I wish to load 400 000 lines in the same time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ayyappan S INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Faster Sqlloader Question
Gilbert, disable or remove all constraints on the table being loaded. remove all indexes on the table being loaded. if all else fails, buy a faster machine with faster disk. good luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, July 30, 2002 8:59 AM To: Multiple recipients of list ORACLE-L How can I do to sqlloader work faster. I tryed the DIRECT option, same result. I tryed DIRECT and PARALLEL option, same result I am working with an Oralce 8.1.6.3.0 patched sqlloader I loaded 20 000 lines in 1 minute and I wish to load 400 000 lines in the same time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Faster Sqlloader Question
See Oracle SQ Loader - The definitive guide by Jonathan Gennick Amazon URL is http://www.amazon.com/exec/obidos/ASIN/1565929489/qid%3D1028034789/sr%3D11-1 /ref%3Dsr%5F11%5F1/104-2680480-7921519#product-details Also there is a good review (and examples) by a member of this list - Stephen Andert at http://oracle.oreilly.com/news/oraclesqlload_0401.html HTH John -Original Message- Sent: 30 July 2002 13:59 To: Multiple recipients of list ORACLE-L How can I do to sqlloader work faster. I tryed the DIRECT option, same result. I tryed DIRECT and PARALLEL option, same result I am working with an Oralce 8.1.6.3.0 patched sqlloader I loaded 20 000 lines in 1 minute and I wish to load 400 000 lines in the same time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Faster Sqlloader Question
Gilbert - Have you looked at: http://www.orafaq.com/faqloadr.htm#SPEED Are you saying that performance with and without direct produced the same time? There were some good suggestions on the list yesterday, I recall, but I didn't keep them. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, July 30, 2002 7:59 AM To: Multiple recipients of list ORACLE-L How can I do to sqlloader work faster. I tryed the DIRECT option, same result. I tryed DIRECT and PARALLEL option, same result I am working with an Oralce 8.1.6.3.0 patched sqlloader I loaded 20 000 lines in 1 minute and I wish to load 400 000 lines in the same time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Faster Sqlloader Question
From server, I put the table in nologging and I improve the loading by 84%. Now I am at 400 000 lines in 3 minutes and I wish 400 000 in less then 1 minutes. I do not have any trigger, index, constraintes or foreigh key. Any more about Thanks. -Message d'origine- De: Alexandre Gorbatchev [mailto:[EMAIL PROTECTED]] Date: mardi 30 juillet 2002 16:03 À: Multiple recipients of list ORACLE-L Objet: Re: Faster Sqlloader Question Hi, Network performance could be a bottleneck. Do you run loader from server or from remote host? Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 30, 2002 2:58 PM How can I do to sqlloader work faster. I tryed the DIRECT option, same result. I tryed DIRECT and PARALLEL option, same result I am working with an Oralce 8.1.6.3.0 patched sqlloader I loaded 20 000 lines in 1 minute and I wish to load 400 000 lines in the same time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Faster Sqlloader Question
Maybe partitioning on several disks? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 30, 2002 4:54 PM From server, I put the table in nologging and I improve the loading by 84%. Now I am at 400 000 lines in 3 minutes and I wish 400 000 in less then 1 minutes. I do not have any trigger, index, constraintes or foreigh key. Any more about Thanks. -Message d'origine- De: Alexandre Gorbatchev [mailto:[EMAIL PROTECTED]] Date: mardi 30 juillet 2002 16:03 À: Multiple recipients of list ORACLE-L Objet: Re: Faster Sqlloader Question Hi, Network performance could be a bottleneck. Do you run loader from server or from remote host? Alexandre - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 30, 2002 2:58 PM How can I do to sqlloader work faster. I tryed the DIRECT option, same result. I tryed DIRECT and PARALLEL option, same result I am working with an Oralce 8.1.6.3.0 patched sqlloader I loaded 20 000 lines in 1 minute and I wish to load 400 000 lines in the same time? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bernard, Gilbert INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sqlloader question
Lyuda, Did you get an answer for this? You can try using the SUBSTR command to read the 7 char data string and rebuild it into something longer like: col_dat substr(:col_seq,1,2) || '--' || substr(:col_seq,3,3) || '--' || substr(:col_seq,6,2) Hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, December 27, 2001 1:56 PM To: Multiple recipients of list ORACLE-L I need to load a text format file into my database using sqlloader. The table I am loading into contains a special code column 9 positions long with a following format ##--###--## (note dashes in between). Dashes are being stored in the database! (don't ask me why :-) ). My input file data for that column comes in straight text ( no dashes, of course ). It is 7 positions long. Is there a way to somehow input dashes using control file while loading data? I've looked 'concatenate' sqlloader command. It is not going to take care of my need. Thank you to everyone who replies. Have a safe a joyful new year. Lyuda Hoska -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
sqlloader question
I need to load a text format file into my database using sqlloader. The table I am loading into contains a special code column 9 positions long with a following format ##--###--## (note dashes in between). Dashes are being stored in the database! (don't ask me why :-) ). My input file data for that column comes in straight text ( no dashes, of course ). It is 7 positions long. Is there a way to somehow input dashes using control file while loading data? I've looked 'concatenate' sqlloader command. It is not going to take care of my need. Thank you to everyone who replies. Have a safe a joyful new year. Lyuda Hoska -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sqlloader question
Have a look at the section in sql loader on applying SQL to fields in theSLQ Loader documents else Is it possible for you to run the data through a Perl script to alter tha data before invoking sqlloader Cheers -- = Peter McLartyE-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 28/12/2001 04:55 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Fax to: Subject:sqlloader question I need to load a text format file into my database using sqlloader. The table I am loading into contains a special code column 9 positions long with a following format ##--###--## (note dashes in between). Dashes are being stored in the database! (don't ask me why :-) ). My input file data for that column comes in straight text ( no dashes, of course ). It is 7 positions long. Is there a way to somehow input dashes using control file while loading data? I've looked 'concatenate' sqlloader command. It is not going to take care of my need. Thank you to everyone who replies. Have a safe a joyful new year. Lyuda Hoska -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise.
RE: SQLLOADER question.
Hi, You can use the same with little modification. Since if there is more no. of columns, then terminated by is more headache. Load data infile * Append Into table tab1 fields terminated by ',' trailing nullcols ( col1 , col2 , col3 , col4 :col3*.07, col5) begindata 123,20010920,20,30,35 123,20010920,20,30,35 123,20010920,20,30,35 Regards, Nirmal. -Original Message- From: Mercadante, Thomas F [SMTP:[EMAIL PROTECTED]] Sent: Friday, September 21, 2001 11:16 PM To: Multiple recipients of list ORACLE-L Subject: RE: SQLLOADER question. Ron, try this: Load data infile * Append Into table tab1 trailing nullcols ( col1 terminated by , , col2 terminated by ,, col3 terminated by , , col4 terminated by , :col3*.07, col5 terminated by , ) begindata 123,20010920,20,30,35 123,20010920,20,30,35 123,20010920,20,30,35 I just ran it - works fine! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 21, 2001 3:21 PM To: Multiple recipients of list ORACLE-L List, Oracle 7.3.4 Using SqlLoader I load a table with 5 columns of data. I would like to be able to have column 4 be the value of (col3 * .07). I have RTFM and other books but it seems that you are not allowed to perform calculations during the load except the date functions. Am I correct in my understanding? Control file info: Load data Append Into table tab1 trailing nullcols ( col1 terminated by , , col2 terminated by , TO_DATE(:col2,'MMDD'), col3 terminated by , , col4 terminated by , , col5 terminated by , ) The source data has values in each location: 123,20010920,20,30,35 Any Ideas? Thanks, Ron ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nirmal Kumar Muthu Kumaran INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQLLOADER question.
Get yourself a copy of Oracle SQL*Loader by Gennick Mishra, O'Reilly Books. I reviewed the discussion of it in Ch 8 of the book. It looks like you need to script a Function and then reference the Function name in the Control File. Hope this helps. It's not to simple so you need to get the book. Ken Janusz, CPIM Database Conversion Lead Sufficient Systems, Inc. -Original Message- Sent: Friday, September 21, 2001 2:21 PM To: Multiple recipients of list ORACLE-L List, Oracle 7.3.4 Using SqlLoader I load a table with 5 columns of data. I would like to be able to have column 4 be the value of (col3 * .07). I have RTFM and other books but it seems that you are not allowed to perform calculations during the load except the date functions. Am I correct in my understanding? Control file info: Load data Append Into table tab1 trailing nullcols ( col1 terminated by , , col2 terminated by , TO_DATE(:col2,'MMDD'), col3 terminated by , , col4 terminated by , , col5 terminated by , ) The source data has values in each location: 123,20010920,20,30,35 Any Ideas? Thanks, Ron ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ken Janusz INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: SQLLOADER question.
Ron, try this: Load data infile * Append Into table tab1 trailing nullcols ( col1 terminated by , , col2 terminated by ,, col3 terminated by , , col4 terminated by , :col3*.07, col5 terminated by , ) begindata 123,20010920,20,30,35 123,20010920,20,30,35 123,20010920,20,30,35 I just ran it - works fine! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 21, 2001 3:21 PM To: Multiple recipients of list ORACLE-L List, Oracle 7.3.4 Using SqlLoader I load a table with 5 columns of data. I would like to be able to have column 4 be the value of (col3 * .07). I have RTFM and other books but it seems that you are not allowed to perform calculations during the load except the date functions. Am I correct in my understanding? Control file info: Load data Append Into table tab1 trailing nullcols ( col1 terminated by , , col2 terminated by , TO_DATE(:col2,'MMDD'), col3 terminated by , , col4 terminated by , , col5 terminated by , ) The source data has values in each location: 123,20010920,20,30,35 Any Ideas? Thanks, Ron ROR mª¿ªm -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sqlloader question
Lyuda, According to the good ol' doc, when you use a direct load with the ROWS param, Loader will count the rows it's building into blocks, and 'save' the blocks when each time ROWS is reached. I've used ROWS in direct loads, and it worked as advertised. The loads were still fast, and data was saved at the number of ROWS specified. There is a difference between a 'save' during a direct load with ROWS, and a COMMIT during a conventional load with ROWS. According to the doc, the only difference is that during a direct load, any indexes are marked unusable. If the load crashes, 'saved' rows will still be there, and you're indexes will need to be recreated. (You're indexes would need to be recreated anyway if a direct load crashes. Just that if you use ROWS, whatever's been saved so far is saved.) Hope this helps somewhat, Yosi -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 03, 2001 1:02 PM To: Multiple recipients of list ORACLE-L Subject: sqlloader question Hello oradba's, I have a general question about sqlloader. There is an option called ROWS. According to Oracle Complete reference by G.Koch and K.Loney ROWS is the number of rows to buffer together for an insert and commit. Default value is 64. According to one of my coworkers specifying ROWS in combination with LOAD=DIRECT is not a good idea. Supposedly it will confuse and slow down the load, possibly throw it to a non-direct load. The theory is based on the following idea: during direct load sql loader is not supposed to count and commit records. Does it sound like true? Lyuda Hoska -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: sqlloader question
ROWS takes on a slightly different semantic meaning depending on whether your load is direct-path or conventional, but the practical effect is pretty much the same. For a conventional path load, ROWS specifies the number of rows for the bind array, which ends up being the number of rows loaded between commits. For a direct-path load, ROWS specifies the number of rows to read from the input file before saving them to the database. The semantics of commit don't apply to direct-path load: for example, triggers don't fire. Specifying ROWS in conjunction with DIRECT will NOT cause your load to use the conventional path. Saving at the end of a direct-path load (the default) is best performance-wise, but the tradeoff is that if the load fails, you get to do it ALL over again. Specifying a value for ROWS puts a limit on the amount of the load that you will need to redo in the event of a failure. It's all a tradeoff. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org Tuesday, July 03, 2001, 1:02:25 PM, you wrote: lcc Hello oradba's, lcc I have a general question about sqlloader. lcc There is an option called ROWS. According to Oracle Complete reference by lcc G.Koch and K.Loney ROWS is the number of rows to buffer together for an lcc insert and commit. Default value is 64. lcc According to one of my coworkers specifying ROWS in combination with lcc LOAD=DIRECT is not a good idea. Supposedly it will confuse and slow down lcc the load, possibly throw it to a non-direct load. The theory is based on lcc the following idea: during direct load sql loader is not supposed to count lcc and commit records. lcc Does it sound like true? lcc Lyuda Hoska -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Sqlloader question solved.
HiStarting with 8i you have the "filler"option in SQL*Loader control file=which does what you want. Thanks Jack. It was indeed on 8i (I should have mentioned this) and the FILLER keyword worked like a charm. I love this list !! Lee The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system.
