>I am creating Oracle Control files to use with SQL*Loader to import large text file into Oracle. I'm just wondering if this is the best way to do things? From everything I've read it seems to be. These files can vary from 200K to about 4GB in size. I use BULK INSERT in MS SQL to do the same thing right now.
>
>I am building code to generically build the CTL files for the various tables currently and just thought I would get a second opinion.
>
>As anyone else dumping large amounts of data into an Oracle backend this way?
>
>TIA

Absolutely! I created some VFP code using textmerge to create .CTL and .PAR files and the data [.DAT] files. When I was converting an old app to an Oracle database, I created some remote views of the Oracle tables, set table buffering on [I closed all the views and never issued a tableupdate], and converted the old data to the new format. I then scanned each view to create the DAT files. I used textmerge because of memo [LONG] data types, and also used the tilde [~] as the delimiter.

Since I created a .PAR [parameter] file, the syntax to load the data is SQLLDR filename.PAR

I also create a SQL*Plus script file that I ran to drop all indexes before running loader. I have a second script that recreates the indexes after loading the data. Of course if your data is being used, you don't have this luxury of dropping indexes before loading.

Even though this looks like it would take longer, creating the DAT files and using SQL Loader was still faster than issuing a tableupdate on all the views. Remarkable considering the most records any view had was about 36,000.

procedure pCreate_SQL_Load_Files
*
lparameter tcViewName
if vartype(tcViewName) <> "C" or empty(tcViewName) or isnull(tcViewName)
return
endif
local lcView, lcTable
lcView = alltrim(tcViewName)
if empty(lcView)
return
endif
lcTable = substr(lcView, 3)
if file("D:\Apps\Npdes99\SQL_Load\" + lcTable + ".ctl")
erase "D:\Apps\Npdes99\SQL_Load\" + lcTable + ".ctl"
endif
if file("D:\Apps\Npdes99\SQL_Load\" + lcTable + ".par")
erase "D:\Apps\Npdes99\SQL_Load\" + lcTable + ".par"
endif
if file("D:\Apps\Npdes99\SQL_Load\" + lcTable + ".dat")
erase "D:\Apps\Npdes99\SQL_Load\" + lcTable + ".dat"
endif
select (lcView)
set order to
local i
set textmerge on
set textmerge to "d:\apps\npdes99\sql_load\" + lcTable + ".par" noshow
\\userid=my_ora_id/my_password@dsn
\control=<>.ctl
\bad=<>.bad
set textmerge to "d:\apps\npdes99\sql_load\" + lcTable + ".ctl" noshow
\\LOAD DATA
\INFILE <>.dat
\INSERT
\ INTO TABLE <>
\ FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '~'
\ TRAILING NULLCOLS
\ (
for i = 1 to fcount()
if i > 1
\\
endif
do case
case type(field(i)) = "D"
\\<> DATE (8) "YYYYMMDD"
case type(field(i)) = "M"
&& assuming no memo field is longer than 1500 chars. can be increased.
\\<> CHAR (1500)
otherwise
\\<>
endcase
if i < fcount()
\\,
\
endif
endfor
\\)
\

set textmerge to "D:\Apps\Npdes99\SQL_Load\" + lcTable + ".dat"
locate
local lcProgCaption
lcProgCaption = lcTable + " Data File Creation Progress "
oBar.Value = 0
oProgress.Caption = lcProgCaption
oProgress.Show()

scan
for i = 1 to fcount()
do case
case inlist(type(field(i)), "C", "M")
\\~<>~
case type(field(i)) = "N"
\\<>
case type(field(i)) = "D"
\\<>
otherwise
\\<>
endcase
if i < fcount()
\\,
endif
endfor
\
lnVal = abs(recno(lcView))/min(reccount(lcView), MAX_RECS) * 100
oProgress.Caption = lcProgCaption + str(lnVal, 3, 0) + "%"
oBar.Value = min(lnVal, 100)
endscan
set textmerge to
set textmerge off
return

Mark McCasland
Midlothian, TX USA