loading 200 column table data

Hi

I am trying to load data from a flat file into a 200 column table using sql loader, I am wondering if there is any easier way to write the control file? Or I have to type all 200 columns manually :o

[Edited by pando on 01-26-2001 at 01:26 PM]

How about a query from dba_tab_columns:
[email protected]> l
1 select column_name || ' ' || data_type || ','
2 from dba_tab_columns
3 where table_name = 'CUST'
4* order by column_id
[email protected]> /

COLUMN_NAME||''||DATA_TYPE||','
----------------------------------------------------------
ACCT_NO CHAR,
BANK_NO CHAR,
FEED VARCHAR2,
PROFIT_CENTER VARCHAR2,
ACCT_OFFICER CHAR,
NAME VARCHAR2,
ADDR VARCHAR2,
ICB_FLAG CHAR,
PRINT_SUPPRESS_FLAG CHAR,
COMA_STATEMENT_FLAG CHAR,
STOP_HOLD_FLAG VARCHAR2,
ACCT_STATUS CHAR,
ACCT_TYPE CHAR,
SEND_STMNT_TO_PARENT CHAR,
LAST_TXN_DATE DATE,
CREATE_TIME DATE,
DDA_INSERT_TIME DATE,
PENDING CHAR,
PARENT_ACCT_NO CHAR,
PARENT_BANK_NO CHAR,
PASSWORD VARCHAR2,

21 rows selected.

is it deleniated? a quick perl script could be something like this (say if it were comma deleniated). This will create a file turning every one of the rows in the flatfile into an insert statement so you don't have to do any control files.

Off the top of my head so test it and change it as you need to


#!/usr/bin/perl

$batch_count = 0;
$batch_size = 1000;

open(FLATFILE, "location/of/flatfile");
open(INSERTFILE, "insert.sql");
# read each line
while ($row = ) {
# @columns is an array, each value is a column
@columns = split /,/, $row;
# create the front of the insert statement
$insert = "insert into table_name values (";

# append each column data to the insert statement
foreach $column (@columns) {
$insert .= "$column,";
}
# remove the trailing comma
chop $insert;
# close the insert statement
$insert .= ");\n";

print INSERTFILE $insert;
if ($batch_count % $batch_size == 0) {
print INSERTFILE "commit;\n";
}
}
print INSERTFILE "commit;\n";

close INSERTFILE;
close FLATFILE;