RE: Sql loader
1) Write a perl script which will rewrite the control file on the fly. 2) Write a script which will generate a symbolic link for the file du jour. 3) Do PURGE *.txt/KEEP=1 (I couldn't resist, it was the best OS ever). -- Mladen Gogala Oracle DBA > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On > Behalf Of [EMAIL PROTECTED] > Sent: Thursday, September 11, 2003 10:24 AM > To: Multiple recipients of list ORACLE-L > Subject: Sql loader > > > 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? > > Thanks in 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). > Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: SQL Loader
Pradeep, I don't know what you mean when you say SQL*Loader is "starting from the 50,000th record and going up". If there are 50k records, are you saying it is not loading them? Or is it reading them from 50,000 then 49,999 (i.e. in revers order)? How are you determining what order they are being read? Regarding external tables, the way they work is by reading the file from the first block to either the end or till it finds what it needs. Thus, external tables will read in the order the records are in the file. I stand ready for my ramblings to be corrected by anyone who can explain better :) Stephen >>> [EMAIL PROTECTED] 06/01/03 10:35PM >>> Dear All, I don't know whether SQL Loader behaves like this always or for me only. I am loading a .dat file which has 5 records. I noticed that SQL loader is starting from the 50,000th record and going upwards, while external tables is starting from the 1st record and going downwards. Could somebody help me confirming this? Regards, Pradeep -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pradeep Kumar G 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: Stephen Andert 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: SQL Loader
I think the query is displaying data from the table in the order in which rows were inserted by SQL*Loader. Whereas, the query to read the same data from the external table is reading the file from the first physical record. So it appears reversed.. - Kirti Stephen Andert <[EMAIL PROTECTED]> wrote: Pradeep, I don't know what you mean when you say SQL*Loader is "starting fromthe 50,000th record and going up". If there are 50k records, are yousaying it is not loading them? Or is it reading them from 50,000 then49,999 (i.e. in revers order)? How are you determining what order theyare being read? Regarding external tables, the way they work is by reading the filefrom the first block to either the end or till it finds what it needs. Thus, external tables will read in the order the records are in thefile. I stand ready for my ramblings to be corrected by anyone who canexplain better :)Stephen >>> [EMAIL PROTECTED] 06/01/03 10:35PM >>>Dear All,I don't know whether SQL Loader behaves like this always or for meonly. I am loading a .dat file which has 5 records.I noticed that S! QL loader is starting from the 50,000th record andgoing upwards, while external tables is starting from the 1st recordand going downwards.Could somebody help me confirming this?Regards,Pradeep Do you Yahoo!? Free online calendar with sync to Outlook(TM).
Re: SQL Loader
When I queried the table to which SQL Loader loaded data from the .dat file, the first record in the table was the 5th record and 4,49998... Kirtikumar Deshpande yahoo.com>cc: Sent by: Subject: Re: SQL Loader [EMAIL PROTECTED] 06/02/03 11:02 PM Please respond to ORACLE-L I think the query is displaying data from the table in the order in which rows were inserted by SQL*Loader. Whereas, the query to read the same data from the external table is reading the file from the first physical record. So it appears reversed.. - Kirti Stephen Andert <[EMAIL PROTECTED]> wrote: Pradeep, I don't know what you mean when you say SQL*Loader is "starting from the 50,000th record and going up". If there are 50k records, are you saying it is not loading them? Or is it reading them from 50,000 then 49,999 (i.e. in revers order)? How are you determining what order they are being read? Regarding external tables, the way they work is by reading the file from the first block to either the end or till it finds what it needs. Thus, external tables will read in the order the records are in the file. I stand ready for my ramblings to be corrected by anyone who can explain better :) Stephen >>> [EMAIL PROTECTED] 06/01/03 10:35PM >>> Dear All, I don't know whether SQL Loader behaves like this always or for me only. I am loading a .dat file which has 5 records. I noticed that S! QL loader is starting from the 50,000th record and going upwards, while external tables is starting from the 1st record and going downwards. Could somebody help me confirming this? Regards, Pradeep Do you Yahoo!? Free online calendar with sync to Outlook(TM). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pradeep Kumar G 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: SQL Loader
Title: RE: SQL Loader If SQL loader loads a table it takes the first record in the file and stuffs it in the top of the table... like a stack... last in, first out so if you were to read the table like you read the sequential file, it is in there with the "bottom" record in the file as the "top" row in the table...so SQL loader is, in effect, "going" from the first record in the file, it just pushes it on the stack... and when you are reading you are popping it off (although leaving it physically in place where a stack would pop it off and then discard it). I haven't really played a lot with external tables, but I would imagine that Kirti's explanation is quite logical. It takes the first block of records in the external table, and inserts it as the first (or next if you are appending it) block in the "regular" table... so it appears to be in the "correct" order. I guess that, in a sense then, that external tables are more of a queue type structure... filling up top to bottom... ajw April Wells Oracle DBA/Oracle Apps DBA Corporate Systems Amarillo Texas You will recognize your own path when you come upon it, because you will suddenly have all the energy and imagination you will ever need. ~ Jerry Gillies ~ -Original Message- From: Pradeep Kumar G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 03, 2003 6:00 AM To: Multiple recipients of list ORACLE-L Subject: Re: SQL Loader When I queried the table to which SQL Loader loaded data from the .dat file, the first record in the table was the 5th record and 4,49998... Kirtikumar Deshpande yahoo.com> cc: Sent by: Subject: Re: SQL Loader [EMAIL PROTECTED] 06/02/03 11:02 PM Please respond to ORACLE-L I think the query is displaying data from the table in the order in which rows were inserted by SQL*Loader. Whereas, the query to read the same data from the external table is reading the file from the first physical record. So it appears reversed.. - Kirti Stephen Andert <[EMAIL PROTECTED]> wrote: Pradeep, I don't know what you mean when you say SQL*Loader is "starting from the 50,000th record and going up". If there are 50k records, are you saying it is not loading them? Or is it reading them from 50,000 then 49,999 (i.e. in revers order)? How are you determining what order they are being read? Regarding external tables, the way they work is by reading the file from the first block to either the end or till it finds what it needs. Thus, external tables will read in the order the records are in the file. I stand ready for my ramblings to be corrected by anyone who can explain better :) Stephen >>> [EMAIL PROTECTED] 06/01/03 10:35PM >>> Dear All, I don't know whether SQL Loader behaves like this always or for me only. I am loading a .dat file which has 5 records. I noticed that S! QL loader is starting from the 50,000th record and going upwards, while external tables is starting from the 1st record and going downwards. Could somebody help me confirming this? Regards, Pradeep Do you Yahoo!? Free online calendar with sync to Outlook(TM). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pradeep Kumar G INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To R
Re: SQL Loader
That's what I thought. LIFO. Why does the order matter? Can you sort the result to suit your needs? - Kirti --- Pradeep Kumar G <[EMAIL PROTECTED]> wrote: > > When I queried the table to which SQL Loader loaded data from the .dat > file, the first record in the table was the 5th record and > 4,49998... > list ORACLE-L > <[EMAIL PROTECTED]> > yahoo.com>cc: > > > Sent by: Subject: Re: SQL Loader > > > [EMAIL PROTECTED] > > > > > I think the query is displaying data from the table in the order in > which rows were inserted by SQL*Loader. > Whereas, the query to read the same data from the external table is > reading the file from the first physical record. So it appears > reversed.. > > - Kirti > > Stephen Andert <[EMAIL PROTECTED]> wrote: > Pradeep, > > I don't know what you mean when you say SQL*Loader is "starting from > the 50,000th record and going up". If there are 50k records, are you > saying it is not loading them? Or is it reading them from 50,000 then > 49,999 (i.e. in revers order)? How are you determining what order > they > are being read? > > Regarding external tables, the way they work is by reading the file > from the first block to either the end or till it finds what it > needs. > Thus, external tables will read in the order the records are in the > file. > > I stand ready for my ramblings to be corrected by anyone who can > explain better :) > > Stephen > > >>> [EMAIL PROTECTED] 06/01/03 10:35PM >>> > > Dear All, > > I don't know whether SQL Loader behaves like this always or for me > only. I am loading a .dat file which has 5 records. > I noticed that S! QL loader is starting from the 50,000th record and > going upwards, while external tables is starting from the 1st record > and going downwards. > > Could somebody help me confirming this? > > Regards, > Pradeep > __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: SQL loader
Hello Nirmal, The FILLER command is available in sqlloader to allow you to miss a field out something like LOAD (field 1 ... field2 ... dum_rec FILLER, field3 ) may work but I am not sure that this will work in 7.3 of Oracle as I think FILLER came out in 8.0x However an easy way ( I was going to say the easiest but I figured someone would send in a 1 character script to achieve the same thing) is to use awk to concatanate the fields as in the following example cat j.txt a ccc cat j.txt | awk '{print $1,$2 $3,$4 }' a ccc I think that gives you what you want and you just use your loader routine to load the amended data HTH John -Original Message- Sent: 10 November 2002 07:33 To: Multiple recipients of list ORACLE-L Hi list, LOAD DATA INFILE 'c:\temp\file2.csv' APPEND INTO TABLE dc_temp1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ( name, old_po_box, dn, gsm, nregc, cr_no, new_po_box , remark, ncli) The above is my control file for loading data. i need to refer the two columns of data from the datafile 'c:\temp\file2.csv' should go into one column(remark) of my table. Oracle7.3.1. Any ideas pls. Thanks. Nirmal. -- 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 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.com -- 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: SQL Loader
BDY.RTF Description: RTF file
RE: SQL LOADER
IA, like this: ( col1 char , col_seq "test_seq.nextval" ) where test_seq is a sequence HTH Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, December 19, 2001 6:50 AM To: Multiple recipients of list ORACLE-L using sql loader it is possible to populate fields with a sequnce e.g INTO TABLE dept (deptno sequence(2, 3), but i want to use my own sequence , i.e my_seq.nextval how is this done? reagards IA -- 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).
Re: SQL LOADER
Hi, I am trying to assign a default value for a column from my sequence and i am unable to do that. How do you give it in the control file? Thanks in advance. Regards Sundar Mercadante, Thomas F wrote: >IA, > >like this: > >( col1 char , > col_seq "test_seq.nextval" >) > >where test_seq is a sequence > >HTH > >Tom Mercadante >Oracle Certified Professional > > >-Original Message- >Sent: Wednesday, December 19, 2001 6:50 AM >To: Multiple recipients of list ORACLE-L > > >using sql loader it is possible to populate fields with a sequnce e.g >INTO TABLE dept >(deptno sequence(2, 3), > >but i want to use my own sequence , i.e my_seq.nextval > >how is this done? > >reagards >IA > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sundar 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: SQL*Loader
David, What does your log file show as the reason for failure? John -Original Message- Sent: 08 April 2002 17:16 To: Multiple recipients of list ORACLE-L I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY "," ENCLOSED BY '"' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')", TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann 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: John Hallas 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: SQL*Loader
Hum, I could be reading this wrong but it looks like you are converting a char to a date and then putting it into a char column If its a char on the table then don't convert it to a date first. Otherwise change the data type to date. DATETESTED char "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')", -Original Message- Sent: Monday, April 08, 2002 10:10 AM To: Multiple recipients of list ORACLE-L David, What does your log file show as the reason for failure? John -Original Message- Sent: 08 April 2002 17:16 To: Multiple recipients of list ORACLE-L I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY "," ENCLOSED BY '"' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')", TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann 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: John Hallas 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: Kimberly Smith 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: SQL*Loader
Dave, I bet you the single quotes around the data string are messing you up. You could try the following: DATETESTED char "rtrim(ltrim(to_date(:DateTested,'mm/dd/ hh:mi:ss pm'),),)", Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 12:16 PM To: Multiple recipients of list ORACLE-L I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY "," ENCLOSED BY '"' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')", TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann 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: SQL*Loader
Here is an example of my logfile that it is generated: SQL*Loader: Release 8.1.6.0.0 - Production on Mon Apr 8 10:06:31 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved. Control File: app_dev.testcaseuatstatus.ctl Data File: app_dev.testcaseuatstatus.dat Bad File: app_dev.testcaseuatstatus.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65536 bytes Continuation:none specified Path used: Conventional Table APP_DEV.TESTCASEUATSTATUS, loaded from every logical record. Insert option in effect for this table: INSERT TRAILING NULLCOLS option in effect Column Name Position Len Term Encl Datatype -- -- - -- --- TESTCASESTATUSIDFIRST 4 INTEGER TESTCASEID NEXT 4 INTEGER USERID NEXT 4 INTEGER CORDID NEXT 4 INTEGER UATASSIGNED NEXT 4 INTEGER PASSFAILSTATUSID NEXT 4 INTEGER DATETESTED NEXT * ," CHARACTER SQL string for column : "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')" TASKID NEXT 4 INTEGER RETEST NEXT 4 INTEGER ASSIDNEXT 4 INTEGER NONVALID NEXT 4 INTEGER Record 1: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 2: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 3: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 4: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 5: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Table APP_DEV.TESTCASEUATSTATUS: 0 Rows successfully loaded. 5 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 19072 bytes(64 rows) Space allocated for memory besides bind array:0 bytes Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 5 Total logical records discarded:0 Run began on Mon Apr 08 10:06:31 2002 Run ended on Mon Apr 08 10:06:36 2002 Elapsed time was: 00:00:05.96 CPU time was: 00:00:03.92 thanks alot, David Ehresmann. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John Hallas > Sent: Monday, April 08, 2002 12:10 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL*Loader > > > David, > What does your log file show as the reason for failure? > John > -Original Message- > Sent: 08 April 2002 17:16 > To: Multiple recipients of list ORACLE-L > > I am trying to user SQL*Loader to load some tables in my 8i database. The > data will not load. It seems to have to do with the format. In > particular > the date format. Can anybody help? I have messed with this for 2 days. > > Here is my data file (only 5 rows displayed): > > 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 > 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 > 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 > 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 > 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 > > Here is my control file: > > LOAD DATA > INSERT > INTO TABLE APP_DEV.TESTCASEUATSTATUS > FIELDS TERMINATED BY "," ENCLOSED BY '"' > TRAILING NULLCOLS > (TESTCASESTATUSID INTEGER, > TESTCASEID INTEGER, > USERID INTEGER, > CORDID INTEGER, > UATASSIGNED INTEGER, > PASSFAILSTATUSID INTEGER, > DATETESTED char "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')", > TASKID INTEGER, > RETEST INTEGER, > ASSID INTEGER, > NONVALID INTEGER) > > I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string > for the date field. > > > David Ehresmann > Oracle DBA 8i OCP > MCI Worldcom > [EMAIL PROTECTED] > 972.656.1015 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: David Ehresmann > INET: [EMAIL PROTECTED] > > Fat City Network Services
RE: SQL*Loader
Dave, I just ran the following and it worked just fine: Sql_Loader file: LOAD DATA INFILE 'test_loader1.dat' replace INTO TABLE test ( DATETIME POSITION(01:21) CHAR "to_date(rtrim(ltrim(:datetime,''''),''''),'-mm-dd-hh24.mi.ss')", USERID POSITION(23:30) CHAR) Data File: '1999-02-05-09.26.13' 416000ZHUP '1999-02-05-09.26.13' 416000ZHUP '1999-02-05-09.26.13' 416000ZHUP Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, April 08, 2002 1:57 PM To: Multiple recipients of list ORACLE-L Here is an example of my logfile that it is generated: SQL*Loader: Release 8.1.6.0.0 - Production on Mon Apr 8 10:06:31 2002 (c) Copyright 1999 Oracle Corporation. All rights reserved. Control File: app_dev.testcaseuatstatus.ctl Data File: app_dev.testcaseuatstatus.dat Bad File: app_dev.testcaseuatstatus.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65536 bytes Continuation:none specified Path used: Conventional Table APP_DEV.TESTCASEUATSTATUS, loaded from every logical record. Insert option in effect for this table: INSERT TRAILING NULLCOLS option in effect Column Name Position Len Term Encl Datatype -- -- - -- --- TESTCASESTATUSIDFIRST 4 INTEGER TESTCASEID NEXT 4 INTEGER USERID NEXT 4 INTEGER CORDID NEXT 4 INTEGER UATASSIGNED NEXT 4 INTEGER PASSFAILSTATUSID NEXT 4 INTEGER DATETESTED NEXT * ," CHARACTER SQL string for column : "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')" TASKID NEXT 4 INTEGER RETEST NEXT 4 INTEGER ASSIDNEXT 4 INTEGER NONVALID NEXT 4 INTEGER Record 1: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 2: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 3: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 4: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Record 5: Rejected - Error on table APP_DEV.TESTCASEUATSTATUS, column DATETESTED. Initial enclosure character not found Table APP_DEV.TESTCASEUATSTATUS: 0 Rows successfully loaded. 5 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 19072 bytes(64 rows) Space allocated for memory besides bind array:0 bytes Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 5 Total logical records discarded:0 Run began on Mon Apr 08 10:06:31 2002 Run ended on Mon Apr 08 10:06:36 2002 Elapsed time was: 00:00:05.96 CPU time was: 00:00:03.92 thanks alot, David Ehresmann. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of John Hallas > Sent: Monday, April 08, 2002 12:10 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL*Loader > > > David, > What does your log file show as the reason for failure? > John > -Original Message- > Sent: 08 April 2002 17:16 > To: Multiple recipients of list ORACLE-L > > I am trying to user SQL*Loader to load some tables in my 8i database. The > data will not load. It seems to have to do with the format. In > particular > the date format. Can anybody help? I have messed with this for 2 days. > > Here is my data file (only 5 rows displayed): > > 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 > 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 > 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 > 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 > 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 > > Here is my control file: > > LOAD DATA > INSERT > INTO TABLE APP_DEV.TESTCASEUATSTATUS > FIELDS TERMINATED BY "," ENCLOSED BY '"' > TRAILING NULLCOLS > (TESTCASESTATUSID INTEGER, > TESTCASEID INTEGER, > USERID INTEGER, > CORDID INTEGER
RE: SQL*Loader
David, In the FIELD clause, shouldn't it be OPTIONALLY ENCLOSED BY ? -Original Message- Sent: Monday, April 08, 2002 12:16 PM To: Multiple recipients of list ORACLE-L I am trying to user SQL*Loader to load some tables in my 8i database. The data will not load. It seems to have to do with the format. In particular the date format. Can anybody help? I have messed with this for 2 days. Here is my data file (only 5 rows displayed): 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 Here is my control file: LOAD DATA INSERT INTO TABLE APP_DEV.TESTCASEUATSTATUS FIELDS TERMINATED BY "," ENCLOSED BY '"' TRAILING NULLCOLS (TESTCASESTATUSID INTEGER, TESTCASEID INTEGER, USERID INTEGER, CORDID INTEGER, UATASSIGNED INTEGER, PASSFAILSTATUSID INTEGER, DATETESTED char "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')", TASKID INTEGER, RETEST INTEGER, ASSID INTEGER, NONVALID INTEGER) I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string for the date field. David Ehresmann Oracle DBA 8i OCP MCI Worldcom [EMAIL PROTECTED] 972.656.1015 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: David Ehresmann 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: Hand, Michael T 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: SQL*Loader
I used this string: "to_date(rtrim(ltrim(:DateTested,''),''),'mm/dd/ hh:mi:ss pm')" TESTCASESTATUSID TESTCASEID USERID columns continue... -- -- 858533944 741617974 808202803 858534200 741945654 808202803 858534456 741356086 808202803 858534712 741421622 808202803 858534968 741487158 808202803 5 rows went in, but this is what I got! Any ideas? thanks, David. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Mercadante, > Thomas F > Sent: Monday, April 08, 2002 12:57 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL*Loader > > > Dave, > > I bet you the single quotes around the data string are messing > you up. You > could try the following: > > DATETESTED char "rtrim(ltrim(to_date(:DateTested,'mm/dd/ hh:mi:ss > pm'),''''),'''')", > > Hope this helps. > > Tom Mercadante > Oracle Certified Professional > > > -Original Message- > Sent: Monday, April 08, 2002 12:16 PM > To: Multiple recipients of list ORACLE-L > > > I am trying to user SQL*Loader to load some tables in my 8i database. The > data will not load. It seems to have to do with the format. In > particular > the date format. Can anybody help? I have messed with this for 2 days. > > Here is my data file (only 5 rows displayed): > > 80,3614,32,0,1,2,'12/20/2001 2:34:42 PM',1860,0,,0 > 81,3619,32,0,1,1,'12/20/2001 2:38:42 PM',1861,0,,0 > 82,3620,32,0,1,1,'12/20/2001 2:41:37 PM',1861,0,,0 > 83,3621,32,0,1,2,'12/20/2001 2:42:30 PM',1861,0,,0 > 84,3622,32,0,1,2,'12/20/2001 2:42:15 PM',1861,0,,0 > > Here is my control file: > > LOAD DATA > INSERT > INTO TABLE APP_DEV.TESTCASEUATSTATUS > FIELDS TERMINATED BY "," ENCLOSED BY '"' > TRAILING NULLCOLS > (TESTCASESTATUSID INTEGER, > TESTCASEID INTEGER, > USERID INTEGER, > CORDID INTEGER, > UATASSIGNED INTEGER, > PASSFAILSTATUSID INTEGER, > DATETESTED char "to_date(:DateTested,'mm/dd/ hh:mi:ss pm')", > TASKID INTEGER, > RETEST INTEGER, > ASSID INTEGER, > NONVALID INTEGER) > > I have also tried: DATETESTED date 'mm/dd/ hh:mi:ss pm' this string > for the date field. > > > David Ehresmann > Oracle DBA 8i OCP > MCI Worldcom > [EMAIL PROTECTED] > 972.656.1015 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: David Ehresmann > 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: David Ehresmann 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: SQL Loader
Hello Shankar, You can'n rely on rowid in your case. It would be much better to add additional field into your table and write rownum to it. You can generate record number automatically by SQL Loader. Here is simple example how to do it: LOAD DATA INFILE 'yourfile.dat' BADFILE 'load.bad' INSERT INTO TABLE YOUR_TABLE trailing nullcols ( R_NUMBER RECNUM, TABLE_ROW) you can also use SEQUENCE keyword instead of RECNUM. Thursday, May 16, 2002, 2:53:21 PM, you wrote: RCCC> Hi friends, RCCC> I have to load a input file into a oracle table . The table has only RCCC> one field which will store each line as a single row . The order of the file RCCC> is very important for my further process. sometimes the order of the file is RCCC> changed when it is stored in the file. there is no sequence or unique id RCCC> present in the file. RCCC> Can you tell me why this ordering is being changed .when u re-run RCCC> the sql loader , it is working properly. not able to simulate why this is RCCC> happening . RCCC> Is it any way to specify in loader just fetch a single row from the RCCC> file and write it before fetching next. RCCC> Best Regards, RCCC> Shankar -- Best regards, Sergeymailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergey V Dolgov 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: SQL Loader
>Hi friends, >I have to load a input file into a oracle >table . The table has only >one field which will store each line as a single >row . The order of the file >is very important for my further process. Perhaps you should re-read a paper published in 1970 in 'Communications of the ACM' by an Edward Codd. IMHO you do not need a relational database at this stage of your process. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: SQL Loader
Client is not accepting to use the utl_file for reading data from the file. i have to fetch each line from the file and process it for storing the information in various tables. so apart from sql loader can u suggest other method (excluding utl_file) for doing the same operation. Best Regards, Shankar -Original Message- Sent: Thursday, May 16, 2002 3:23 PM To: Multiple recipients of list ORACLE-L >Hi friends, >I have to load a input file into a oracle >table . The table has only >one field which will store each line as a single >row . The order of the file >is very important for my further process. Perhaps you should re-read a paper published in 1970 in 'Communications of the ACM' by an Edward Codd. IMHO you do not need a relational database at this stage of your process. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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 e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information.If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. Any unauthorised review, use, disclosure, dissemination, forwarding, printing or copying of this email or any action taken in reliance on this e-mail is strictly prohibited and may be unlawful. Visit us at http://www.cognizant.com
Re: SQL Loader
How about using external table? You define the table as external having one field. I think that select * from such table will bring you the lines in order and then you can do whatever you want with the data. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, May 16, 2002 1:33 PM > Client is not accepting to use the utl_file for reading data from the file. > i have to fetch each line from the file and process it for storing the > information in various tables. so apart from sql loader can u suggest other > method (excluding utl_file) for doing the same operation. > > Best Regards, > Shankar > > -Original Message- > Sent: Thursday, May 16, 2002 3:23 PM > To: Multiple recipients of list ORACLE-L > > > >Hi friends, > > I have to load a input file into a oracle > >table . The table has only > >one field which will store each line as a single > >row . The order of the file > >is very important for my further process. > > Perhaps you should re-read a paper published in 1970 in 'Communications of > the ACM' by an Edward Codd. IMHO you do not need a relational database at > this stage of your process. > > Regards, > > Stephane Faroult > Oriole > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Stephane Faroul > 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: Yechiel Adar 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: SQL*Loader
There's several examples in Chapter 11 of my SQL*Loader book. Here's a slightly simplified version of one of those: LOAD DATA INFILE 'waterfalls_photos.dat' REPLACE INTO TABLE WATERFALLS ( falls_photo_file FILLER CHAR TERMINATED BY ',' ENCLOSED BY '"', falls_photo LOBFILE(falls_photo_file) RAW TERMINATED BY EOF falls_name CHAR TERMINATED BY ',' ENCLOSED BY '"' ) Your input file should look something like: "chapel_falls.jpg","Chapel Falls" "scott_falls.jpg","Scott Falls" ... All the JPG files should, of course, exist also. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org Tuesday, July 17, 2001, 5:46:11 PM, you wrote: Jac> Does anyone have a good example of loading an image (i.e..jpg, .gif, etc) into a BLOB Jac> column using SQL*Loader. Jac> Looked through the docs and can't seem to find what I need. -- 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).
Re: Sql*Loader
What about something like this (in your control file) : DummyDate varchar2(01:09), UserName varchar2(10:17), BirthDatedate(18:21) "TO_DATE( :DummyDate || :BirthDate, '-MM-DD HH24:MI' )" I can't test it here at work, but it might prompt some ideas ... Regards Oweson Flynn -- Certified Oracle DBA The Flynn Consultancy Tel: 082-600-7-006 Fax: (011) 782-9313 EMail: [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, January 31, 2001 1:16 PM > Hi > > I've got the following entries in a sql*Loader import file > - > 2000-02-25JOHN 0608 > 2000-02-28PETE 0718 > - > 123456789012345678901 > > Char 1 to 9 and 18 to 21 make up the date and time, ie '2000-02-25 06:08' > ('-MM-DD HH24:MI') > > I need to import this information into a table with two columns, "USERNAME" > (1st) & "BIRTHDATE" (2nd), but I'm struggeling using Sql*Loader to > concatenate the two fieds. > > create table USER_BIRTHDATES > (USERNAME varchar2(20), > BDATE date); > > Any ideas. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Johan Locke@i-Commerce Services > 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 message may contain information which is confidential and subject to legal privilege. If you are not the intended recipient, you may not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify the sender immediately by email, facsimile or telephone and return and/or destroy the original message. *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Oweson Flynn 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: sql loader question
Since direct path loads do an append, I would say the answer shown is incorrect. <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 07/03/2003 02:40 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:sql loader question Hello list I have a doubt regarding the following question : ** SQL*Loader reads a set of records from a file, generates INSERT commands, and passes them to the Oracle kernel. Oracle then finds places for those records in free blocks in the table and updates any associated indexes. Which SQL*Loader mode is used in this scenario? A. direct-path load B. conventional path load Answer : Direct-path load , ( It is said "free blocks" which implies "Direct-Path Load", because "Conventional-Path Load" also use partially filled blocks. ) *** Shouldn't it be conventional path load, because insert commands are being used . Direct path load builds blocks of data in memory and saves these blocks directly into the extents allocated for the table being loaded -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: 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: sql loader question
Thursday, July 3, 2003, 5:40:37 AM, you wrote: bnini> SQL*Loader reads a set of records from a file, generates INSERT bnini> commands, This is the key right here. A conventional path load generates INSERT statements. A direct path load does not. A direct-path load passes preformatted blocks to the database. The term "direct path" comes from the fact that SQL*Loader (the client) formats a database block and sends it to the server to be written directly to the disk. No SQL processing is involved. bnini> Answer : Direct-path load , ( It is said "free blocks" which implies bnini> "Direct-Path Load", because "Conventional-Path Load" also use bnini> partially filled blocks. ) Was this from an Oracle exam? I hope not. It's ironic the person writing the test intended for you to notice the subtle mention of "free blocks" when they themselves didn't realize that direct path loads do not generate INSERT statements. bnini> Shouldn't it be conventional path load, because insert commands are bnini> being used . Yes. You are correct. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word "subscribe" in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: SQL Loader problem
Try with external tables. The best thing since sliced bread. On 2003.07.29 17:39, Anna Li wrote: Hi All, I'm trying to use SQL Loader to load data from a text file into a table. However, I always get error as column SKILL_DESCRIPTION field in data file exceeds maximum length where the column SKILL_DESCRIPTION is declared as varchar2(4000). I know the data is <4000 characters, but >1000 characters. It was inserted successfully when I execute insert statement in TOAD as: insert into skills_tmp values(..); Is there any option in SQL Loader I have to set in order to load a long string? Thanks for all the inputs in advance. Anna _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: SQL Loader problem
Sliced bread or not, the syntax problem will be the same since external tables are modeled on SQL*Loader syntax... :-) Anna, I suspect that there is confusion on the SQL*Loader concepts of "external datatypes" and "internal datatypes", which incidentally is shared by external tables currently. SQL*Loader's "internal datatypes" match the standard Oracle RDBMS datatypes, but the "external datatypes" are something else altgoether... I don't have docs available, but specifying an "external datatype" of "VARCHAR" or "VARCHAR2" probably doesn't mean what you want it to mean. If memory serves, I think it is supposed to be a two-byte binary integer (which is the data length) to be followed by the data itself. Or something like that... Try "CHAR(4000)", perhaps? The "Server Utilities" manual from the standard Oracle doc-set on "http://otn.oracle.com"; has all this stuff in it... Hope this helps... -Tim on 7/29/03 3:19 PM, Mladen Gogala at [EMAIL PROTECTED] wrote: > Try with external tables. The best thing since sliced bread. > On 2003.07.29 17:39, Anna Li wrote: >> Hi All, >> >> I'm trying to use SQL Loader to load data from a text file into a table. >> However, I always get error as column SKILL_DESCRIPTION field in data file >> exceeds maximum length where the column SKILL_DESCRIPTION is declared as >> varchar2(4000). I know the data is <4000 characters, but >1000 characters. >> It was inserted successfully when I execute insert statement in TOAD as: >> >> insert into skills_tmp values(..); >> >> Is there any option in SQL Loader I have to set in order to load a long >> string? Thanks for all the inputs in advance. >> >> Anna >> >> _ >> Protect your PC - get McAfee.com VirusScan Online >> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.net >> -- >> Author: Anna Li >> 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: Tim Gorman 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: SQL Loader problem
Tim, Sorry for late reply. I was out for a few days. You are right. CHAR(4000) fixed the problem. I really appreciate your help and all the help from other people who answered my question. Thanks. Anna From: Tim Gorman <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: SQL Loader problem Date: Tue, 29 Jul 2003 15:09:29 -0800 Sliced bread or not, the syntax problem will be the same since external tables are modeled on SQL*Loader syntax... :-) Anna, I suspect that there is confusion on the SQL*Loader concepts of "external datatypes" and "internal datatypes", which incidentally is shared by external tables currently. SQL*Loader's "internal datatypes" match the standard Oracle RDBMS datatypes, but the "external datatypes" are something else altgoether... I don't have docs available, but specifying an "external datatype" of "VARCHAR" or "VARCHAR2" probably doesn't mean what you want it to mean. If memory serves, I think it is supposed to be a two-byte binary integer (which is the data length) to be followed by the data itself. Or something like that... Try "CHAR(4000)", perhaps? The "Server Utilities" manual from the standard Oracle doc-set on "http://otn.oracle.com"; has all this stuff in it... Hope this helps... -Tim on 7/29/03 3:19 PM, Mladen Gogala at [EMAIL PROTECTED] wrote: > Try with external tables. The best thing since sliced bread. > On 2003.07.29 17:39, Anna Li wrote: >> Hi All, >> >> I'm trying to use SQL Loader to load data from a text file into a table. >> However, I always get error as column SKILL_DESCRIPTION field in data file >> exceeds maximum length where the column SKILL_DESCRIPTION is declared as >> varchar2(4000). I know the data is <4000 characters, but >1000 characters. >> It was inserted successfully when I execute insert statement in TOAD as: >> >> insert into skills_tmp values(..); >> >> Is there any option in SQL Loader I have to set in order to load a long >> string? Thanks for all the inputs in advance. >> >> Anna >> >> _ >> Protect your PC - get McAfee.com VirusScan Online >> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 >> >> -- >> Please see the official ORACLE-L FAQ: http://www.orafaq.net >> -- >> Author: Anna Li >> 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: Tim Gorman 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). _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Anna Li 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: SQL Loader help
Rachel, I do not claim to be a guru, but I do happen to have Jonathan's book here. The first thing is what field type are you using for this memo field? The book says you should use CHAR for VARCHAR2, CHAR, LONG and other related. What error are you getting? Can you post the lines from the ctl? Is there a chance there are TAB's in the data? Stephen >>> [EMAIL PROTECTED] 08/29/03 09:24AM >>> I know that there are several sql loader gurus on this list. I'm having a problem loading data from a third party vendor. into what is basically a LONG column The file is ASCII, tab delimited. The specs on the input data say that this particular column is a "memo" field (the originating database is FoxPro.. joy) I'm trying to load this into a 9.2.0.2 database. And it's barfing. This is annoying me no end and so I'm asking for help. I don't have Jonathan's book here with me! (that would, of course, be easy) Rachel __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Stephen Andert 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: SQL Loader help
Stephen, I was using CHAR for the LONG column the error is actually on the field in the file FOLLOWING the LONG column... I was getting a message that the data was too large. So I did the following and it seems to be working, although I can't get it to work via direct path this way: I changed the column in the Oracle table to CLOB I changed the definition of the column in the ctl file to char(1) The column separator was X'001' and the end of record marker was X'002'. I had been translating them both, the 001 to a tab and the 002 to a newline. I translate only the end of record marker now and define the column separator as X'1' in the ctl file. So far, it's working. Of course, it's slow as molasses, and this is a column which we DON'T NEED but then again... it's the Friday before a long weekend. I just want to get it to work, I'll make it pretty and fast later Rachel --- Stephen Andert <[EMAIL PROTECTED]> wrote: > Rachel, > > I do not claim to be a guru, but I do happen to have Jonathan's book > here. > > The first thing is what field type are you using for this memo field? > > The book says you should use CHAR for VARCHAR2, CHAR, LONG and other > related. > > What error are you getting? > > Can you post the lines from the ctl? > > Is there a chance there are TAB's in the data? > > Stephen > > > > >>> [EMAIL PROTECTED] 08/29/03 09:24AM >>> > I know that there are several sql loader gurus on this list. I'm > having > a problem loading data from a third party vendor. into what is > basically a LONG column > > The file is ASCII, tab delimited. The specs on the input data say > that > this particular column is a "memo" field (the originating database is > FoxPro.. joy) > > I'm trying to load this into a 9.2.0.2 database. And it's barfing. > This > is annoying me no end and so I'm asking for help. I don't have > Jonathan's book here with me! (that would, of course, be easy) > > Rachel > > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rachel Carmichael > 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: Stephen Andert > 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Sql*Loader problem...
JL, When you describe the field type in the SQLLoader file set the CHAR(4000) as the field description. You are correct in saying that the default is CHAR(255). As an example; col1 terminated by "," , col2 CHAR(4000) terminated by "," ... Ron >>> [EMAIL PROTECTED] 03/13/03 01:44PM >>> Hi... I would like to receive a bit of your help (I've been looking at Metalink with no luck). I'm having a sql*loader problem when I try to insert records in a table field. The problem is: my data file has a column with MORE THAN 255 characters long, and sql*loader rejects the records that exceed that limit! The data are generated by an app called NetCache. And, for auditing, we need to load the data into the oracle database. I'm having problems with just one column: URL_VISITED varchar2(4000) Any ideas, help would be appreciated!. I need to load a string like this: (A workaround could be SUBSTRac just the first 255 chars ) 'http://www.hotmail.msn.com/cgi-bin/sbox?did=1&t=4zO *OzYH5DI!K5JDg2lw3GV*Z17S74coVgj5bSbi3Idhx6CPvVsK3Eh bHes!GVxjYRKdxmfIAmoF1SvuqW0Vd5rQ$$&p=4wcx4xWmyGq6jd OZdt9566bmc4SziYsk6XKA60n*bkWwcK3UuKf8dzl1ykTjzYsAhK YN8wfQ6ay2rrR1FSRXj*djcyllOvUfV5H8drR6iESnR6PEig6lqH Cv7iH*opeUYbDd4IlZxxwRC6pqrKos5CLOW2iSBE5fp4L2g71b7E LgER0F!Eiw0XdUZsVKN!a17yI*bRtievO7lRyJefsNcjcA$$&js=yes' Regards! JL __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jose Luis Delgado 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: Ron Rogers 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: SQL*LOADER question
Should not be a problem: sqlldr control= data= userid=/@remote_machine where "remote_machine" is an entry in your local/client tnsnames file. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, March 19, 2003 8:23 AM > Hi All, > > > > > > I have two (2) Windows 2000 (w2k) machines, one running Oracle 8i at a > remote site (WAN connection) and another on my desk running the Oracle > client software(no database). Can I, and if so how, use sqlldr on my > desktop PC to load data into the remote database. The data file to be > loaded and the control file are on my desktop. I do not want to use > netmeeting or PC anywhere type programs which I know I can use. > Thanks > Rick > > > > > > > -- > 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: Igor Neyman 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: SQL*LOADER question
sqlldr username/[EMAIL PROTECTED] control=controlfilename I do it all the time sqlnet works for all Oracle utilities (sqlldr, exp, imp) --- [EMAIL PROTECTED] wrote: > Hi All, > > > > > > > > > > > I have two (2) Windows 2000 (w2k) machines, one running Oracle 8i > at a > remote site (WAN connection) and another on my desk running the > Oracle > client software(no database). Can I, and if so how, use sqlldr on > my > desktop PC to load data into the remote database. The data file to > be > loaded and the control file are on my desktop. I do not want to use > > netmeeting or PC anywhere type programs which I know I can use. > > Thanks > > Rick > > > > > > > > > > -- > 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). > __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: SQL*LOADER question
Sure, you can do it. Just use Oracle Net8 connect string to connect to the remote database when specifying userid for sqlldr. - Kirti -Original Message- Sent: Wednesday, March 19, 2003 7:24 AM To: Multiple recipients of list ORACLE-L Hi All, I have two (2) Windows 2000 (w2k) machines, one running Oracle 8i at a remote site (WAN connection) and another on my desk running the Oracle client software(no database). Can I, and if so how, use sqlldr on my desktop PC to load data into the remote database. The data file to be loaded and the control file are on my desktop. I do not want to use netmeeting or PC anywhere type programs which I know I can use. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: SQL*LOADER question
Rick, am i missing something in the picture here??, why not use sqlloader(you'll need it on your local PC) and send it across sql*net? joe > Hi All, > > > > > > I have two (2) Windows 2000 (w2k) machines, one running Oracle 8i at a > remote site (WAN connection) and another on my desk running the Oracle > client software(no database). Can I, and if so how, use sqlldr on my > desktop PC to load data into the remote database. The data file to be > loaded and the control file are on my desktop. I do not want to use > netmeeting or PC anywhere type programs which I know I can use. > Thanks > Rick > > > > > > > -- > 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). > > Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joseph S Testa 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: SQL*LOADER question
Rick You sure can -- as long as you can establish SQL*Net connection to the instance on the remote machine. All you have to do is specify "[EMAIL PROTECTED]" either on the command line or in your parameter file. If the OSs are exectly the same, you can even use direct path inserts Kevin -Original Message- [EMAIL PROTECTED] Sent: Wednesday, March 19, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Hi All, I have two (2) Windows 2000 (w2k) machines, one running Oracle 8i at a remote site (WAN connection) and another on my desk running the Oracle client software(no database). Can I, and if so how, use sqlldr on my desktop PC to load data into the remote database. The data file to be loaded and the control file are on my desktop. I do not want to use netmeeting or PC anywhere type programs which I know I can use. Thanks Rick -- 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: Kevin Toepke 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: SQL*LOADER question
Rick, Assuming sqlldr is installed on your client machine, you should be able to run it locally. HTH, Peter Schauss -Original Message- Sent: Wednesday, March 19, 2003 8:24 AM To: Multiple recipients of list ORACLE-L Hi All, I have two (2) Windows 2000 (w2k) machines, one running Oracle 8i at a remote site (WAN connection) and another on my desk running the Oracle client software(no database). Can I, and if so how, use sqlldr on my desktop PC to load data into the remote database. The data file to be loaded and the control file are on my desktop. I do not want to use netmeeting or PC anywhere type programs which I know I can use. Thanks Rick -- 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: Schauss, Peter 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: SQL*Loader question
I don't know if you can load the first 100 records, but you could load the last 100 by setting the "skip" value to the total - 100. HTH, Beth -Original Message- Sent: Tuesday, June 04, 2002 11:39 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun 2.6. I have tried reviewing the docs, but I didn't see anything that answered the question. Is it possible to limit the number of records being loaded? We have a file that has records in the 6 digit range. I'd like to test the controlfile, but I don't want to load the whole file. Is there a way to tell loader to only load, say the 1st 100 records? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Seefelt, Beth 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: SQL*Loader question
I don't know of any such option in sqlloader. But, you can do one thing copy the 100 records from the file and create a new file and try to load that one. Hope this helps. Regards, Inder -Original Message- Sent: Tuesday, June 04, 2002 11:39 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun 2.6. I have tried reviewing the docs, but I didn't see anything that answered the question. Is it possible to limit the number of records being loaded? We have a file that has records in the 6 digit range. I'd like to test the controlfile, but I don't want to load the whole file. Is there a way to tell loader to only load, say the 1st 100 records? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: SQL*Loader question
why not just create a smaller input file? head -100 ...filename... > testfilename and use the test file? --- "Ball, Terry" <[EMAIL PROTECTED]> wrote: > Oracle 8.1.6.3 on Sun 2.6. > > I have tried reviewing the docs, but I didn't see anything that > answered the > question. Is it possible to limit the number of records being > loaded? We > have a file that has records in the 6 digit range. I'd like to test > the > controlfile, but I don't want to load the whole file. Is there a way > to > tell loader to only load, say the 1st 100 records? > > TIA > > Terry Ball, DBA > Birch Telecom > Work: 816-300-1335 > FAX: 816-300-1800 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ball, Terry > 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: SQL*Loader question
Terry - Use Unix head command to create a small test file. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, June 04, 2002 10:39 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun 2.6. I have tried reviewing the docs, but I didn't see anything that answered the question. Is it possible to limit the number of records being loaded? We have a file that has records in the 6 digit range. I'd like to test the controlfile, but I don't want to load the whole file. Is there a way to tell loader to only load, say the 1st 100 records? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: SQL*Loader question
$ sqlldr help=y userid ORACLE username/password control Control file name log Log file name bad Bad file name dataData file name discard Discard file name discardmax Number of discards to allow skipNumber of logical records to skip loadNumber of logical records to load -Original Message- Sent: Tuesday, June 04, 2002 12:09 PM To: Multiple recipients of list ORACLE-L I don't know if you can load the first 100 records, but you could load the last 100 by setting the "skip" value to the total - 100. HTH, Beth -Original Message- Sent: Tuesday, June 04, 2002 11:39 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun 2.6. I have tried reviewing the docs, but I didn't see anything that answered the question. Is it possible to limit the number of records being loaded? We have a file that has records in the 6 digit range. I'd like to test the controlfile, but I don't want to load the whole file. Is there a way to tell loader to only load, say the 1st 100 records? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Seefelt, Beth 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: Toepke, Kevin M 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: SQL*Loader question
It is supported on the machines that have implemented RPM instruction in their CPUs. > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, June 04, 2002 12:08 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL*Loader question > > > > I don't know of any such option in sqlloader. But, you can do > one thing copy > the 100 records from the file and create a new file and try > to load that > one. Hope this helps. > > Regards, > Inder > > -Original Message- > Sent: Tuesday, June 04, 2002 11:39 AM > To: Multiple recipients of list ORACLE-L > > > Oracle 8.1.6.3 on Sun 2.6. > > I have tried reviewing the docs, but I didn't see anything > that answered the > question. Is it possible to limit the number of records > being loaded? We > have a file that has records in the 6 digit range. I'd like > to test the > controlfile, but I don't want to load the whole file. Is > there a way to > tell loader to only load, say the 1st 100 records? > > TIA > > Terry Ball, DBA > Birch Telecom > Work: 816-300-1335 > FAX: 816-300-1800 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ball, Terry > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen 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: SQL*Loader question
Get a copy of SQL*Loader the Definitive Guide by Gennick & Mishra / O'Reilly. You can use command-line parameters in the control file via a the OPTIONS command. For example: OPTIONS (parameter=value[,parameter=value...]) You can use: SKIP=logical_record_count LOAD=logical_record_count (the number of records to load) etc. You can also extract the number of rows you want to load and place the data in the control file. Use the keyword BEGINDATA data data data My $0.02 worth, Ken Janusz, CPIM - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, June 04, 2002 11:53 AM > $ sqlldr help=y > userid ORACLE username/password > control Control file name > log Log file name > bad Bad file name > dataData file name > discard Discard file name > discardmax Number of discards to allow > skipNumber of logical records to skip > loadNumber of logical records to load > > -Original Message- > Sent: Tuesday, June 04, 2002 12:09 PM > To: Multiple recipients of list ORACLE-L > > > > I don't know if you can load the first 100 records, but you could load > the last 100 by setting the "skip" value to the total - 100. > > HTH, > > Beth > > -Original Message- > Sent: Tuesday, June 04, 2002 11:39 AM > To: Multiple recipients of list ORACLE-L > > > Oracle 8.1.6.3 on Sun 2.6. > > I have tried reviewing the docs, but I didn't see anything that answered > the > question. Is it possible to limit the number of records being loaded? > We > have a file that has records in the 6 digit range. I'd like to test the > controlfile, but I don't want to load the whole file. Is there a way to > tell loader to only load, say the 1st 100 records? > > TIA > > Terry Ball, DBA > Birch Telecom > Work: 816-300-1335 > FAX: 816-300-1800 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ball, Terry > 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: Seefelt, Beth > 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: Toepke, Kevin M > 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: KENNETH 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: SQL*Loader question
Terry, Type SQLLDR at the command prompt, and all the help is displayed. The answer to your question is : sqlldr load=50 will load only 50 records. Another option is to copy your data file and delete everything except for the number of records to load for testing purposes. Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, June 04, 2002 11:39 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun 2.6. I have tried reviewing the docs, but I didn't see anything that answered the question. Is it possible to limit the number of records being loaded? We have a file that has records in the 6 digit range. I'd like to test the controlfile, but I don't want to load the whole file. Is there a way to tell loader to only load, say the 1st 100 records? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: SQL*Loader question
The parameters LOAD and SKIP determine how many records to load and skip. By default ALL records are loaded. HTH. Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia > * +61-412-232-056 > * use mobile please Please note 17 hour time difference between Melbourne and CA -Original Message- Sent: Tuesday, 4 June 2002 8:39 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun 2.6. I have tried reviewing the docs, but I didn't see anything that answered the question. Is it possible to limit the number of records being loaded? We have a file that has records in the 6 digit range. I'd like to test the controlfile, but I don't want to load the whole file. Is there a way to tell loader to only load, say the 1st 100 records? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Ferenc Mantfeld 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: SQL*Loader question
Hi, Why don't you just copy and paste those 100 records to other file and load from that new file. Sinardy -Original Message- Sent: 05 June 2002 12:28 To: Multiple recipients of list ORACLE-L The parameters LOAD and SKIP determine how many records to load and skip. By default ALL records are loaded. HTH. Regards: Ferenc Mantfeld Senior Performance Engineer Siebel Performance Engineering Melbourne, 3000, VIC, Australia > * +61-412-232-056 > * use mobile please Please note 17 hour time difference between Melbourne and CA -Original Message- Sent: Tuesday, 4 June 2002 8:39 AM To: Multiple recipients of list ORACLE-L Oracle 8.1.6.3 on Sun 2.6. I have tried reviewing the docs, but I didn't see anything that answered the question. Is it possible to limit the number of records being loaded? We have a file that has records in the 6 digit range. I'd like to test the controlfile, but I don't want to load the whole file. Is there a way to tell loader to only load, say the 1st 100 records? TIA Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry 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: Ferenc Mantfeld 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: Sinardy Xing 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: SQL*Loader question
By default all records are loaded is only true if there are no errors. The default ERRORS is 50 which means that the load is automatically aborted when 50 records are rejected due to errors. To assure that all records can be loaded you need to set ERRORS to a higher number than the total number of records in the load. My $0.02 worth, Ken Janusz, CPIM - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, June 04, 2002 11:28 PM > The parameters LOAD and SKIP determine how many records to load and skip. By > default ALL records are loaded. HTH. > > Regards: > Ferenc Mantfeld > Senior Performance Engineer > Siebel Performance Engineering > Melbourne, 3000, VIC, Australia > > * +61-412-232-056 > > * use mobile please > Please note 17 hour time difference between Melbourne and CA > > > -Original Message- > Sent: Tuesday, 4 June 2002 8:39 AM > To: Multiple recipients of list ORACLE-L > > > Oracle 8.1.6.3 on Sun 2.6. > > I have tried reviewing the docs, but I didn't see anything that answered the > question. Is it possible to limit the number of records being loaded? We > have a file that has records in the 6 digit range. I'd like to test the > controlfile, but I don't want to load the whole file. Is there a way to > tell loader to only load, say the 1st 100 records? > > TIA > > Terry Ball, DBA > Birch Telecom > Work: 816-300-1335 > FAX: 816-300-1800 > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Ball, Terry > 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: Ferenc Mantfeld > 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: KENNETH 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: Sql loader question
Roland It should be "I:\dvh\tuppy.txt" Amazing.. Mark -Original Message- [EMAIL PROTECTED] Sent: 11 June 2002 12:58 To: Multiple recipients of list ORACLE-L Hallo I am running this script but gets this errormessage: Why is it so? I get the errormessage The system cannot find the file specified.(I:dvh\tuppy.txt) but this file really exists: What is wrong. I include the textfile and also the ctl file. (See attached file: nielsen.ctl)(See attached file: Tuppy.txt) please check the files and see what is wrong. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith 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: Sql loader question
You don't have a \ after I: That may be a Problem. HTH Best Regards, Ganesh R Tel : +971 (4) 397 3337 Ext 420 Fax : +971 (4) 397 6262 HP : +971 (50) 745 6019 Live to learn... forget... and learn again. -Original Message- Sent: Tuesday, June 11, 2002 3:58 PM To: Multiple recipients of list ORACLE-L Hallo I am running this script but gets this errormessage: Why is it so? I get the errormessage The system cannot find the file specified.(I:dvh\tuppy.txt) but this file really exists: What is wrong. I include the textfile and also the ctl file. (See attached file: nielsen.ctl)(See attached file: Tuppy.txt) please check the files and see what is wrong. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ganesh Raja 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: Sql loader question
path problem. you set wrong. > The system cannot find the file specified.(I:dvh\tuppy.txt) try full path I:\dvh\tuppy.txt or I:/dvh/tuppy.txt -- Alexandre -- 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: Sql loader question
Did you, perchance, mean to specify "I:\dvh\tuppy.txt"? -Original Message- Sent: Tuesday, June 11, 2002 7:58 AM To: Multiple recipients of list ORACLE-L Hallo I am running this script but gets this errormessage: Why is it so? I get the errormessage The system cannot find the file specified.(I:dvh\tuppy.txt) but this file really exists: What is wrong. I include the textfile and also the ctl file. (See attached file: nielsen.ctl)(See attached file: Tuppy.txt) please check the files and see what is wrong. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M 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: SQL*LOADER ERROR
Hi, sounds stupid, but is your spelling correctly ? Try the pwd and SID uppercase. Nirmal Kumar Muthu Kumaran schrieb: > > Hi gurus, > > Greetings. > > c:\> sqlldr80 userid=msalah/msalah@qtel control=c:\test.ctl > > ORA-12203: TNS:unable to connect destination > SQL*Loader-704: Internal error: ulconnect : OCIServerAttach [-1] > > But am able to connect with SQL*Plus with the same username/password. > > Why it's showing this error, then?. > > Am using oracle 8.0.4, on Win NT. > > Rgds, > Nirmal. > -- > -- > 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). > > - > This Mail has been checked for Viruses > Attention: Encrypted mails can NOT be checked! > > ** > > Diese Mail wurde auf Viren geprueft > Hinweis: Verschluesselte mails koennen NICHT auf Viren geprueft werden! > - -- | Regards, | | Stefan Jahnke | | BOV AG | | @:D2 Vodafone, Abt.: FBOM | | Tel.: 0211/533-4893| -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stefan Jahnke 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: SQL*LOADER ERROR
Hello, Have you connected with "sqlplus username/password@qtel"? If you did sqlplus without the @qtel, you are not using the listener. In that case, you will probably want to get the listener running (or running correctly). HTH Stephen>>> [EMAIL PROTECTED] 09/04/01 01:56AM >>>Hi gurus,Greetings.c:\> sqlldr80 userid=msalah/msalah@qtel control=c:\test.ctlORA-12203: TNS:unable to connect destinationSQL*Loader-704: Internal error: ulconnect : OCIServerAttach [-1]But am able to connect with SQL*Plus with the same username/password.Why it's showing this error, then?.Am using oracle 8.0.4, on Win NT.Rgds,Nirmal.-- -- 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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: sql loader-dates
Try using the to_date command for the column data in question in your control file: ex: load data append into table xxx trailing nullcols ( column1 terminated by "," "to_date(:column1,'mm/dd/')", column2. ROR mª¿ªm >>> [EMAIL PROTECTED] 12/19/01 10:45AM >>> Hi , In my data file i have dates in the format e.g. 12/19/2001, but when loader tries to insert this into the table , the table is expecting date in the format 19-DEC-01 so it rejects the data. What can i do to get around this? regards -- 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: 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).
RE: sql loader-dates
Use DATE "mm/dd/", This should work. Also, look into getting the book "SQL*Loader The Definitive Guide" from O'Reilly. Hope this helps, Ken Janusz, CPIM Database Conversion Lead Sufficient System, Inc. Minneapolis, MN -Original Message- Sent: Wednesday, December 19, 2001 9:45 AM To: Multiple recipients of list ORACLE-L Subject:sql loader-dates Hi , In my data file i have dates in the format e.g. 12/19/2001, but when loader tries to insert this into the table , the table is expecting date in the format 19-DEC-01 so it rejects the data. What can i do to get around this? regards -- 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: 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: SQL Loader Question
My guess is : No. Can you run a quick test and let us know ?? Thanks. - Kirti -Original Message- Sent: Wednesday, January 09, 2002 3:45 PM To: Multiple recipients of list ORACLE-L 8.1.7 on W2000 I would like to know if there is a way to group more than one parfile into one file? I would like to run just one file verses a number of parfiles. Thanks, Ken Janusz, CPIM Database Conversion Lead Sufficient Systems, Inc. Minneapolis, MN -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deshpande, Kirti 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: SQL Loader Question
I tested under 8.1.6 on NT and the reply is NO. Checked also the new features for 9.0.1 and no mention of this. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: Deshpande, Kirti [SMTP:[EMAIL PROTECTED]] > Sent: Thu, January 10, 2002 4:30 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL Loader Question > > My guess is : No. > Can you run a quick test and let us know ?? > Thanks. > - Kirti > > -Original Message- > Sent: Wednesday, January 09, 2002 3:45 PM > To: Multiple recipients of list ORACLE-L > > > 8.1.7 on W2000 > I would like to know if there is a way to group more than one parfile into > one file? I would like to run just one file verses a number of parfiles. > > Thanks, > Ken Janusz, CPIM > Database Conversion Lead > Sufficient Systems, Inc. > Minneapolis, MN > > -- > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Deshpande, Kirti > 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 e-mail was scanned by the eSafe Mail Gateway > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= 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: SQL Loader Question
Thank you very much - Kirti -Original Message- Sent: Thursday, January 10, 2002 10:51 AM To: Multiple recipients of list ORACLE-L I tested under 8.1.6 on NT and the reply is NO. Checked also the new features for 9.0.1 and no mention of this. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: Deshpande, Kirti [SMTP:[EMAIL PROTECTED]] > Sent: Thu, January 10, 2002 4:30 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL Loader Question > > My guess is : No. > Can you run a quick test and let us know ?? > Thanks. > - Kirti > > -Original Message- > Sent: Wednesday, January 09, 2002 3:45 PM > To: Multiple recipients of list ORACLE-L > > > 8.1.7 on W2000 > I would like to know if there is a way to group more than one parfile into > one file? I would like to run just one file verses a number of parfiles. > > Thanks, > Ken Janusz, CPIM > Database Conversion Lead > Sufficient Systems, Inc. > Minneapolis, MN > > -- > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Deshpande, Kirti > 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 e-mail was scanned by the eSafe Mail Gateway > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= 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: Deshpande, Kirti 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: SQL Loader Question
Ken, You could create a batch file that references each par file and then run the batch file. ex: file :testexp.bat contains the following lines; exp login/passwd@server parfile=location and name of parfile1 exp login/passwd@server parfile=location and name of parfile2 etc. then just execute the testexp.bat file. Works good under Novell. ROR mª¿ªm>>> [EMAIL PROTECTED] 01/10/02 11:50AM >>>I tested under 8.1.6 on NT and the reply is NO.Checked also the new features for 9.0.1 and no mention of this.Yechiel Adar, Mehish Computer Services[EMAIL PROTECTED]> -Original Message-> From: Deshpande, Kirti [SMTP:[EMAIL PROTECTED]]> Sent: Thu, January 10, 2002 4:30 PM> To: Multiple recipients of list ORACLE-L> Subject: RE: SQL Loader Question> > My guess is : No. > Can you run a quick test and let us know ?? > Thanks.> - Kirti > > -Original Message-> Sent: Wednesday, January 09, 2002 3:45 PM> To: Multiple recipients of list ORACLE-L> > > 8.1.7 on W2000> I would like to know if there is a way to group more than one parfile into> one file? I would like to run just one file verses a number of parfiles.> > Thanks,> Ken Janusz, CPIM > Database Conversion Lead > Sufficient Systems, Inc. > Minneapolis, MN> > -- > 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).> -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com> -- > Author: Deshpande, Kirti> 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 e-mail was scanned by the eSafe Mail Gateway > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: SQL Loader Question
Ron: That's great, I'll give it a try. Thanks, Ken -Original Message- From: Ron Rogers [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 10, 2002 12:23 PM To: Multiple recipients of list ORACLE-L Subject: RE: SQL Loader Question Ken, You could create a batch file that references each par file and then run the batch file. ex: file :testexp.bat contains the following lines; exp login/passwd@server parfile=location and name of parfile1 exp login/passwd@server parfile=location and name of parfile2 etc. then just execute the testexp.bat file. Works good under Novell. ROR mª¿ªm >>> [EMAIL PROTECTED] 01/10/02 11:50AM >>> I tested under 8.1.6 on NT and the reply is NO. Checked also the new features for 9.0.1 and no mention of this. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: Deshpande, Kirti [SMTP:[EMAIL PROTECTED]] > Sent: Thu, January 10, 2002 4:30 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: SQL Loader Question > > My guess is : No. > Can you run a quick test and let us know ?? > Thanks. > - Kirti > > -Original Message- > Sent: Wednesday, January 09, 2002 3:45 PM > To: Multiple recipients of list ORACLE-L > > > 8.1.7 on W2000 > I would like to know if there is a way to group more than one parfile into > one file? I would like to run just one file verses a number of parfiles. > > Thanks, > Ken Janusz, CPIM > Database Conversion Lead > Sufficient Systems, Inc. > Minneapolis, MN > > -- > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Deshpande, Kirti > 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 e-mail was scanned by the eSafe Mail Gateway > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= 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: SQL*Loader Problem
Where's what note 113211.1 on metalink says about this. I hope it helps. Problem Description --- Using SQL*Loader to load data into a database table and rows fail to load with the message: Record X: Rejected - Error on table ""."", column "XX". Field in data file exceeds maximum length The data to be loaded will be longer than 255 characters and the database column is set to larger than 255 characters. Solution Description In the control file specify the length of the column if the columns in the database are longer than 255 characters. eg LOAD DATA INFILE * INTO TABLE "TEST"."TEST1" APPEND FIELDS TERMINATED BY ',' ("COL1", "COL2" char(4000), "COL3") BEGINDATA 333, data data data longer than 255 characters data data etc,this will work Explanation --- As a default, if the column length is not specified, it will default to a maximum of 255 characters. If the database column is longer than 255 characters and the data to be loaded exceeds 255 characters, then it exceeds the maximum length. Specifying the maximum length in the control file overrides the default setting. References -- Oracle8 Server Utilities Additional Search Words --- sqlloader loader . Bill Gentry DBA Allina Health System Minneapolis, MN 55403 612-775-1190 [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, January 11, 2002 9:55 AM > 8.1.7 on W2000 > > I am loading data and keep getting an error. The fields are text about 400+ > characters. The column I am loading into is varchar2(4000). I keep getting > rejects with this error: Field in data file exceeds maximum length. Any > ideas as to why this is happening? > > Thanks, > > Ken Janusz, CPIM > Database Conversion Lead > Sufficient Systems, Inc. > Minneapolis, MN > > > > -- > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Gentry 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: SQL*Loader Problem
Bill: I also found that I have to specify the size of the column even if the data field is being skipped using filler! Ken -Original Message- Sent: Friday, January 11, 2002 10:55 AM To: Multiple recipients of list ORACLE-L Subject:Re: SQL*Loader Problem Where's what note 113211.1 on metalink says about this. I hope it helps. Problem Description --- Using SQL*Loader to load data into a database table and rows fail to load with the message: Record X: Rejected - Error on table ""."", column "XX". Field in data file exceeds maximum length The data to be loaded will be longer than 255 characters and the database column is set to larger than 255 characters. Solution Description In the control file specify the length of the column if the columns in the database are longer than 255 characters. eg LOAD DATA INFILE * INTO TABLE "TEST"."TEST1" APPEND FIELDS TERMINATED BY ',' ("COL1", "COL2" char(4000), "COL3") BEGINDATA 333, data data data longer than 255 characters data data etc,this will work Explanation --- As a default, if the column length is not specified, it will default to a maximum of 255 characters. If the database column is longer than 255 characters and the data to be loaded exceeds 255 characters, then it exceeds the maximum length. Specifying the maximum length in the control file overrides the default setting. References -- Oracle8 Server Utilities Additional Search Words --- sqlloader loader . Bill Gentry DBA Allina Health System Minneapolis, MN 55403 612-775-1190 [EMAIL PROTECTED] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, January 11, 2002 9:55 AM > 8.1.7 on W2000 > > I am loading data and keep getting an error. The fields are text about 400+ > characters. The column I am loading into is varchar2(4000). I keep getting > rejects with this error: Field in data file exceeds maximum length. Any > ideas as to why this is happening? > > Thanks, > > Ken Janusz, CPIM > Database Conversion Lead > Sufficient Systems, Inc. > Minneapolis, MN > > > > -- > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Gentry 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: SQL*LOADER Questions
Ron, If it were me, I would use Sql*Loader to load the data into a "loading" db tables, and then use a PL/SQL script to read that table and process the records into wherever it is needed. You can certainly guarantee that the data will be read in the correct order (by the PL/SQL script) by creating additional columns in this loading table, and load the data/time, or a sequence number. this seems to be the easiest way to do this. hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Monday, July 09, 2001 11:30 AM To: Multiple recipients of list ORACLE-L We are trying to replace a VB program with SQL*Loader. Two Questions. The input file contains record types. A type 1 header record contains data that must be retained and combined with a type 3 record. Also, depending on the record type the data is loaded into different tables. Is all this possible in SQL*Loader? Thanks! Ron -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. 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: SQL*LOADER Questions
Monday, July 09, 2001, 11:30:20 AM, Ron Smith wrote: SRL> We are trying to replace a VB program with SQL*Loader. Two Questions. The SRL> input file contains record types. A type 1 header record contains data that SRL> must be retained and combined with a type 3 record. Also, depending on the SRL> record type the data is loaded into different tables. Is all this possible SRL> in SQL*Loader? SQL*Loader can recognize record types, and can direct data from the different types of records to different tables. Retaining data from a type 1 record, and combining it with a type 3 record, especially multiple type 3 records, is beyond what SQL*Loader can do. At least, I can't conceive of a way to do it as I think about it right now. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org -- 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).
RE: SQL Loader error
Could you post the control file? Kev. -Original Message- Sent: 30 January 2002 09:15 To: Multiple recipients of list ORACLE-L i have these 2 errors in the log file 1.Record 2389: Rejected - Error on table IA_TEST, column COMMENTS. Field in data file exceeds maximum length 2. Record 885: Rejected - Error on table IA_TEST. ORA-01401: inserted value too large for column whats the differennce between the 2? cheers -- 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: Thomas, Kevin 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: SQL Loader error
heres the ctl LOAD DATA INFILE 'sierras.dat' INTO TABLE ia_test trailing nullcols ( SWREGNOterminated by ',' OPTIONALLY ENCLOSED BY '"', commentsterminated by ',' OPTIONALLY ENCLOSED BY '"') dat file looks something like: "aer100","customer's wish to cancel" "aer200","etc." "Thomas, Kevin" <[EMAIL PROTECTED]>@fatcity.com on 30/01/2002 11:25:34 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Could you post the control file? Kev. -Original Message- Sent: 30 January 2002 09:15 To: Multiple recipients of list ORACLE-L i have these 2 errors in the log file 1.Record 2389: Rejected - Error on table IA_TEST, column COMMENTS. Field in data file exceeds maximum length 2. Record 885: Rejected - Error on table IA_TEST. ORA-01401: inserted value too large for column whats the differennce between the 2? cheers -- 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: Thomas, Kevin 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: SQL Loader error
If I remember right, SQL*LOADER has (or used to have) a maximum field size of 256 characters. This can be overcome by specifying the size you want to load like: LOAD DATA INFILE 'sierras.dat' INTO TABLE ia_test trailing nullcols ( SWREGNOterminated by ',' OPTIONALLY ENCLOSED BY '"', comments CHAR(xxx) terminated by ',' OPTIONALLY ENCLOSED BY '"' ) Where xxx is the size of the field in the table you are trying to load into. Regards, Kev. -Original Message- Sent: 30 January 2002 12:11 To: Multiple recipients of list ORACLE-L heres the ctl LOAD DATA INFILE 'sierras.dat' INTO TABLE ia_test trailing nullcols ( SWREGNOterminated by ',' OPTIONALLY ENCLOSED BY '"', commentsterminated by ',' OPTIONALLY ENCLOSED BY '"') dat file looks something like: "aer100","customer's wish to cancel" "aer200","etc." "Thomas, Kevin" <[EMAIL PROTECTED]>@fatcity.com on 30/01/2002 11:25:34 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Could you post the control file? Kev. -Original Message- Sent: 30 January 2002 09:15 To: Multiple recipients of list ORACLE-L i have these 2 errors in the log file 1.Record 2389: Rejected - Error on table IA_TEST, column COMMENTS. Field in data file exceeds maximum length 2. Record 885: Rejected - Error on table IA_TEST. ORA-01401: inserted value too large for column whats the differennce between the 2? cheers -- 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: Thomas, Kevin 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin 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: SQL Loader error
The first is a SQL loader error: SQL*Loader-00621 Field in data file exceeds maximum length Cause: A field exceeded its maximum allowable length. The maximum length is either the length specified in the SQL*Loader control file, or, for delimitable fields without a length specified, the maximum length of the corresponding database column (for char, varchar columns). The second error is from the database like putting 999 into a two digits field: ORA-01401 inserted value too large for column Cause: The value entered is larger than the maximum width defined for the column. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Sent: Wed, January 30, 2002 11:15 AM > To: Multiple recipients of list ORACLE-L > Subject: SQL Loader error > > i have these 2 errors in the log file > > > 1.Record 2389: Rejected - Error on table IA_TEST, column COMMENTS. > Field in data file exceeds maximum length > > 2. Record 885: Rejected - Error on table IA_TEST. > ORA-01401: inserted value too large for column > > whats the differennce between the 2? > > cheers > > > -- > 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 e-mail was scanned by the eSafe Mail Gateway > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= 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: SQL Loader error
Iashraf, Not knowing what your table desc looks like for the columns in question and what the actual data, (record 2389 and 885) looks like I can take a wild guess at the explaination. Record 2389 the comments column " Delimited data was specified with a maximun length and the data value exceeded that length" . You could be missing the field delimiter.. Record 885, you are trying to insert 6 char into a 5 char field . The data rows that are bad should be in the "bad" file location. Review the data for the errors. ROR mª¿ªm >>> [EMAIL PROTECTED] 01/30/02 07:11AM >>> heres the ctl LOAD DATA INFILE 'sierras.dat' INTO TABLE ia_test trailing nullcols ( SWREGNOterminated by ',' OPTIONALLY ENCLOSED BY '"', commentsterminated by ',' OPTIONALLY ENCLOSED BY '"') dat fReceived: from CONNECT-MTA by galotterile looks something like: "aer100","customer's wish to cancel" "aer200","etc." "Thomas, Kevin" <[EMAIL PROTECTED]>@fatcity.com on 30/01/2002 11:25:34 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Could you post the control file? Kev. -Original Message- Sent: 30 January 2002 09:15 To: Multiple recipients of list ORACLE-L i have these 2 errors in the log file 1.Record 2389: Rejected - Error on table IA_TEST, column COMMENTS. Field in data file exceeds maximum length 2. Record 885: Rejected - Error on table IA_TEST. ORA-01401: inserted value too large for column whats the differennce between the 2? cheers -- 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: Thomas, Kevin 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). -- 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).
RE: Sql loader - schedule
Not Unix - I will presume NT. On NT you can use the AT command to schedule task. Just type AT at the command prompt. One more thing: activate the scheduling service. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -Original Message- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Sent: Mon, March 18, 2002 3:48 PM > To: Multiple recipients of list ORACLE-L > Subject: Sql loader - schedule > > Hallo, > > How is it possible to schedule an sql loader script, which is running on > network(not unix). Let us say this should run at 6 pm every day. > This sqlloader simply loads a textfile into a table in the database. > > Thanks in advance > > Roland > > -- > 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: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= 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: Sql loader - schedule
what does running on a network(not unix) mean? any way you can use a scheduler. There are alot of free ones avaiable. On Mon, 18 Mar 2002 [EMAIL PROTECTED] wrote: > Hallo, > > How is it possible to schedule an sql loader script, which is running on network(not >unix). Let us say this should run at 6 pm every day. > This sqlloader simply loads a textfile into a table in the database. > > Thanks in advance > > Roland > > -- > 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: Alex 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: Sql loader - schedule
Hi Roland, You could do this via the OEM job system. Or whatever batch scheduling system exists in your environment John [EMAIL PROTECTED] wrote: >Hallo, > >How is it possible to schedule an sql loader script, which is running on network(not >unix). Let us say this should run at 6 pm every day. >This sqlloader simply loads a textfile into a table in the database. > >Thanks in advance > >Roland > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ora NT DBA 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: SQL*Loader Book
Looks good to me to. I will find out more when I start using it. Thanks for the tip. Ken -Original Message- Sent: Wednesday, September 26, 2001 1:15 PM To: Multiple recipients of list ORACLE-L You can find the information here: http://www.oreilly.com/catalog/orsqlloader/errata/ I have the same book, and think that it is a great one. Brian Ken Janusz wrote: > I will be using the book "Oracle SQL*Loader The Definitive Guide" to load > flat files into an Oracle 8.1.5 DB. > > Would like to know if there are any errors / gotchas or missing information > in this book that I need to be aware of? > > Thanks, > Ken Janusz, CPIM > Database Conversion Lead > Sufficient Systems, Inc. > Minneapolis, MN > -- > 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). -- -- | Brian McGraw -- Oracle DBA | | Central Alabama Oracle Users Group | || | mailto:[EMAIL PROTECTED] | | http://bmcgraw.home.mindspring.com | -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian McGraw 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: SQL*Loader Book
You can find the information here: http://www.oreilly.com/catalog/orsqlloader/errata/ I have the same book, and think that it is a great one. Brian Ken Janusz wrote: > I will be using the book "Oracle SQL*Loader The Definitive Guide" to load > flat files into an Oracle 8.1.5 DB. > > Would like to know if there are any errors / gotchas or missing information > in this book that I need to be aware of? > > Thanks, > Ken Janusz, CPIM > Database Conversion Lead > Sufficient Systems, Inc. > Minneapolis, MN > -- > 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). -- -- | Brian McGraw -- Oracle DBA | | Central Alabama Oracle Users Group | || | mailto:[EMAIL PROTECTED] | | http://bmcgraw.home.mindspring.com | -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian McGraw 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: SQL Loader questions
Lisa, No and no. Loader loads rows into tables. If you ask nicely, it will clean out the table completely before it does that. That's all it does. And it does it pretty well. It does not let users redefine tables. And it does not do selective update and delete based on the incoming data file. Other loading tools do that (I know DataJunction does, and others) but not Loader. Yosi YTTRI Lisa wrote: > Hi everyone - > > I need some help. We have an application running on 8.0.5 on NT. My > programmer tells me that she should be able to add columns to a table simply > by changing the sql loader control file definition of the input. I have > looked through the documentation and tried several tests, but I can't see > any way that this would work. > > Is this actually possible with SQL Loader? > > Also, she tells me that if a record exists in the table and she has the same > record (key value only) in the input file, that SQL Loader should update the > record with any changed field values. Is there a special keyword to do this > - I can't seem to find anything on that either? > > Thanks in advance for any help you can give me. > > Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yosi Greenfield 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: SQL Loader questions
You might ask your programmer to read the manual. However, the first might be a nice enhancement but it's not currently available. As for the second, SQL*Loader either inserts into an empty table or appends to a table with existing data. It doesn't do an implicit update. However, if your table has a column with a timestamp showing when each row was inserted then you could disable the unique constraint, append the new data via SQL*Loader, find the rows with duplicate key values, delete the older rows and re-enable the constraint. YTTRI Lisa <[EMAIL PROTECTED]> Sent by: rootcc: Subject: SQL Loader questions 10/24/2001 12:05 PM Please respond to ORACLE-L Hi everyone - I need some help. We have an application running on 8.0.5 on NT. My programmer tells me that she should be able to add columns to a table simply by changing the sql loader control file definition of the input. I have looked through the documentation and tried several tests, but I can't see any way that this would work. Is this actually possible with SQL Loader? Also, she tells me that if a record exists in the table and she has the same record (key value only) in the input file, that SQL Loader should update the record with any changed field values. Is there a special keyword to do this - I can't seem to find anything on that either? Thanks in advance for any help you can give me. Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: YTTRI Lisa 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: SQL Loader questions
what documentation has your programmer been reading? as far as I know you can't add a column on the fly through a sqlloader control file, nor will oracle do an update to existing data. I'm not an expert, but I've never been able to do that --- YTTRI Lisa <[EMAIL PROTECTED]> wrote: > Hi everyone - > > I need some help. We have an application running on 8.0.5 on NT. My > programmer tells me that she should be able to add columns to a table > simply > by changing the sql loader control file definition of the input. I > have > looked through the documentation and tried several tests, but I can't > see > any way that this would work. > > Is this actually possible with SQL Loader? > > Also, she tells me that if a record exists in the table and she has > the same > record (key value only) in the input file, that SQL Loader should > update the > record with any changed field values. Is there a special keyword to > do this > - I can't seem to find anything on that either? > > Thanks in advance for any help you can give me. > > Lisa > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: YTTRI Lisa > 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). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: SQL Loader questions
That's it folks. We can all go home. Rachel says "I'm not an expert". Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, October 24, 2001 2:00 PM To: Multiple recipients of list ORACLE-L what documentation has your programmer been reading? as far as I know you can't add a column on the fly through a sqlloader control file, nor will oracle do an update to existing data. I'm not an expert, but I've never been able to do that --- YTTRI Lisa <[EMAIL PROTECTED]> wrote: > Hi everyone - > > I need some help. We have an application running on 8.0.5 on NT. My > programmer tells me that she should be able to add columns to a table > simply > by changing the sql loader control file definition of the input. I > have > looked through the documentation and tried several tests, but I can't > see > any way that this would work. > > Is this actually possible with SQL Loader? > > Also, she tells me that if a record exists in the table and she has > the same > record (key value only) in the input file, that SQL Loader should > update the > record with any changed field values. Is there a special keyword to > do this > - I can't seem to find anything on that either? > > Thanks in advance for any help you can give me. > > Lisa > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: YTTRI Lisa > 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). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: SQL Loader questions
Thanks to all for reconfirming this for me. I had never heard of SQL Loader doing these things, but couldn't quite convince the programmer. What I have since found out is that once upon a time, a consultant came in and set up the job that loads their data - and left without documenting it. Lo and behold, there's a lot of other stuff the script is doing besides running SQL Loader. Thanks again for all your responses. Lisa :D -Original Message- Sent: Wednesday, October 24, 2001 1:00 PM To: Multiple recipients of list ORACLE-L what documentation has your programmer been reading? as far as I know you can't add a column on the fly through a sqlloader control file, nor will oracle do an update to existing data. I'm not an expert, but I've never been able to do that --- YTTRI Lisa <[EMAIL PROTECTED]> wrote: > Hi everyone - > > I need some help. We have an application running on 8.0.5 on NT. My > programmer tells me that she should be able to add columns to a table > simply > by changing the sql loader control file definition of the input. I > have > looked through the documentation and tried several tests, but I can't > see > any way that this would work. > > Is this actually possible with SQL Loader? > > Also, she tells me that if a record exists in the table and she has > the same > record (key value only) in the input file, that SQL Loader should > update the > record with any changed field values. Is there a special keyword to > do this > - I can't seem to find anything on that either? > > Thanks in advance for any help you can give me. > > Lisa > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: YTTRI Lisa > 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). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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: YTTRI Lisa 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: SQL loader query
you can use the skip option of the sql loader. in ur case it should be skip=1(if headings are also separated by commas) can have the full help by typing sqlldr help=y at the command prompt. - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, May 17, 2001 1:05 PM > Friends, > > I have the classic case of a comma separated text file which I want to upload > and I need to know how can I enforce the following behavior of the SQL Loader: > > 1. To ignore the first row of the data file (which contains the column titles) > > 2. Avoid uploading of a column from the middle of the data file > > Thanks in advance, > Pierre > > -- > 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: Saurabh Sharma 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: SQL loader query
Could you not simply open the .csv file and manually delete the first row? As for your second question, I believe you can just not mention the column you want to leave out in the control file. HTH Mark -Original Message- [EMAIL PROTECTED] Sent: Thursday, May 17, 2001 08:35 To: Multiple recipients of list ORACLE-L Friends, I have the classic case of a comma separated text file which I want to upload and I need to know how can I enforce the following behavior of the SQL Loader: 1. To ignore the first row of the data file (which contains the column titles) 2. Avoid uploading of a column from the middle of the data file Thanks in advance, Pierre -- 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: Mark Leith 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: SQL loader query
In a recent mail on a similar topic someone said that in 8i there is a FILLER field in SQL*Loader that allows you to jump over a column. Prior to 8i you are stuck. I have been recommended a brand new book on SQL*Loader. Not yet bought or read it. Jonathan Gennick & Sanjay Mishra O'Reilly & Associates It is available in the States but not yet in the UK. e.g. Amazon.com Simon Fox Room 205 Rail House Gresty Road CREWE Cheshire CW2 6EA England 01270 533997 -Original Message- Sent: 17 May 2001 09:35 To: Multiple recipients of list ORACLE-L you can use the skip option of the sql loader. in ur case it should be skip=1(if headings are also separated by commas) can have the full help by typing sqlldr help=y at the command prompt. - Original Message - To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent: Thursday, May 17, 2001 1:05 PM > Friends, > > I have the classic case of a comma separated text file which I want to upload > and I need to know how can I enforce the following behavior of the SQL Loader: > > 1. To ignore the first row of the data file (which contains the column titles) > > 2. Avoid uploading of a column from the middle of the data file > > Thanks in advance, > Pierre > > -- > 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: Saurabh Sharma 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 email is confidential and intended solely for the use of the individual to whom it is addressed. Any views or opinions presented are solely those of the author and do not necessarily represent those of Sema. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. If you have received this email in error please notify the Sema UK Helpdesk by telephone on +44 (0) 121 627 5600. ___ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: FOX, Simon 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: SQL*LOADER problem
Satish, You can do a couple of things...in the query you could to a replace(column1, chr(10), '~') (or some other unlikely character or string), then do a replace again in the SQL*Loader script to get the carriage returns back in there. Or you could use the query to put a character in the front of each true record, something like select '#' || col1, col2, col3, and then use the SQL*Loader commands for concatenating records. Can't remember offhand how that one works, though... HTH, Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] "Satish Iyer" <[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> tle.wa.us> cc: Sent by:Fax to: [EMAIL PROTECTED]Subject: SQL*LOADER problem 05/30/2001 08:05 PM Please respond to ORACLE-L Hi everyone. Having this typical problem with sql*loader. I am extracting data out from a table and this table has a field in which users have put in a new line feed character. Now when I extract the data out using SQL*PLUS it obviously has a problem eg. 1 First line of stt 2333232 Second line 2Full line 2323232 3 Again a partial 2323232 line. Now if such a table was extracted to a txt file, the sqlldr runs into a problem of getting the second half of the line as expected. Any ideas how this could be avoided ? Hope I explained that okay. Satish Iyer DBA CCSS Team 684-3016 -- 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: SQL*LOADER problem
Thanks Diana for letting me know about the replace function. A little more research and my problem was solved. Satish IyerDBACCSS Team 684-3016>>> [EMAIL PROTECTED] 05/30/01 07:50PM >>>Satish,You can do a couple of things...in the query you could to areplace(column1, chr(10), '~') (or some other unlikely character orstring), then do a replace again in the SQL*Loader script to get thecarriage returns back in there. Or you could use the query to put acharacter in the front of each true record, something likeselect '#' || col1, col2, col3, and then use the SQL*Loader commands for concatenating records. Can'tremember offhand how that one works, though...HTH,Diana DuncanTITAN Technology PartnersOne Copley Parkway, Ste 540Morrisville, NC 27560VM: 919.466.7337 x 316F: 919.466.7427E: [EMAIL PROTECTED] "Satish Iyer" <[EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> tle.wa.us> cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: SQL*LOADER problem 05/30/2001 08:05 PM Please respond to ORACLE-L Hi everyone.Having this typical problem with sql*loader. I am extracting data out froma table and this table has a field in which users have put in a new linefeed character. Now when I extract the data out using SQL*PLUS itobviously has a problemeg.1 First line of stt 2333232 Second line2 Full line 23232323 Again a partial 2323232 line.Now if such a table was extracted to a txt file, the sqlldr runs into aproblem of getting the second half of the line as expected. Any ideas howthis could be avoided ?Hope I explained that okay.Satish IyerDBACCSS Team 684-3016-- 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-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: Sql Loader documentation
Eric, O'Reilly has a good book out soon. http://www.oreilly.com/catalog/orsqlloader/index.html I reviewed the book and found a lot of great information. I really liked the chapter on tuning. I put a couple of the things I found there into place on some regular jobs that I have and saw some incredible performance gains. Stephen >>> [EMAIL PROTECTED] 03/22 1:10 PM >>> Any one know where to find good documentation on sqlloader? Thanks, Eric -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chesebro, Eric 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: Stephen Andert 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: SQL*Loader help
Title: RE: SQL*Loader help There is something like DEFAULTIF NULL. Happy RTFM'ing! :)
Re: SQL*Loader help
Just insert the string you want in the position you desire. i.e.. (01110,"*",,,1,1,31460) you would also have to specify the delimiters in the control file. regards Tavo [EMAIL PROTECTED] wrote: > Maybe some could help me. > I'm having trouble trying to load a csv file with sqlldr. The destination table > has char columns defined as not null and the csv file has missing data for these > columns. (These columns will contain blanks in the database). > > Sqlldr wants to set the missing fields to null, which results in record > rejected errors. Is there a way around this. I would be happy if I could insert > blanks or insert any other marker-value into the field like '***'.. > The input file lines looks something like (011101,1,31460) > Thanks > Chaim > > -- > 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: Luis DeUrioste 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: SQL*Loader help
Chaim, Check out the sqlldr case studies on OTN. Ross has the right idea with the NULLIF. Here's a general description from OTN: Loading All-Blank Fields Totally blank fields for numeric or DATE fields cause the record to be rejected. To load one of these fields as null, use the NULLIF clause with the BLANKS keyword, as described in Comparing Fields to BLANKS. Case 6: Loading Using the Direct Path Load Method provides examples of how to load all-blank fields as null with the NULLIF clause. If an all-blank CHAR field is surrounded by enclosure delimiters, then the blanks within the enclosures are loaded. Otherwise, the field is loaded as null. More details on whitespace are presented in Trimming Blanks and Tabs and in Preserving Whitespace. Hope this helps. David A. Barbour Oracle DBA, OCP [EMAIL PROTECTED] wrote: > > Maybe some could help me. > I'm having trouble trying to load a csv file with sqlldr. The destination table > has char columns defined as not null and the csv file has missing data for these > columns. (These columns will contain blanks in the database). > > Sqlldr wants to set the missing fields to null, which results in record > rejected errors. Is there a way around this. I would be happy if I could insert > blanks or insert any other marker-value into the field like '***'.. > The input file lines looks something like (011101,1,31460) > Thanks > Chaim > > -- > 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: David A. Barbour 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: SQL*Loader help
You might want to look into the 'defaultif' option in the control file. Prakash -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, March 30, 2001 12:27 PM To: Multiple recipients of list ORACLE-L Maybe some could help me. I'm having trouble trying to load a csv file with sqlldr. The destination table has char columns defined as not null and the csv file has missing data for these columns. (These columns will contain blanks in the database). Sqlldr wants to set the missing fields to null, which results in record rejected errors. Is there a way around this. I would be happy if I could insert blanks or insert any other marker-value into the field like '***'.. The input file lines looks something like (011101,1,31460) Thanks Chaim -- 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: Bala, Prakash 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: SQL*Loader help
Chaim, If on the other hand the BLANK will not work properly on a numeric filed that is to be used in calculations. In that case you might want to have a 0(zero) loaded rather than a null,blank. Then you could use the NVL clause in your controll statement where the field type is numeric. ROR mª¿ªm >>> [EMAIL PROTECTED] 03/30/01 02:00PM >>> Chaim, Check out the sqlldr case studies on OTN. Ross has the right idea with the NULLIF. Here's a general description from OTN: Loading All-Blank Fields Totally blank fields for numeric or DATE fields cause the record to be rejected. To load one of these fields as null, use the NULLIF clause with the BLANKS keyword, as described in Comparing Fields to BLANKS. Case 6: Loading Using the Direct Path Load Method provides examples of how to load all-blank fields as null with the NULLIF clause. If an all-blank CHAR field is surrounded by enclosure delimiters, then the blanks within the enclosures are loaded. Otherwise, the field is loaded as null. More details on whitespace are presented in Trimming Blanks and Tabs and in Preserving Whitespace. Hope this helps. David A. Barbour Oracle DBA, OCP [EMAIL PROTECTED] wrote: > > Maybe some could help me. > I'm having trouble trying to load a csv file with sqlldr. The destination table > has char columns defined as not null and the csv file has missing data for these > columns. (These columns will contain blanks in the database). > > Sqlldr wants to set the missing fields to null, which results in record > rejected errors. Is there a way around this. I would be happy if I could insert > blanks or insert any other marker-value into the field like '***'.. > The input file lines looks something like (011101,1,31460) > Thanks > Chaim > > -- > 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: David A. Barbour 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: 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).
Re: sql*loader datatype question
Lisa, in the modeling world, unless you are performing a calculation(like on phone number), i've always been told to make them character. joe >>> [EMAIL PROTECTED] 08/30/01 01:33PM >>> I'm hitting a wall here. I'm trying to load data into a large table. Two of the fields are phone numbers (10 digits). The database field is defined as number(10). When I try to load these records, they err out with an error similar to below. Record 858: Rejected - Error on table INV_OWNER.FPSITE, column FPS_AFFIL_LTD_PTS_FPS. ORA-01722: invalid number So I change the fields to character, get the data in and it truly is all 10-digit numbers. I'm wondering if I'm missing something in the format. The doco says that INTEGER EXTERNAL is to be used for "human readable" numbers, non-binary, when you want to specify the length. The length I've chosen is correct. I can even create a table with number(10) columns and insert the varchar(10) phone number data into it! What am I missing? I really do not want to change all my number fields to character. It would defeat the purpose. I have a feeling I'm missing some sort of conversion in SQL*Loader but I don't see it off the top of my head. Thanks in advance for any suggestions. Lisa Koivu wanna-be DBA Hand Holder (that's what I've been doing this morning with an ex-coworker) and DBA Ft. Lauderdale, FL, USA The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments.
Re: sql*loader datatype question
Title: sql*loader datatype question Lisa, Try to specify 'BADFILE' when using SQL*LOADER, should help you to find bad record. From FM: When SQL*Loader executes, it can create a file called a bad file or reject file in which it places records that were rejected because of formatting errors or because they caused Oracle errors. If you have specified that a bad file is to be created, the following applies: if one or more records are rejected, the bad file is logged. Igor Neyman, OCP DBAPerceptron, Inc.(734)414-4627[EMAIL PROTECTED] - Original Message - From: Koivu, Lisa To: Multiple recipients of list ORACLE-L Sent: Thursday, August 30, 2001 1:33 PM Subject: sql*loader datatype question I'm hitting a wall here. I'm trying to load data into a large table. Two of the fields are phone numbers (10 digits). The database field is defined as number(10). When I try to load these records, they err out with an error similar to below. Record 858: Rejected - Error on table INV_OWNER.FPSITE, column FPS_AFFIL_LTD_PTS_FPS. ORA-01722: invalid number So I change the fields to character, get the data in and it truly is all 10-digit numbers. I'm wondering if I'm missing something in the format. The doco says that INTEGER EXTERNAL is to be used for "human readable" numbers, non-binary, when you want to specify the length. The length I've chosen is correct. I can even create a table with number(10) columns and insert the varchar(10) phone number data into it! What am I missing? I really do not want to change all my number fields to character. It would defeat the purpose. I have a feeling I'm missing some sort of conversion in SQL*Loader but I don't see it off the top of my head. Thanks in advance for any suggestions. Lisa Koivu wanna-be DBA Hand Holder (that's what I've been doing this morning with an ex-coworker) and DBA Ft. Lauderdale, FL, USA The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments.
RE: sql*loader datatype question
Title: RE: sql*loader datatype question Yup, looked at that too. Unfortunately the record is 1500 characters long. That's a lot to just eyeball. Thanks -Original Message- From: Igor Neyman [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 2:31 PM To: Multiple recipients of list ORACLE-L Subject: Re: sql*loader datatype question Lisa, Try to specify 'BADFILE' when using SQL*LOADER, should help you to find bad record. From FM: When SQL*Loader executes, it can create a file called a bad file or reject file in which it places records that were rejected because of formatting errors or because they caused Oracle errors. If you have specified that a bad file is to be created, the following applies: if one or more records are rejected, the bad file is logged. Igor Neyman, OCP DBA Perceptron, Inc. (734)414-4627 [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> - Original Message - From: Koivu, Lisa <mailto:[EMAIL PROTECTED]> To: Multiple recipients of list ORACLE-L <mailto:[EMAIL PROTECTED]> Sent: Thursday, August 30, 2001 1:33 PM Subject: sql*loader datatype question I'm hitting a wall here. I'm trying to load data into a large table. Two of the fields are phone numbers (10 digits). The database field is defined as number(10). When I try to load these records, they err out with an error similar to below. Record 858: Rejected - Error on table INV_OWNER.FPSITE, column FPS_AFFIL_LTD_PTS_FPS. ORA-01722: invalid number So I change the fields to character, get the data in and it truly is all 10-digit numbers. I'm wondering if I'm missing something in the format. The doco says that INTEGER EXTERNAL is to be used for "human readable" numbers, non-binary, when you want to specify the length. The length I've chosen is correct. I can even create a table with number(10) columns and insert the varchar(10) phone number data into it! What am I missing? I really do not want to change all my number fields to character. It would defeat the purpose. I have a feeling I'm missing some sort of conversion in SQL*Loader but I don't see it off the top of my head. Thanks in advance for any suggestions. Lisa Koivu wanna-be DBA Hand Holder (that's what I've been doing this morning with an ex-coworker) and DBA Ft. Lauderdale, FL, USA The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments.
RE: sql*loader datatype question
how about a cut of the 10 chars, then grep looking for A-Z, a-z in whats left. joe >>> [EMAIL PROTECTED] 08/30/01 02:58PM >>> Yup, looked at that too. Unfortunately the record is 1500 characters long. That's a lot to just eyeball. Thanks -Original Message- From: Igor Neyman [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 2:31 PM To: Multiple recipients of list ORACLE-L Subject: Re: sql*loader datatype question Lisa, Try to specify 'BADFILE' when using SQL*LOADER, should help you to find bad record. From FM: When SQL*Loader executes, it can create a file called a bad file or reject file in which it places records that were rejected because of formatting errors or because they caused Oracle errors. If you have specified that a bad file is to be created, the following applies: if one or more records are rejected, the bad file is logged. Igor Neyman, OCP DBAPerceptron, Inc.(734)414-4627[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> - Original Message - From: Koivu, Lisa <mailto:[EMAIL PROTECTED]> To: Multiple recipients of list ORACLE-L <mailto:[EMAIL PROTECTED]> Sent: Thursday, August 30, 2001 1:33 PM Subject: sql*loader datatype question I'm hitting a wall here. I'm trying to load data into a large table. Two of the fields are phone numbers (10 digits). The database field is defined as number(10). When I try to load these records, they err out with an error similar to below. Record 858: Rejected - Error on table INV_OWNER.FPSITE, column FPS_AFFIL_LTD_PTS_FPS.ORA-01722: invalid number So I change the fields to character, get the data in and it truly is all 10-digit numbers. I'm wondering if I'm missing something in the format. The doco says that INTEGER EXTERNAL is to be used for "human readable" numbers, non-binary, when you want to specify the length. The length I've chosen is correct. I can even create a table with number(10) columns and insert the varchar(10) phone number data into it! What am I missing? I really do not want to change all my number fields to character. It would defeat the purpose. I have a feeling I'm missing some sort of conversion in SQL*Loader but I don't see it off the top of my head. Thanks in advance for any suggestions. Lisa Koivuwanna-be DBA Hand Holder (that's what I've been doing this morning with an ex-coworker) and DBAFt. Lauderdale, FL, USA The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments.
Re: sql*loader datatype question
Lisa, Is the field you are attempting to load the last field in the string? is the delimited in place? The reason being is the EXTERNAL INTEGER data might be interpreted at char including the "space" at the end of the string. That has bit me before and now all non-numbers are enclosed in quotes. ROR mª¿ªm >>> [EMAIL PROTECTED] 08/30/01 01:33PM >>> I'm hitting a wall here. I'm trying to load data into a large table. Two of the fields are phone numbers (10 digits). The database field is defined as number(10). When I try to load these records, they err out with an error similar to below. Record 858: Rejected - Error on table INV_OWNER.FPSITE, column FPS_AFFIL_LTD_PTS_FPS. ORA-01722: invalid number So I change the fields to character, get the data in and it truly is all 10-digit numbers. I'm wondering if I'm missing something in the format. The doco says that INTEGER EXTERNAL is to be used for "human readable" numbers, non-binary, when you want to specify the length. The length I've chosen is correct. I can even create a table with number(10) columns and insert the varchar(10) phone number data into it! What am I missing? I really do not want to change all my number fields to character. It would defeat the purpose. I have a feeling I'm missing some sort of conversion in SQL*Loader but I don't see it off the top of my head. Thanks in advance for any suggestions. > Lisa Koivu > wanna-be DBA Hand Holder (that's what I've been doing this morning with an > ex-coworker) and DBA > Ft. Lauderdale, FL, USA > > The information in the electronic mail message is Cendant confidential and > may be legally privileged, it is intended solely for the addressee(s) > access to this internet electronic mail message by anyone else is > unauthorized. If you are not the intended recipient, any disclosure, > copying, distribution or any action taken or omitted to be taken in > reliance on it is prohibited and may be unlawful. > > The sender believes that this E-mail and any attachments were free of any > virus, worm, Trojan horse, and/or malicious code when sent. This message > and its attachments could have been infected during transmission. By > reading the message and opening any attachments, the recipient accepts > full responsibility for taking protective and remedial action about > viruses and other defects. Cendant Corporation or Affiliates are not > liable for any loss or damage arising in any way from this message or its > attachments. > > -- 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).
RE: sql*loader datatype question
Lisa, Have you tried loading without specifying any datatype and length in the control file? Just a thought... Prakash -Original Message- Sent: Thursday, August 30, 2001 1:33 PM To: Multiple recipients of list ORACLE-L I'm hitting a wall here. I'm trying to load data into a large table. Two of the fields are phone numbers (10 digits). The database field is defined as number(10). When I try to load these records, they err out with an error similar to below. Record 858: Rejected - Error on table INV_OWNER.FPSITE, column FPS_AFFIL_LTD_PTS_FPS. ORA-01722: invalid number So I change the fields to character, get the data in and it truly is all 10-digit numbers. I'm wondering if I'm missing something in the format. The doco says that INTEGER EXTERNAL is to be used for "human readable" numbers, non-binary, when you want to specify the length. The length I've chosen is correct. I can even create a table with number(10) columns and insert the varchar(10) phone number data into it! What am I missing? I really do not want to change all my number fields to character. It would defeat the purpose. I have a feeling I'm missing some sort of conversion in SQL*Loader but I don't see it off the top of my head. Thanks in advance for any suggestions. Lisa Koivu wanna-be DBA Hand Holder (that's what I've been doing this morning with an ex-coworker) and DBA Ft. Lauderdale, FL, USA The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash 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: sql*loader datatype question
Lisa, Can you show us the control file and a couple of lines of sample data? Are you sure you don't have an unprintable character before/after the phone number? Is this occurring for all the phone numbers? David A. Barbour Oracle DBA, OCP AISD 512-414-1002 "Koivu, Lisa" field.com> cc: Sent by:Subject: sql*loader datatype question [EMAIL PROTECTED] 08/30/2001 12:33 PM Please respond to ORACLE-L I'm hitting a wall here. I'm trying to load data into a large table. Two of the fields are phone numbers (10 digits). The database field is defined as number(10). When I try to load these records, they err out with an error similar to below. Record 858: Rejected - Error on table INV_OWNER.FPSITE, column FPS_AFFIL_LTD_PTS_FPS. ORA-01722: invalid number So I change the fields to character, get the data in and it truly is all 10-digit numbers. I'm wondering if I'm missing something in the format. The doco says that INTEGER EXTERNAL is to be used for "human readable" numbers, non-binary, when you want to specify the length. The length I've chosen is correct. I can even create a table with number(10) columns and insert the varchar(10) phone number data into it! What am I missing? I really do not want to change all my number fields to character. It would defeat the purpose. I have a feeling I'm missing some sort of conversion in SQL*Loader but I don't see it off the top of my head. Thanks in advance for any suggestions. Lisa Koivu wanna-be DBA Hand Holder (that's what I've been doing this morning with an ex-coworker) and DBA Ft. Lauderdale, FL, USA The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -- 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: sql*loader datatype question
Title: RE: sql*loader datatype question Well noo it's fixed-length and I think I'd end up with a big jumble, wouldn't I? No delims in the file. -Original Message- From: Bala, Prakash [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 2:48 PM To: Multiple recipients of list ORACLE-L Subject: RE: sql*loader datatype question Lisa, Have you tried loading without specifying any datatype and length in the control file? Just a thought... Prakash -Original Message- Sent: Thursday, August 30, 2001 1:33 PM To: Multiple recipients of list ORACLE-L I'm hitting a wall here. I'm trying to load data into a large table. Two of the fields are phone numbers (10 digits). The database field is defined as number(10). When I try to load these records, they err out with an error similar to below. Record 858: Rejected - Error on table INV_OWNER.FPSITE, column FPS_AFFIL_LTD_PTS_FPS. ORA-01722: invalid number So I change the fields to character, get the data in and it truly is all 10-digit numbers. I'm wondering if I'm missing something in the format. The doco says that INTEGER EXTERNAL is to be used for "human readable" numbers, non-binary, when you want to specify the length. The length I've chosen is correct. I can even create a table with number(10) columns and insert the varchar(10) phone number data into it! What am I missing? I really do not want to change all my number fields to character. It would defeat the purpose. I have a feeling I'm missing some sort of conversion in SQL*Loader but I don't see it off the top of my head. Thanks in advance for any suggestions. Lisa Koivu wanna-be DBA Hand Holder (that's what I've been doing this morning with an ex-coworker) and DBA Ft. Lauderdale, FL, USA The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation or Affiliates are not liable for any loss or damage arising in any way from this message or its attachments. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash 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: sql*loader datatype question
Title: RE: sql*loader datatype question Good explanation but no it's not -Original Message- From: Ron Rogers [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 30, 2001 2:48 PM To: Multiple recipients of list ORACLE-L Subject: Re: sql*loader datatype question Lisa, Is the field you are attempting to load the last field in the string? is the delimited in place? The reason being is the EXTERNAL INTEGER data might be interpreted at char including the "space" at the end of the string. That has bit me before and now all non-numbers are enclosed in quotes. ROR mª¿ªm >>> [EMAIL PROTECTED] 08/30/01 01:33PM >>> I'm hitting a wall here. I'm trying to load data into a large table. Two of the fields are phone numbers (10 digits). The database field is defined as number(10). When I try to load these records, they err out with an error similar to below. Record 858: Rejected - Error on table INV_OWNER.FPSITE, column FPS_AFFIL_LTD_PTS_FPS. ORA-01722: invalid number So I change the fields to character, get the data in and it truly is all 10-digit numbers. I'm wondering if I'm missing something in the format. The doco says that INTEGER EXTERNAL is to be used for "human readable" numbers, non-binary, when you want to specify the length. The length I've chosen is correct. I can even create a table with number(10) columns and insert the varchar(10) phone number data into it! What am I missing? I really do not want to change all my number fields to character. It would defeat the purpose. I have a feeling I'm missing some sort of conversion in SQL*Loader but I don't see it off the top of my head. Thanks in advance for any suggestions. > Lisa Koivu > wanna-be DBA Hand Holder (that's what I've been doing this morning with an > ex-coworker) and DBA > Ft. Lauderdale, FL, USA > > The information in the electronic mail message is Cendant confidential and > may be legally privileged, it is intended solely for the addressee(s) > access to this internet electronic mail message by anyone else is > unauthorized. If you are not the intended recipient, any disclosure, > copying, distribution or any action taken or omitted to be taken in > reliance on it is prohibited and may be unlawful. > > The sender believes that this E-mail and any attachments were free of any > virus, worm, Trojan horse, and/or malicious code when sent. This message > and its attachments could have been infected during transmission. By > reading the message and opening any attachments, the recipient accepts > full responsibility for taking protective and remedial action about > viruses and other defects. Cendant Corporation or Affiliates are not > liable for any loss or damage arising in any way from this message or its > attachments. > > -- 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).