ith 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
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
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
:[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
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
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
]
field.com cc:
Sent by:Subject: sql*loader datatype question
[EMAIL PROTECTED
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
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
: Multiple recipients of list ORACLE-LSubject: 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
Anyway, what if you need to add some
non-domestic phone numbers?
Henry
-Original Message-From: Mercadante, Thomas F
[mailto:[EMAIL PROTECTED]]Sent: Thursday, August 30, 2001
3:46 PMTo: Multiple recipients of list ORACLE-LSubject:
RE: sql*loader datatype question
I agree
Title: RE: sql*loader datatype question
Lisa,
A .bad
file with the offending record should have been created when sqlloader abended.
Even at 1500 characters you should be able to eyeball the single bad record in a
good editor and figure out what values are in the phone number part
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
Title: awk, sed, windoze, sql*loader, other fun stuff
try
perl.
www.activestate.com
www.cpan.org
www.perl.com
it's
free and easy to load.
-Original Message-From: Koivu, Lisa
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, August 29, 2001
2:27 PMTo: Multiple recipients of list
Title: awk, sed, windoze, sql*loader, other fun stuff
Hello all,
I'm working on sql*loader scripts and I realize that I'd probably be DONE by now if I had awk and sed to use. Of course, as unix-deficient as I am, I don't have it. Does anyone know of a windows version of these utilities
Title: awk, sed, windoze, sql*loader, other fun stuff
One site that has the main Unix tools is http://virtunix.itribe.net/.
I have them and use them all the time.
Randy Pace Phone 801 495 9300 9662 S 700 E
Fax 801 495 9301 Sandy, Utah 84070
email [EMAIL PROTECTED]
- Original
Hi Lisa,
MKS Toolkit or Unix Utilites for Windows (Microsoft). Both work very well.
-Rocky
--- Koivu, Lisa [EMAIL PROTECTED] wrote:
Hello all,
I'm working on sql*loader scripts and I realize that I'd probably be
DONE by
now if I had awk and sed to use. Of course, as unix-deficient as I
ORACLE-L [EMAIL PROTECTED]
field.com cc:
Sent by:Subject: awk, sed, windoze,
sql*loader, other fun stuff
[EMAIL
List,
OS Novell 4.2 Oracle 7.3.4.5
I have a table test desc as fld1 number(5,3), fld2 varchar2(1).
The control file has ,(comma) delimited, optionally enclosed in (quotes).
The data file contains 99.999,C
It fails with an ORA-01401: inserted value to large for column.
Question. Why did
David,
SQL*Loader probably can't help here, because, unfortunatly,
the WHEN clause is not nearly as flexible as a SQL WHERE
clause. If no other solution presents itself, you could load
the data into a work table and write some PL/SQL code to
filter out the records that you don't want. You're
You could try:
when (9:12) 'UNIX'
However, this will only work if you have fixed field lengths in the input file.
Jonathan Gennick wrote:
David,
SQL*Loader probably can't help here, because, unfortunatly,
the WHEN clause is not nearly as flexible as a SQL WHERE
clause. If no other
Im using SQL*Loader to populate a database table with transaction
records. The data file contains
some transactions that I want to exclude from the load and they all contain the
same text string, something like xxx,yyy,FLAG_TEXT,xxx,yyy. Can I use a SQL statement in the SQL*Loader
control
rk for you.
Tom Mercadante Oracle Certified Professional
-Original Message-From: David Wagoner
[mailto:[EMAIL PROTECTED]]Sent: Monday, July 30, 2001
2:56 PMTo: Multiple recipients of list ORACLE-LSubject:
SQL*Loader question- Transforming Data
Im
using SQL*Loader to populate a
://MichiganWaterfalls.com * http://MetalDrums.org
Monday, July 30, 2001, 2:56:22 PM, you wrote:
DW I'm using SQL*Loader to populate a database table with transaction records.
DW The data file contains some transactions that I want to exclude from the
DW load and they all contain the same text string
isn't
loaded.
Best regards,
Jonathan Gennick
mailto:[EMAIL PROTECTED] * 906.387.1698
http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org
Monday, July 30, 2001, 2:56:22 PM, you wrote:
DW I'm using SQL*Loader to populate a database table with transaction
records.
DW
*loader script which failed due to the non possibility to
extend a rollback segment in the
TBS ROLLBACK_DATA.
Oracle version 8.1.6.2 on AIX 4.3.3.
I have a huge rollback segment in the TBS ROLLBACK_DATA, but how tell to
sql*loader to use
this rollback segment ?
I have not found any option to assign
Hi All,
Does anyone have a good example of loading an image (i.e..jpg, .gif, etc) into a BLOB
column using SQL*Loader.
Looked through the docs and can't seem to find what I need.
TIA
Rick
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED
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
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
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
Dear all,
I have a text file of 70 rows. After
usingsql*loader to load it into my table, I select * from my table it only
show 802 rows.
Some people told me that's because of buffer. Who
would like to show me how I ought to do? Thanks you and sorry for my bad
english.
BaoDuy
: Multiple recipients of list ORACLE-LSubject: Use
sql*loader
Dear all,
I have a text file of 70 rows. After
usingsql*loader to load it into my table, I select * from my table it
only show 802 rows.
Some people told me that's because of buffer. Who
would like to show me how I
.Subject: Use sql*loader
com
Ngo BaDu wrote:
Dear all,
I have a text file of 70 rows. After using sql*loader to load it
into my table, I select * from my table it only show 802 rows.
Some people told me that's because of buffer.
Rubbish.
Who would like to show
me how I ought to do? Thanks you and sorry for my
ngo_duy@fmail To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
.vnn.vn cc: (bcc: Jack van
Zanen/nlzanen1/External/MEY/NL)
Sent by: Subject: Use sql*loader
happend.
Ngo BaDu
ngo_duy@fmaiTo: Multiple recipients of list
ORACLE-L
l.vnn.vn[EMAIL PROTECTED]
Sent by: cc:
root@fatcity.Subject: Use sql*loader
Thanks in advance for help, been lurking for a couple of weeks.
I am using sqlldr73.exe to load a text file into an oracle 7.3.2.1.1 table. Field
datatype is number(14,0). What is the correct statement in the tablename.clr file
that will correctly load the data.
The clr file info latest
Try:
SICK_ACCRUAL POSITION(569:583) integer external(14),
VAC_ACCRUAL POSITION(585:599) integer external(14)
Helen
"Johnston, Steve" [EMAIL PROTECTED] wrote:
Thanks in advance for help, been lurking for a couple of weeks.I am using sqlldr73.exe to load a text file into an oracle 7.3.2.1.1
or inturrupted.
ROR mª¿ªm
[EMAIL PROTECTED] 06/18/01 08:50PM
Hello,
I have a sql*loader script which failed due to the non possibility to
extend a rollback segment in the
TBS ROLLBACK_DATA.
Oracle version 8.1.6.2 on AIX 4.3.3.
I have a huge rollback segment in the TBS ROLLBACK_DATA, but how
Hello,
I have a sql*loader script which failed due to the non possibility to
extend a rollback segment in the
TBS ROLLBACK_DATA.
Oracle version 8.1.6.2 on AIX 4.3.3.
I have a huge rollback segment in the TBS ROLLBACK_DATA, but how tell to
sql*loader to use
this rollback segment ?
I have
:
com Subject: Sql*Loader and assign a ROLLBACK
segment
.cc:
com Subject: Sql*Loader and assign
a ROLLBACK
segment
19-Jun-2001
08:50 AM
Please
respond
varchar2(30));
Nirmal.
-Original Message-
From: Darlene Marley [SMTP:[EMAIL PROTECTED]]
Sent: Friday, June 08, 2001 1:21 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Urgenr : Sql Loader Question
FIELDS TERMINATED BY X'9'
Chowdary, Suren wrote:
How wud i
FIELDS TERMINATED BY X'9'
Chowdary, Suren wrote:
How wud i load a TAB delimited data file through SQL*Loader into a
Table.
Like i use FIELDS TERMINATED BY , (for a comma seperated data file)
what shud i use for TAB delimited file...
Thanx for ur help,
Chowday
begin:vcard
Title: Urgenr : Sql Loader Question
How wud i load a TAB delimited data file through SQL*Loader into a Table.
Like i use FIELDS TERMINATED BY , (for a comma seperated data file)
what shud i use for TAB delimited file...
Thanx for ur help,
Chowday
Hi, i'm having forms4.5 and repots2.5, i need to install SQL loader, pls
give some idea for this
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Senthil Ganapathi
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego
to install SQL loader, pls
give some idea for this
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Senthil Ganapathi
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access
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
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
Hi,
I need some help...I am trying to use SQL loader
to load a flat file into a table...the problem I have is that
when trying to load a blank column into a date column...
this throws out an error...and the loader fails...do you know of a way of
how
to get around this problem...how can I
Hi,
Take a look at the nullif clause.
tom
-Original Message-
Sent: Tuesday, May 29, 2001 12:41 PM
To: Multiple recipients of list ORACLE-L
Hi,
I need some help...I am trying to use SQL loader
to load a flat file into a table...the problem I have is that
when trying to load a blank
to place in the column.
ROR mª¿ªm
[EMAIL PROTECTED] 05/29/01 12:41PM
Hi,
I need some help...I am trying to use SQL loader
to load a flat file into a table...the problem I have is that
when trying to load a blank column into a date column...
this throws out an error...and the loader fails...do you
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
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
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
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
Thanx to Witold and Prakash for suggesting NULLIF and DEFAULTIF.
Unfortunately, it looks like both of those do basically the opposite of
what I'm trying to accomplish. Essentially, I want to prevent
sql*loader from ever inserting NULL in a column (which may not be
numeric), substituting
of
what I'm trying to accomplish. Essentially, I want to prevent
sql*loader from ever inserting NULL in a column (which may not be
numeric), substituting whatever value is appropriate for the given
application instead. Something like using "nvl(:field,'unknown')" in
the field spe
1, at 7:20, [EMAIL PROTECTED] wrote:
Thanx to Witold and Prakash for suggesting NULLIF and DEFAULTIF.
Unfortunately, it looks like both of those do basically the opposite of
what I'm trying to accomplish. Essentially, I want to prevent
sql*loader from ever inserting NULL in a column
Greg, look into the 'defaultif' option.
Prakash
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Monday, April 02, 2001 2:50 PM
To: Multiple recipients of list ORACLE-L
When using direct path loads, is it possible to have sql*loader insert a
default value in place of NULL
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
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
Chaim,
Along these lines, can't you disable the not null constraint, load the
data, update the table/columns (UPDATE MY_TABLE SET THIS_COLUMN = 0
WHERE THIS COLUMN IS NULL) then re-enable the constraints?
David
Ron Rogers wrote:
Chaim,
If on the other hand the BLANK will not work properly
lto:[EMAIL PROTECTED]]Sent: Friday, March 23, 2001 2:47 PMTo: Multiple recipients of list ORACLE-LSubject: SQL Loader Help in 8.1.6
Dear Experts:
This does not make any sense to me, can you help me out?
I have a control file to interpret the mainframe data and then write to my table in Oracle Dat
Hello Stephen,
Hey, I saw your post on the Oracle list. Thanks for plugging
the book. You can thank Sanjay for that tuning chapter. BTW,
which of his suggestions did you use that did so much good?
Best regards,
Jonathan Gennick
mailto:[EMAIL PROTECTED] * 906.387.1698
http://Gennick.com *
Dear Experts:
This does not make any sense to me, can you help me out?
I have a control file to interpret the mainframe data and then write to my table in Oracle Database.
The mainframe data looks like "0}", The table is designed as Number(10,2), The control file use zoned(10,2).
All
At 11:47 AM 3/23/01 -0800, you wrote:
The mainframe data looks like "0}", The table is designed as Number(10,2), The control file use zoned(10,2).
That looks like the internal representation for a signed number under COBOL. The least significant digit (LSD) is used to store the
2:47
PMTo: Multiple recipients of list ORACLE-LSubject: SQL
Loader Help in 8.1.6
Dear Experts:
This does not make any sense to me, can you help me out?
I have a control file to interpret the mainframe data and then write to my
table in Oracle Database.
The mainframe data looks like
8.1.7/Win2000
While loading a table with small tiff images (6k - 15K, out of line
Blob) and supporting data using SQL*loader we experienced a problem
that some of the images getting loaded aren't matching up with the
supporting data. Row 95 has the images from Row 1985, etc. Appears
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
Hi,
I need to find whether SYSDATE falls between beginning
date and ending date while loading data from a flat
file. I searched Oracle manuals and Metalink, could
not find a clue. Is this possible at all? If so how?
example:
=
LOAD DATA
INFILE 'price.dat'
INTO TABLE price
WHEN
Wanted to report my findings from MetaLink on why
SQL*Loader was unable to write records to a bad file.
Note:1047807.6 says that, with Oracle 8.0.4, records
larger than 270 bytes can't be written to a bad file.
Each record is rather large, and I think this is why I
can't write to the bad file
Hi,
I am using Oracle 8.0.4 on Solaris 2.6.
Trying to load data from a text file to a table using
SQL*Loader.
Receiving the following error during the load:
SQL*Loader-523: error-2 writing to file
(/mnt/voyager/ccuser/ccuser.bad)
The load.shl file contains bad file parameter:
BAD=$DIR
301 - 373 of 373 matches
Mail list logo
