I am using sqlldr conventional path to load some data.
My understanding is that I cannot use direct path since my users require
access to the table at the same time as the load is in progress. Is this a
correct assumption?
The speed of the upload is slow, or I think so10,000 rows is taking
20
an external table and a
merge. go to asktom. he has an example.
From: John Dunn [EMAIL PROTECTED]
Date: 2003/08/15 Fri AM 07:09:28 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: speeding up conventional path sqlldr
I am using sqlldr conventional path to load some
: speeding up conventional path sqlldr
I am using sqlldr conventional path to load some data.
My understanding is that I cannot use direct path since my users require
access to the table at the same time as the load is in progress. Is this a
correct assumption?
The speed of the upload is slow
I'm a sqlldr newbie. Platform is Oracle 9.2.
My question is...can I prevent sqlldr loading any data if there is even a
single bad record, even if it is, for example, the last record? I am using
conventional path
load.
John
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
An excellent book for SQL Loader new comers, and experienced users as well, is one by
Jonathan
Gennick and Sanjay Mishra : Oracle SQL Loader, The definitive Guide, by O'Reilly.
Stenphen Andert will agree with me ;)
- Kirti
--- John Dunn [EMAIL PROTECTED] wrote:
I'm a sqlldr newbie
[EMAIL PROTECTED]
Subject: Re: sqlldr questions
An excellent book for SQL Loader new comers, and experienced users as well, is one
by Jonathan
Gennick and Sanjay Mishra : Oracle SQL Loader, The definitive Guide, by O'Reilly.
Stenphen Andert will agree with me ;)
- Kirti
--- John Dunn
Please look the following text, which works fine everyday for loading my data into
oracle 9.2
database.
$ORACLE_HOME/bin/sqlldr USERID=your_username/[EMAIL PROTECTED] control=link-summary.ctl
log=link-summary.log
$ more link-summary.ctl
options (rows=9297500, errors=2, bindsize=100
I'm hoping for expert assistance. I'm using SQL*Loader to load some
2200+ records (comma delimited) into a table and running into this
problem for the modcnt field:
The control file reads:
modcnt DECIMAL EXTERNAL -- I'm sure this is where my problem is. I have
tried almost every datatype that
Krishna,
Have you tried putting a . at the end of your file name when calling
sqlldr? Or can you rename the file to be emp.dat at the os level?
Stephen
[EMAIL PROTECTED] 12/28/02 09:13PM
Hi,
Oracle 8.1.7 on SUN Solaris 5.8
I want to load a datafile with no extension into Oracle using
SQL
infile 'abcd.'
Yechiel AdarMehish
- Original Message -
From:
Krishna
Kakatur
To: Multiple recipients of list ORACLE-L
Sent: Sunday, December 29, 2002 6:13
AM
Subject: Datafile with no extension using
sqlldr
Hi,
Oracle 8.1.7 on SUN Solaris 5.8
I
Hi,
Oracle 8.1.7 on SUN Solaris 5.8
I want to load a datafile with no extension into
Oracle using SQL*Loader. But it looks for a file with a .dat extension. This is
the exact error message I got:
SQL*Loader-500: Unable to open file
(emp.dat)SQL*Loader-553: file not foundSQL*Loader-509:
to change
skip2 to read:
skip2 FILLER char enclosed by (SRVR= and =,
Finally, we need to call SQL*Loader (for me this is on 8.1.7 on HP/UX):
sqlldr MYSCHEMANAME control=listener.sqlload log=listener_sqlload.log
direct=false rows=2000 bindsize=1000 readsize=1000
...where MYSCHEMANAME
Hi All,
Oracle 8.1.6 WinNT
I have been trying to use sqlldr and I am getting following error
Record 1: Rejected - Error on table INTERVIEW, column COMMENTS.
end of logical record found when reading length of varying length field
Here is sample record and control file.
Any ideas why I get
, October 02, 2002 12:19 PM
To: Multiple recipients of list ORACLE-L
Hi All,
Oracle 8.1.6 WinNT
I have been trying to use sqlldr and I am getting following error
Record 1: Rejected - Error on table INTERVIEW, column COMMENTS.
end of logical record found when reading length of varying length field
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
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
, 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
I try to insert 14,000 rows into oracle database using SQLLDR and it takes
too long to finish (around an hour). Is there a way to improve SQLLDR to
make it run faster? For example, if modify parameter file will help?
Thanks,
David
--
Please see the official ORACLE-L FAQ: http
self-plug
Stephen
[EMAIL PROTECTED] 06/20/02 04:20PM I
try to insert 14,000 rows into oracle database using SQLLDR and it takestoo
long to finish (around an hour). Is there a way to improve SQLLDR
tomake it run faster? For example, if modify parameter file will
help?Thanks,David-- Please see
into oracle database using SQLLDR and it takes
too long to finish (around an hour). Is there a way to improve SQLLDR to
make it run faster? For example, if modify parameter file will help?
Thanks,
David
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nguyen, David M
INET
DIR=directory where the files live
ls $DIR |
while read FILE
do
sqlldr ... file=$DIR/$FILE ...
done
--- Ravindra [EMAIL PROTECTED] wrote:
We have a process that generates log file
continously with dateandtime stamp on the filename
like log_041502_113545.log
These log files should
We have a process that generates log file
continously with dateandtime stamp on the filename
like log_041502_113545.log
These log files should be loade to the table using
sql loader.I have the sqlldr script to load to
the table.But this sqlldr will be part of the shell
script
THEN ' ||chr(13)||chr(10)||
'chargepercent(50)' ||chr(13)||chr(10)|| 'ELSE ' ||chr(13)||chr(10)||
'chargepercent(25)' ||chr(13)||chr(10)|| 'ENDIF'
When I use the above flat file to load the table with sqlldr the table is
not getting populated the way I expected. How can I insert the new
)|| 'Chargeperce(100)'
||chr(13)||chr(10)||
'ELSEIF ServiceDate sysdate THEN '
||chr(13)||chr(10)||
'chargepercent(50)' ||chr(13)||chr(10)|| 'ELSE '
||chr(13)||chr(10)||
'chargepercent(25)' ||chr(13)||chr(10)||
'ENDIF'
When I use the above flat file to load the table
with sqlldr the table
, 2002 2:13
AM
Subject: Re: sqlldr
HI Yechiel :
How can i to install two versions of Oracle in
the same server , like :
Oracle 8i and Oracle 9i, in oracle_home
different.
Is it possible ?
Regards
Eriovaldo
- Original Message -
From
ok Yechiel.
Thanks for the answer.
Regards
Eriovaldo
- Original Message -
From:
Yechiel
Adar
To: Multiple recipients of list ORACLE-L
Sent: Sunday, April 28, 2002 6:43
AM
Subject: Re: sqlldr
Hello
I do not have experience with 9i and
8i.
One
, 2002 6:43
PM
Subject: Re: sqlldr
Ravi,
To the best of my knowledge, there is no limitation on the
number of records that can be loaded at a time other than the size of your
rollback segments. I have loaded 10's of millions of rows in a single sqlldr
job by setting some
: Saturday, April 27, 2002 8:18
PM
Subject: Re: sqlldr
Hello Stephen
Thanks for the link.
The article is very illuminating.
As we have some loader tuning to do I thank you
for sharing the knowledge.
Yechiel Adar
Mehish
- Original Message -
From
Ravi,
To the best of my knowledge, there is no limitation on the
number of records that can be loaded at a time other than the size of your
rollback segments. I have loaded 10's of millions of rows in a single sqlldr job
by setting some parameters. There are defaults that
work, but don't
i belive there is a limitation on the number of records that can be loaded
from sqlldr at a time based on the bind array and rows default parameter.
if i have a data file with more than 5000records(don't know the exact
number) that needs to be imported using sqlldr
what is the value i need to set
Hi
I installed 9iAS 1.0.2.2.2 on Solaris 8. All went well except
the Portal component failed. When I tried to re-run the
opca (portal configuration assistant) it failed on a segmentation
fault in SQLLDR. Oracle support seems to be stumped by this.
Has anyone else run into this? Thanks,
Ben
variables (for Oracle 8i only, none of the ones for 9iAS) and
sqlldr works OK.
If you ever find a solution for this problem I'll appreciate it if you tell
me what you did.
Thanks
Ana E. Choto
Systems Programmer
American University
e-Operations - Information Technology
Phone (202) 885-2275
Fax
Good morning,
We are having a problem load a file containing dates in MON DD format
into a database having and nls_date_format=MMDD. Is there a way to do
an alter session command in the control file or the parameter file to that
we can load this data?
We have had the production
Ruth Gramolini wrote:
Good morning,
We are having a problem load a file containing dates in MON DD format
into a database having and nls_date_format=MMDD. Is there a way to do
an alter session command in the control file or the parameter file to that
we can load this data?
Ruth:
Are you using a delimited flat file via SQL Loader? Would the to_date
function do the trick?
My 0.03 Euro's worth,
Ken
-Original Message-
Sent: Thursday, January 10, 2002 7:25 AM
To: Multiple recipients of list ORACLE-L
Subject:nls_date_format problem in sqlldr
).
Yechiel Adar, Mehish Computer Services
[EMAIL PROTECTED]
-Original Message-
From: Ruth Gramolini [SMTP:[EMAIL PROTECTED]]
Sent: Thu, January 10, 2002 3:25 PM
To: Multiple recipients of list ORACLE-L
Subject: nls_date_format problem in sqlldr
Good morning,
We are having a problem
Ruth,
If the source file is being loaded with SQL LOADER that you
can format the date load in the controlfile. We get our dates in the MMDD
format and load in to the database default by defining the source
data:
SaleDate TERMINATED BY ","
"TO_DATE(:SALEDATE,'MMDD')",
...Good
We got around this issue but if anyone knows whether this is possible I
would like to know for future reference.
Thanks anyway,
Ruth
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Thursday, January 10, 2002 8:25 AM
Good morning,
We are having a
Just set the environment variable before running sqlldr:
export NLS_DATE_FORMAT='MON DD '
Ruth Gramolini
I need to load a constant date into a column (01/01/2002) and I need to use
direct path loading. I tried:
creation_date to_date('01/01/2002','dd/mm/'),
SQL*Loader-417: SQL string (on column CREATION_DATE) not allowed in direct
path.
This works with conventional path load. I have tried
I want to put a number of ctl scripts together into one so I don't have to
run each script by itself. Is it possible to have scripts for direct path
load and conventional path load in the same sqlldr script? I don't think
so, but would like the opinion of others.
Thanks,
Ken Janusz, CPIM
HI Ken,
Two ideas
1.Have you tried using a default value on the column?
2.Load the data and then go back and do a mass update.
[EMAIL PROTECTED] wrote:
I need to load a constant date into a column (01/01/2002) and I need to use
direct path loading. I tried:
creation_date
try replacing
document_number sequence (max,80,1),
with
document_number sequence (max,1),
sqlloader is taking 80 as your increment...
docs show only 2 arguments for sequence function...
hth
-Original Message-
Sent: Tuesday, December 04, 2001 11:01 AM
To: Multiple
Ken,
This is what I did once to do a similar exercise. I defined a seq dk_seq as
in my case in the database and used the following control file. The next
value of sequence should depend on how you define the sequence.
LOAD DATA
INFILE 'sysx.csv'
TRUNCATE
INTO TABLE dk_tmp
FIELDS TERMINATED BY
Friends,
Has anyone developed a sqlldr control file to load listener.log? I have a
requirement to trap the IP addresses of failed connection attempts; this
info is not trapped as an IP address in DBA_AUDIT_TRAIL or AUD$. If you have
already accomplished this, let me know so I don't reinvent
Hallo,
Can anyone give me an example on a control.file for th esql ldr that does the
following:
Truncate table X inthe database
Loads data into table X
Makes an insert from that table into another table in the database.
And of course commit statement afterwards
Thanks in advance
Roland S
look in the sqlldr docs for example control files,
1. truncate is a keyword for control file
2. load data
3. create trigger to do the insert into another table.
[EMAIL PROTECTED] wrote:
Hallo,
Can anyone give me an example on a control.file for th esql ldr that does the
following
errors for the same file...
i have
tried other files with even more data but they all worked.
the
rows parameter in sqlldr is 64 (default).
i have
tried to load same file with rows =2..it worked well without giving
errors..
can u
plz point out what exactly is the probs...
thanks
there are two directory
entries, one for sqlload and one for sqlldr...
orapps1 $ ls -al | grep sql
-rwxr-x--x 2 oracle dba 3612900 Oct 3 1997 sqlldr
-rwxr-x--x 2 oracle dba 3612900 Oct 3 1997 sqlload
On the 8.1.6.2 server there is just sqlldr. I am wondering if the sqlload
link
Ken,
Earlier versions had sqlldr and sqlload as copies of the same code. I
think sqlload was a link in 8.0.x. Now with 8i it's gone. You can create the
link if you do not wish to change the existing code. We did just that since
Developers did not want to change their scripts.
HTH,
Regards
I have checkout the solution offer by Oracle , as they said use the sqlldr
come together with Oracle 7 , but if we upgrade our system to 8i already ,
where can I still found the v7 sqlldr ??
IT also mention about OCI , what is OCI mean ? a interface program for
sqlldr with database
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
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
, 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
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
to:
Subject: Re: SQLLDR
Question
05/24/2001 11:31 AM
Please respond
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:
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
Hallo you DBA's:
How can I write a script in ctl-file if I want to import a textfile. I want to use the
command decode because I want to replace the character '?' with a 0 (zero), which is a
number field inthe database.
How can I do this. I want to do it while the sqlldrscript is running.
to:
com Subject: sqlldr to_number
with sqlldr or do I need to load the data as is and use
PL/SQL (and cursor loops) to concatentate the data?
thanks for any help!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Glenn Travis
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX
this be done with sqlldr or do I need to load the data as is and use
PL/SQL (and cursor loops) to concatentate the data?
thanks for any help!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538
: Helen Zhung [SMTP:[EMAIL PROTECTED]]
Sent: Friday, April 27, 2001 7:40 PM
To: Multiple recipients of list ORACLE-L
Subject:SQLLDR - whitespace (tab space) problem
Hello:
I'm new to SQL Loader. Is there a way to code in control file that
can
TERMINATED BY X'9'(textcol1, textcol2)BEGINDATASaj-Original Message-From: Helen Zhung [SMTP:[EMAIL PROTECTED]]Sent: Friday, April 27, 2001 7:40 PMTo: Multiple recipients of list ORACLE-LSubject: SQLLDR - whitespace (tab space) problemHello: I'm new to SQL Loader. Is there a way to code
Hello:
I'm new to SQL Loader. Is there a way to code incontrol filethatcan separate 'a tab' and 'a blank space' ?
I use "FIELDS TERMINATED BY WHITESPACE", but it will TERMINATE a field when there isa space or a tab. However, the data fields were separated only by the "tab", not 'a space'. Is it
Helen,
You can use the optionally parameter to specify multiple enclosed
by/terminated by parameters. NULLIF is also available to assist in
loading blanks.
Regards,
David A. Barbour
Oracle DBA, OCP
Helen Zhung wrote:
Hello:
I'm new to SQL Loader. Is there a way to code in control
You can no doubt do this with Sql*loader.
Personally, I like to clean up the data first instead. I find
it easier to work with that way.
These task are easy on unix, and not too bad on windoze if
you get some decent text tools.
The following line for instance will change all tab characters
Tom,
Just cogitating and ruminating...
You mentioned that you're executing the load as part of a larger
procedure. Couldn't you execute a shell script that would ftp the file
to the target server and run sqlldr via rsh -c? Another thought would
be to have the target file on an NFS
I could try
is running the sqlldr against the 7.3.2.3 database into a view of a table in
the 8.1.5 database through a DB link. I will try that if there is no quick
easy solution to my problem.
Oracle's reply is this. (their syntax - not mine) I think you may try the
workaround you mentioned above
I am executing a SQLLDR shell file from a server :: sourceserv; RDBMS 7.3.2.3
AIX 4.3.2
into a database on server:: destserv; RDBMS 8.1.5 AIX 4.3.3
using a SQL*Net connect string destserv_destSID.
This connect string is defined in the local tnsnames.ora and works. I confirmed
this by doing
Ok,maybe i am lazy or a poor reader, but are you
using 732 sqlldr binaries to load an 815 db?
Why not the 815 sqlldr binaries?
I mean, it sounds like you have to ship data over the
wire no matter what you do, so...why not use the latest
version of the sqlldr bits?
== -Original Message
No you are reading correctly. I have the data on a server with a 7.3.2.3
database and am using that databases' binary sqlldr to load into the 8.1.5
database. The problem is a timing issue. I need to execute the load from the
7.3.2.3 server as part of a larger script. I cannot ensure
I don't you *can* avoid the larger issue. Oracle 8 has undergone some radical changes
underneath the covers. I would expect SQLLDR to have changed just as radically...
[EMAIL PROTECTED] 04/23/01 02:06PM
No you are reading correctly. I have the data on a server with a 7.3.2.3
database
Have you run catexp7 on the 8.1.5 instance?
Pete Barnett
Oracle Database Administrator
Regence BlueCross BlueShield
[EMAIL PROTECTED]
On Mon, 23 Apr 2001, Mohan, Ross wrote:
Ok,maybe i am lazy or a poor reader, but are you
using 732 sqlldr binaries to load an 815 db?
Why not the 815
The missing verb is think, as in I don't think you can avoid
[EMAIL PROTECTED] 04/23/01 02:46PM
I don't you *can* avoid the larger issue. Oracle 8 has undergone some radical changes
underneath the covers. I would expect SQLLDR to have changed just as radically...
[EMAIL PROTECTED
To: Multiple recipients of list ORACLE-L
Subject: RE: Awfully annoying SQLLDR problem
No you are reading correctly. I have the data on a server with a 7.3.2.3
database and am using that databases' binary sqlldr to load into the 8.1.5
database. The problem is a timing issue. I need to execute
76 matches
Mail list logo
