Quote:
Originally Posted by madhunk
Thank you tmarikle...
I did not quite understand what exactly you meant by it...
Yes...It is a tab delimited file. But the problem is that if there are no values in the rest of the columns, they are not filled with spaces or tabs...
Row1: A B C D E F G H
Row2: A B C D
Row3: A B
I am absolutely not sure how I can do this...
Both sqlldr and external tables can process data in the format that you have described. I'll edit this post with some examples shortly.
Additional info:
Here is how you can define an external table using another table and a sqlldr control file to start with.
1) Create your table:
CREATE TABLE my_external_table (
my_key_no VARCHAR2(45)
,load_date DATE
,col_a VARCHAR2(30)
,col_b VARCHAR2(30)
,col_c VARCHAR2(30)
,col_d VARCHAR2(30)
,col_e VARCHAR2(30)
,col_f VARCHAR2(30)
)
2) Create a control file for sqlldr that allows for "missing" columns as per your requirement.
LOAD DATA
INTO TABLE "MY_EXTERNAL_TABLE"
APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' AND '"'
TRAILING NULLCOLS
(
my_key_no "SUBSTRB(:my_key_no, 1, 45)"
,load_date "TO_DATE(:load_date, 'YYYYMMDD')"
,col_a "SUBSTRB(:col_a , 1, 30)"
,col_b "SUBSTRB(:col_a , 1, 30)"
,col_c "SUBSTRB(:col_a , 1, 30)"
,col_d "SUBSTRB(:col_a , 1, 30)"
,col_e "SUBSTRB(:col_a , 1, 30)"
,col_f "SUBSTRB(:col_a , 1, 30)"
)
3) Use sqlldr to define a comperable external table definition.
sqlldr user/pwd test.ctl external_table=generate_only
4) The resulting log file contains the external table DDL and examples on how to use it.
CREATE TABLE "SYS_SQLLDR_X_EXT_MY_EXTERNAL_T"
(
"MY_KEY_NO" VARCHAR(255),
"LOAD_DATE" VARCHAR(255),
"COL_A" VARCHAR(255),
"COL_B" VARCHAR(255),
"COL_C" VARCHAR(255),
"COL_D" VARCHAR(255),
"COL_E" VARCHAR(255),
"COL_F" VARCHAR(255)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.bad'
LOGFILE 'test.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"MY_KEY_NO" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"LOAD_DATE" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_A" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_B" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_C" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_D" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_E" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """ ,
"COL_F" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' AND """
)
)
location
(
'test.dat'
)
)REJECT LIMIT UNLIMITED
At any rate, you can read up on external tables at asktom.oracle.com.