Re: SQLLDR Invalid Number on Record 2: Windows to Unix

From: Volker Hetzer <firstname.lastname_at_ieee.org>
Date: Fri, 13 Oct 2006 13:55:49 +0200
Message-ID: <egnus5$cg3$1@nntp.fujitsu-siemens.com>

The ants are driving me crazy schrieb:

> Thanks for offering to help.  I ended up figuring it out.   I'm a
> little embarassed as the problem was much simpler than I assumed.  I
> had an incorrect, and not needed,  parameter on the INFILE clause.
> 
> By the way, what method is most common for loading data into Oracle
> tables?  SQLLDR? Stored Procedures? VB/JAVA programs?  Something Else?

This entirely depends on the situation.
sqlldr:

+ can read everything that's vaguely line oriented (i.e. non

	  hierarchical) into one or more tables


	+ doesn't need any programming

	+ can use direct path, bothers about indexes on its own

	+ really really fast

	+ nice diagnostics

	+ if you have to cross security barriers, a file is often easier

	  go get permitted than a direct connection.
	- needs at least one commit, typically a lot more. Not good
	  for transaction control. Direct path can made to work with only
	  one "commit".
	- no way to do pre/postprocessing from the loader session.
	  It would be great if one could incorporate a begin and end
	  PL/SQL block. Would obsolete the insert/truncate/append
	  and other cludges too.
	- the diagnostics are emitted for humans. Hiding sqlldr behind
	  another application is difficult.
external tables:
	I have no idea about them, but they didn't work for us,
	because of varrays, if I recall correctly.


	+ as fast as the loader

	- files have to be accessible by the database server
Stored procedures:


	+ run in the database, i.e. locally.

	+ full transaction control

	+ pre/postprocessing in the same transaction

	- probably slow. PL/SQL simply wasn't made for this
	- needs specially set up directories
	- no direct path (IMHO)
VB/JAVA:


	+ full transaction control

	+ pre/postprocessing in the same transaction

	- VB/JAVA :-)
	- no direct path (IMHO), unless VBcan use dll's.
c/c++:


	+ probably as fast as sqlldr

	+ full transaction control

	+ pre/postprocessing in the same transaction

	+ can do direct path

	- hell to code

In short, there is no one size fits all solution. If you have to deal with data in such amounts that you have to think about implementation, you have to find the compromise that's right for you.
I went from inserts (tcl/tk) to array bound inserts (vbscript) and then to 2 parallel sqlldr runs, one direct path unrecoverable for the simple tables and one conventional path into nologging tables for the complex data types.

Lots of Greetings!
Volker

-- 
For email replies, please substitute the obvious.

Received on Fri Oct 13 2006 - 06:55:49 CDT