-
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:
system@dev815nt.us> l
1 select column_name || ' ' || data_type || ','
2 from dba_tab_columns
3 where table_name = 'CUST'
4* order by column_id
system@dev815nt.us> /
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.
Jeff Hunter
-
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
<font face="courier">
#!/usr/bin/perl
$batch_count = 0;
$batch_size = 1000;
open(FLATFILE, "location/of/flatfile");
open(INSERTFILE, "insert.sql");
# read each line
while ($row = <FLATFILE>) {
# @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;
-
Or do it with Jeff's idea :)
I can always come up with a quick idea to get it done, but not always the easiest :)
-
I also thought of using dynamic sql but how do I specify the position though
ACCT_NO CHAR, to
ACCT_NO POSITION(01:04) CHAR EXTERNAL,
:-?
and ... sorry but I dunno perl :D
-
where do you have the positions defined? in a file? what does it look like?
-
the control file looks something like
LOAD DATA
INFILE 'c:\temp\loader.txt'
INTO TABLE EMP
( EMPNO POSITION(01:04) INTEGER EXTERNAL,
ENAME POSITION(06:15) CHAR,
JOB POSITION(17:25) CHAR,
MGR POSITION(27:30) INTEGER EXTERNAL,
SAL POSITION(32:39) DECIMAL EXTERNAL,
COMM POSITION(41:48) DECIMAL EXTERNAL,
DEPTNO POSITION(50:51) INTEGER EXTERNAL)
this is an example, the real table has 200 columns....
-
no no, obviously you have the specification of what fields are in what positions. do you have that written on a piece of paper? is it in a spreadsheet? is it in it's own file, or part of the flatfile?
-
if you generate the column list
don't forget to put the column of type long on the end. =)
-josh
-
I think I will have to write a pl/sql to do it, dba_tab_columns gives field length thankfully :D
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|