Re: SQL*Loader: Multiple loads from one control file

From: Gudrun Janssen <janssen_at_itu.fzk.de>
Date: Tue, 13 Jul 1999 16:19:50 +0200
Message-ID: <378B4B06.7710B6F2_at_itu.fzk.de>

Hallo Julien,

I'm doing it like this under WinNT:
I have a batchfile which looks like this:

%3:\orant\bin\sqlplus.exe %1/%2_at_DB_ALIAS @c:\db\scripts\tab.sql
%3:\orant\bin\sqlplus.exe %1/%2_at_DB_ALIAS @c:\db\scripts\grnt.sql
%3:\orant\bin\sqlldr73 control=c:\db\ctl\tab_1.ctl log=tab_1.log

data=c:\csv\tab_1.csv bad=tab_1.bad userid=%1/%2_at_DB_ALIAS errors=500 skip=1

%3:\orant\bin\sqlldr73 control=c:\db\ctl\tab_2.ctl log=tab_2.log data=c:\csv\tab_2.csv bad=tab_2.bad userid=%1/%2_at_DB_ALIAS errors=500 skip=1

I call this batchfile with three parameters: my username, my password and the driveletter the orant partition is on.

First line of the batchfile drops my tables and creates them new. Second line gives grants as needed. Third and fourth line are the interesting ones for you I think. I simply call the loader for every table I have. Where in my case the data is not in the ctl-file but in an extra csv-file.

So it's only executing the batchfile to setup as many tables I want.

Hope it helps
Gudrun

julien4280_at_my-deja.com wrote:

> Does anyone know how to perform multiple table loading operations using
> only a single control file? I would like to be able to generate a
> single 'dump' file from a 3rd party application that can be used to
> animate my entire Oracle database in one fell swoop. For example, I
> would like to be able to generate a control file that looked like:
>
> LOAD DATA
> INFILE *
> INTO TABLE "TABLE1" REPLACE
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (
> COL_1,
> COL_2)
> BEGINDATA
> 1,"Hello"
> 2,"World"
> 3,"Hello"
> 4,"World"
>
> LOAD DATA
> INFILE *
> INTO TABLE "TABLE2" REPLACE
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (
> COL_1,
> COL_2)
> BEGINDATA
> 1,"This"
> 2,"is"
> 3,"some"
> 4,"data"
>
> The problem with this is that SQL*Loader treats the second load as data
> rows from the first load. Can anyone think of a way to do this? Thanks
> in advance,
>
> - Julien
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Jul 13 1999 - 16:19:50 CEST