avi_ny
asked on
oracle sqlloader Dynamic columns in flat file
Hi All,
I have a flat file like
--------------------------
NAME | SEC_TYP | FEILDS_NUM | FEILDS_VAL 1 TO N
--------------------------
CORP1| BANK | 3| DEL_SIZE|324445| MAT_DT| 12-MAR-97| START_DT|10-JAN-07|
CORP2| BANK AMERICA | 2| DEL_SIZE|324445| MAT_DT| 1-JAN-97|
COLUMNS ARE :NAME,SEC_TYP,FEILDS_NUM,F
TWO COLUMNS ARE OK (NAME,SEC_TYP)
NOW according to the value provided in column feilds_num the columns after it varies like
in record 1 FEILD_NUM=3 therefore there are three things(total 6 values) are defined .It means 3 attribute will be defined so del_size,mat_dt and start_dt is given.
But this feilds_num can be any thing so the number of columns in flat file will vary after this columns.
So my flat file is half static and half is dynamic depends upon feilds_num.
I hope I explained it clear.
Now my question is how can I load data in tables fromthis flat files using SQLLDR or ext tables in this situation.
Any help is apprecaited.
I have a flat file like
--------------------------
NAME | SEC_TYP | FEILDS_NUM | FEILDS_VAL 1 TO N
--------------------------
CORP1| BANK | 3| DEL_SIZE|324445| MAT_DT| 12-MAR-97| START_DT|10-JAN-07|
CORP2| BANK AMERICA | 2| DEL_SIZE|324445| MAT_DT| 1-JAN-97|
COLUMNS ARE :NAME,SEC_TYP,FEILDS_NUM,F
TWO COLUMNS ARE OK (NAME,SEC_TYP)
NOW according to the value provided in column feilds_num the columns after it varies like
in record 1 FEILD_NUM=3 therefore there are three things(total 6 values) are defined .It means 3 attribute will be defined so del_size,mat_dt and start_dt is given.
But this feilds_num can be any thing so the number of columns in flat file will vary after this columns.
So my flat file is half static and half is dynamic depends upon feilds_num.
I hope I explained it clear.
Now my question is how can I load data in tables fromthis flat files using SQLLDR or ext tables in this situation.
Any help is apprecaited.
sathyagiri
I am not sure if there's a easy way to do this with sql loader. However you could use UTL_FILE to read and create a dynamic insert statement in a stored proc to do this.
ASKER
Hi ,
Can you tell me how can I create a control file dynamically with some example.
Thanks
Can you tell me how can I create a control file dynamically with some example.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.