RE: SQLLDR question SOLVED
Thanks for all the help. It turns out there was a problem with the data and the field seperator was part of the data in one of the fields. -Original Message- Sent: Friday, July 26, 2002 10:33 AM To: Multiple recipients of list ORACLE-L I am trying to load data via sqlldr (8.1.6.3 on Solaris 8). A couple of records keep kicking out. Because on the commonality between the records that are kicked out, I have a question. Does sqlldr treat an the same way that sqlplus does. In other words, if one of the fields in a records has an embedded in it, will sqlldr reject the record? If so, is there a way around this? 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: 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).
RE: SQLLDR question
Terry, I tried the following in 817 and it worked fine: LOAD DATA INFILE * INTO TABLE test REPLACE FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( col1 char , col_seq test_seq.nextval ) BEGINDATA 'one', 'two', (sorry about the first email - fingers slipped!) Do you get an error in the sqlldr log? - what does it say? Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, July 26, 2002 11:33 AM To: Multiple recipients of list ORACLE-L I am trying to load data via sqlldr (8.1.6.3 on Solaris 8). A couple of records keep kicking out. Because on the commonality between the records that are kicked out, I have a question. Does sqlldr treat an the same way that sqlplus does. In other words, if one of the fields in a records has an embedded in it, will sqlldr reject the record? If so, is there a way around this? 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: SQLLDR question
I'm not a sqlloader expert, so take with a large grain of salt. If you are using conventional path, a series of 'insert' statements are executed. This may cause the to be interpreted as the variable indicator. If you are using direct path, it may create a block of rows and then directly place them in the proper extent without using the SQL layer. So, try direct path and see if it works, but no promises. Another thought is to do a search and replace of the data and add the escape character (\) before each . Again, try it, but no promises. -Original Message- Sent: Friday, July 26, 2002 9:33 AM To: Multiple recipients of list ORACLE-L I am trying to load data via sqlldr (8.1.6.3 on Solaris 8). A couple of records keep kicking out. Because on the commonality between the records that are kicked out, I have a question. Does sqlldr treat an the same way that sqlplus does. In other words, if one of the fields in a records has an embedded in it, will sqlldr reject the record? If so, is there a way around this? 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: Fink, Dan 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: SQLLDR Question
Can you remove the comma between date and time to make it 1 field? Simon Fox Room 205, CRH 01270 533997 -Original Message- Sent: 23 May 2001 22:41 To: Multiple recipients of list ORACLE-L I am trying to load a file that has the fields comma-delimited, variable length. A sample line from the file looks like this: 503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21 The problem I am having is putting the date and time together. The control file looks like this: load data infile 'cyber_real.dat' append into table rit_cyber_hist when order_id 'Order ID' and order_id '' fields terminated by ',' (order_id, trans_nbr, cyber_status, trans_type, auth_code, avs_code, trans_amt, cic_resp, po_nbr, ship_to_zip, tax_amt, trans_date date(14) mm/dd/yy,hh:mi) It ignores the time portion of the file, I presume because it has a comma before it and it assumes that it is a different field. I can't figure out any way to get this loaded with the trans_date field containing both the date and time. This is on Oracle 8.1.6.0 on Sun Solaris. Any suggestions? Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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: SQLLDR Question
Thank you to everyone who responded to my request. I guess it's just a limitation of sqlldr that it can't handle this without modifying the data. Unfortunately, I can't have the source change their download, so I have to deal with the data as I get it. I did come up with a way to get around the problem. I created a text field for the time and created an on-insert trigger that appends the time to the date field and puts it back into the date field. That works at the minimal cost of a few extra bytes per record and a small amount of extra processing time. Here is the trigger that I came up with: TRIGGER rit_cyber_hist_i before insert on rit_cyber_hist for each row begin :new.trans_date := to_date(to_char(:new.trans_date,'mm/dd/yy ') || :new.time_char,'mm/dd/yy hh24:mi'); end; -Original Message- Sent: 23 May 2001 22:41 To: Multiple recipients of list ORACLE-L I am trying to load a file that has the fields comma-delimited, variable length. A sample line from the file looks like this: 503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21 The problem I am having is putting the date and time together. The control file looks like this: load data infile 'cyber_real.dat' append into table rit_cyber_hist when order_id 'Order ID' and order_id '' fields terminated by ',' (order_id, trans_nbr, cyber_status, trans_type, auth_code, avs_code, trans_amt, cic_resp, po_nbr, ship_to_zip, tax_amt, trans_date date(14) mm/dd/yy,hh:mi) It ignores the time portion of the file, I presume because it has a comma before it and it assumes that it is a different field. I can't figure out any way to get this loaded with the trans_date field containing both the date and time. This is on Oracle 8.1.6.0 on Sun Solaris. Any suggestions? Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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). -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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: SQLLDR Question
Since those are the last two fields in the data file, I think you can use TERMINATED BY WHITESPACE on the field definition. Something like: trans_date date(14) mm/dd/yy,hh:mi terminated by whitespace -Original Message- From: srcdco [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 23, 2001 4:41 PM To: ORACLE-L Cc: srcdco Subject: SQLLDR Question I am trying to load a file that has the fields comma-delimited, variable length. A sample line from the file looks like this: 503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21 The problem I am having is putting the date and time together. The control file looks like this: load data infile 'cyber_real.dat' append into table rit_cyber_hist when order_id 'Order ID' and order_id '' fields terminated by ',' (order_id, trans_nbr, cyber_status, trans_type, auth_code, avs_code, trans_amt, cic_resp, po_nbr, ship_to_zip, tax_amt, trans_date date(14) mm/dd/yy,hh:mi) It ignores the time portion of the file, I presume because it has a comma before it and it assumes that it is a different field. I can't figure out any way to get this loaded with the trans_date field containing both the date and time. This is on Oracle 8.1.6.0 on Sun Solaris. Any suggestions? Thank you. -- Scott Canaan ([EMAIL PROTECTED]) (716) 475-7886 Life is like a sewer, what you get out of it depends on what you put into it - Tom Lehrer -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Scott Canaan 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: SQLLDR Question
You can combine two data fields into one column with sqlldr, but I think both
fields have to be in the table.
Example:
SQL desc x
Name Type
-
TRANS_DATEDATE
F1CHAR(8)
LOAD DATA
INFILE *
APPEND
INTO TABLE x
FIELDS TERMINATED BY ','
(f1,
trans_date to_date(:f1||' '||:trans_date,'mm/dd/yy hh24:mi')
)
BEGINDATA
05/01/01,14:21
05/24/01,9:57
(After the load, you can ALTER TABLE table DROP COLUMN column to get rid of the
f1 column,)
Jared Still [EMAIL PROTECTED] on 05/23/2001 11:16:00 PM
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:(bcc: Chaim Katz/Completions/Bombardier)
Instead of trying to do this in SQL Loader, why not just
try to clean up the data?
This can be done at least 2 methods:
1. Get the sender to send it to you properly
2. clean it up yourself.
The following Perl script will do it
Jared
#!/usr/bin/perl
my $file=data.txt;
open(DATA,$file) || die cannot open $file - $!\n;
while(DATA){
chomp;
my @data = split(/,/);
# append last field to penultimate field, remove last field
$data[ $#data -1] .= - . $data[ $#data ];
undef $data[ $#data ];
#print it all out
my $newData = join(',',@data);
# remove trailing comma
chop $newData;
print $newData\n;
}
On Wednesday 23 May 2001 14:41, Scott Canaan wrote:
I am trying to load a file that has the fields comma-delimited,
variable length. A sample line from the file looks like this:
503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21
The problem I am having is putting the date and time together. The
control file looks like this:
load data
infile 'cyber_real.dat'
append
into table rit_cyber_hist
when order_id 'Order ID' and order_id ''
fields terminated by ','
(order_id,
trans_nbr,
cyber_status,
trans_type,
auth_code,
avs_code,
trans_amt,
cic_resp,
po_nbr,
ship_to_zip,
tax_amt,
trans_date date(14) mm/dd/yy,hh:mi)
It ignores the time portion of the file, I presume because it has a
comma before it and it assumes that it is a different field. I can't
figure out any way to get this loaded with the trans_date field
containing both the date and time.
This is on Oracle 8.1.6.0 on Sun Solaris.
Any suggestions? Thank you.
--
Scott Canaan ([EMAIL PROTECTED])
(716) 475-7886
Life is like a sewer, what you get out of it depends on what you put
into it - Tom Lehrer
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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: SQLLDR Question
In 8.1.6, you don't have to have the one column in the database. Use
'FILLER' as the data type.
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]
[EMAIL PROTECTED]
ardier.com To: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED] cc:
Fax to:
Subject: Re: SQLLDR
Question
05/24/2001 11:31 AM
Please respond to ORACLE-L
You can combine two data fields into one column with sqlldr, but I think
both
fields have to be in the table.
Example:
SQL desc x
Name Type
-
TRANS_DATEDATE
F1CHAR(8)
LOAD DATA
INFILE *
APPEND
INTO TABLE x
FIELDS TERMINATED BY ','
(f1,
trans_date to_date(:f1||' '||:trans_date,'mm/dd/yy hh24:mi')
)
BEGINDATA
05/01/01,14:21
05/24/01,9:57
(After the load, you can ALTER TABLE table DROP COLUMN column to get rid of
the
f1 column,)
Jared Still [EMAIL PROTECTED] on 05/23/2001 11:16:00 PM
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:(bcc: Chaim Katz/Completions/Bombardier)
Instead of trying to do this in SQL Loader, why not just
try to clean up the data?
This can be done at least 2 methods:
1. Get the sender to send it to you properly
2. clean it up yourself.
The following Perl script will do it
Jared
#!/usr/bin/perl
my $file=data.txt;
open(DATA,$file) || die cannot open $file - $!\n;
while(DATA){
chomp;
my @data = split(/,/);
# append last field to penultimate field, remove last field
$data[ $#data -1] .= - . $data[ $#data ];
undef $data[ $#data ];
#print it all out
my $newData = join(',',@data);
# remove trailing comma
chop $newData;
print $newData\n;
}
On Wednesday 23 May 2001 14:41, Scott Canaan wrote:
I am trying to load a file that has the fields comma-delimited,
variable length. A sample line from the file looks like this:
503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21
The problem I am having is putting the date and time together. The
control file looks like this:
load data
infile 'cyber_real.dat'
append
into table rit_cyber_hist
when order_id 'Order ID' and order_id ''
fields terminated by ','
(order_id,
trans_nbr,
cyber_status,
trans_type,
auth_code,
avs_code,
trans_amt,
cic_resp,
po_nbr,
ship_to_zip,
tax_amt,
trans_date date(14) mm/dd/yy,hh:mi)
It ignores the time portion of the file, I presume because it has a
comma before it and it assumes that it is a different field. I can't
figure out any way to get this loaded with the trans_date field
containing both the date and time.
This is on Oracle 8.1.6.0 on Sun Solaris.
Any suggestions? Thank you.
--
Scott Canaan ([EMAIL PROTECTED])
(716) 475-7886
Life is like a sewer, what you get out of it depends on what you put
into it - Tom Lehrer
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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
Re: SQLLDR Question
Instead of trying to do this in SQL Loader, why not just
try to clean up the data?
This can be done at least 2 methods:
1. Get the sender to send it to you properly
2. clean it up yourself.
The following Perl script will do it
Jared
#!/usr/bin/perl
my $file=data.txt;
open(DATA,$file) || die cannot open $file - $!\n;
while(DATA){
chomp;
my @data = split(/,/);
# append last field to penultimate field, remove last field
$data[ $#data -1] .= - . $data[ $#data ];
undef $data[ $#data ];
#print it all out
my $newData = join(',',@data);
# remove trailing comma
chop $newData;
print $newData\n;
}
On Wednesday 23 May 2001 14:41, Scott Canaan wrote:
I am trying to load a file that has the fields comma-delimited,
variable length. A sample line from the file looks like this:
503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21
The problem I am having is putting the date and time together. The
control file looks like this:
load data
infile 'cyber_real.dat'
append
into table rit_cyber_hist
when order_id 'Order ID' and order_id ''
fields terminated by ','
(order_id,
trans_nbr,
cyber_status,
trans_type,
auth_code,
avs_code,
trans_amt,
cic_resp,
po_nbr,
ship_to_zip,
tax_amt,
trans_date date(14) mm/dd/yy,hh:mi)
It ignores the time portion of the file, I presume because it has a
comma before it and it assumes that it is a different field. I can't
figure out any way to get this loaded with the trans_date field
containing both the date and time.
This is on Oracle 8.1.6.0 on Sun Solaris.
Any suggestions? Thank you.
--
Scott Canaan ([EMAIL PROTECTED])
(716) 475-7886
Life is like a sewer, what you get out of it depends on what you put
into it - Tom Lehrer
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
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).
