Re:RE: SQL*Loader

From: <dgoulet_at_vicr.com>
Date: Fri, 30 Jun 2000 16:55:16 -0400
Message-Id: <10544.111021@fatcity.com>

Eric,

Yeah I could drop the column if we were using 8i, but were on 8.0 still. Also this "file" gets created, loaded, recreated, reloaded, etc... several thousands of times a day. Also have a requirement for no operator intervention, variable number of records in file, and must run in background while tester gets on with it's life. Looks like a Pro*C solution is all that's left.

Dick Goulet

____________________Reply Separator____________________

Subject: RE: SQL*Loader
Author: "Eric D. Pierce" <PierceED_at_csus.edu> Date: 6/30/00 1:43 PM

Can't you just drop the offending column (after load) in Oracle8? (Alter table...)

Otherwise if the file has to be preprocessed, and you are on Windows, and for some reason want to use a more platform specific solution than the one Henry is suggesting, use a utility text editor that has macro capabilities (keyboard recorder) to clean out the file?

The macro would basically do this for each line, starting at position 1:

search for "x" number of commas (I'm assuming that commas are only in the file as delimiters), until you find the one just before the data to be deleted
move the cursor one the the right of the comma if needed
start marking/selecting the text
search for the next comma/delimiter (this takes care of varying field length)
make the cursor back up one position if needed
delete
go to position 1 of next line

In the editor I'm describing, there is a "repeat" key. You can press the "repeat" key, put in the number of lines in the text file (eg, 25000), press the "macro" key, and it performs the macro for the specified number of "repeats". This is a funky little DOS editor that came bundled with other utilities in a wretched mess called WordPerfect Office 3. It is very easy, has most of the same function keys as the DOS Wordperfect4/5 editor. Of course it is "obsolete" and "unsupported". :)

If you have more than 5Mb data file, I'm not sure it would work, but I have used it to edit data files about 1,000 char wide, up to around 5-8mb in total size.

For large files, it runs somewhat faster in a DOS box on a Pentium than it did on the original 386s it was designed for!

HTH,
ep

Date sent:              Fri, 30 Jun 2000 11:12:50 -0800
To:                     Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
From:                   Henry Poras <HPoras_at_ctp.com>
Subject:                RE: SQL*Loader

> Thought I had something tucked away in my notes on this. T. Kyte wrote a
> reply to a similar question in the comp.databases.oracle.tools newsgroup.
> You can search for it through dejanews. It is dated 9/2/98, titled 'How to
> skip the first two fields in sql*loader'.
>
> Roughly it says that SQL*Loader by itself cannot do this. However, on a UNIX
> system you can cut the file and pipe the output to Loader. He also does this
> with a pl/sql sql*loader combination.
>
> If you have any trouble finding the note, just send me an e-mail and I can
> fax over a copy.
>
> Henry
>
> -----Original Message-----
> From: dgoulet_at_vicr.com [mailto:dgoulet_at_vicr.com]
> Sent: Friday, June 30, 2000 1:05 PM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL*Loader

...
> My problem is that we have an automatic tester that generates ASCII
> comma delimited text files of it's results to be loaded to the
> database. One of these files has a column in it that is no
> longer wanted in the database. Regrettably it's going to be
> some time before the test engineering folks get around to
> removing it from the file, but the engineers want it out like
> yesterday and wouldn't you know it's right in the middle of each
> line. The headache is that nothing in this results file is fixed
> in size. The records are from 60 to 80 characters long, with
> trailing nullcols, and the fields therein vary from test to test.
> Even this particular column varies from 1 to 5 characters.

...
> Thanks & Happy 4th for those of you in the States.

Celebrate democracy: support and vote reform!

http://www.ficus2000.com/
-
http://www.votenader.com/
-
http://www.greenparties.org/GreenParties/default.asp -
http://www.straighttalkamerica.com/

Matchbox Web Server:
http://wearables.stanford.edu/hardware.html -
Barcelona:
http://futures.wharton.upenn.edu/~jonath22/Gaudi/batllo.html Received on Fri Jun 30 2000 - 15:55:16 CDT