Re: Can SQL*Loader do this?

HansF wrote:
> This looks like a variant on Case #7 from the SQL*Loader Case
> Studies section of the Oracle Utilities manual. (Available at
> http://docs.oracle.com)
>
> IMO, it would be considerably simpler to use Sybrand's suggestion and
> reformat using sed, awk or perl before loading and use a more
> straight forward sql*loader case.
>

Thanks for the lead. I ended up getting it working using the method listed in case #7 and also using another case example which inserted into the same table with another INTO TABLE clause. The global variable and insert trigger worked although I still have a problem if I don't commit after each row. For example, if rows is set to 64, then the global variable only gets updated after 64 inserts and my date is incorrect :( If I commit after every row it works as expected even though case7 says that it should work after every insert.

My settings were as follows which when combined with the global variable and trigger worked.

create table db_size (

	id integer,
	db_name	varchar2(8),
	size_mb	number(7,1),
	size_date date

# rows =1 is extremely bad performance but the insert trigger work # scorrectly then
options (errors = 100000, rows=1)
LOAD DATA
INFILE '/sqlldr/ext_tab_simple.dat'
truncate
DISCARDFILE 'sqlldr_file_.dsc'
INTO TABLE db_size

         when (1) <> '#'
(

         id recnum,
         db_name position(1:8)   CHAR    terminated by whitespace,
         size_mb position(15:26) decimal external

         when (8) = 'n'
(

         id recnum,
         size_date position(10:17) date(8) "DDD YYYY"