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,FEILDS_VALS 1 TO N

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.

avi_nyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sathyagiriCommented:
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.
0
avi_nyAuthor Commented:
Hi ,
Can you tell me how can I create a control file dynamically with some example.
Thanks
0
awking00Information Technology SpecialistCommented:
avi_ny,
I think I would try to do this in two steps. First load the flat file using sqlloader into a staging table with name, sec_type, fields_num, and a column to store all of the remaining data.
For example,
CREATE TABLE stgtable
(name      varchar2(8)
,sec_type  varchar2(20)
,field_num number
,fields    varchar2(4000));

Run sqlloader
LOAD DATA
INFILE 'yourflatfile'
INSERT INTO TABLE stgtable
(name      CHAR TERMINATED BY '|'
,sec_type  CHAR TERMINATED BY '|'
,field_num CHAR TERMINATED BY '|'
           "TO_NUMBER(:field_num)"
,fields    CHAR TERMINATED BY X'0A'
)

Note - The hexadecimal notation X'OA' is for fields terminated by a linefeed. You may have to experiment using X'OD' which represents a carriage return. I'm sorry I'm not able to run sqlloader for some reason so I couldn't test it.

Secondly, create a procedure to dynamically insert into your target table from the staging table and execute it. Assuming your target table looks like the following:
Desc target
NAME                                               VARCHAR2(8)
SEC_TYPE                                           VARCHAR2(20)
FIELD_NUM                                          NUMBER
DEL_SIZE                                           NUMBER
MAT_DT                                             DATE
START_DT                                           DATE

The procedure would look like this -
CREATE OR REPLACE PROCEDURE load_target IS
sql_stmt     VARCHAR2(4000);
val_str      VARCHAR2(4000);
v_num_fields PLS_INTEGER;
v_col_st     PLS_INTEGER;
v_col_end    PLS_INTEGER;
v_val_st     PLS_INTEGER;
v_val_end    PLS_INTEGER;
len_col      PLS_INTEGER;
len_val      PLS_INTEGER;
v_col        VARCHAR2(4000);
v_val        VARCHAR2(4000);
j            PLS_INTEGER := 0;
k            PLS_INTEGER := 0;
BEGIN
sql_stmt := 'INSERT INTO TARGET (name,sec_type,field_num,';
FOR n IN
(SELECT name, sec_type, field_num, fields
 FROM stgtable)
LOOP
v_col_st := 1;
val_str  := ' VALUES('||chr(39)||n.name||chr(39)||','||chr(39)||n.sec_type||chr(39)||','||chr(39)||n.field_num||chr(39)||',';
 v_num_fields := n.field_num;
 FOR i IN 1..V_num_fields
 LOOP
 j := k + 1;
 k := i * 2;
 v_col_end := instr(n.fields,'|',1,j) - 1;
 v_val_st := v_col_end + 2;
 v_val_end := instr(n.fields,'|',1,k) - 1;
 len_col := v_col_end - v_col_st + 1;
 len_val := v_val_end - v_val_st + 1;
 v_col := trim(substr(n.fields,v_col_st,len_col));
 v_val := trim(substr(n.fields,v_val_st,len_val));
 sql_stmt := sql_stmt||v_col||',';
 val_str  := val_str||chr(39)||v_val||chr(39)||',';
 v_col_st := v_val_end + 2;
 END LOOP;
sql_stmt := rtrim(sql_stmt,',')||')';
val_str := rtrim(val_str,',')||')';
sql_stmt := sql_stmt||val_str;
execute immediate sql_stmt;
val_str  := ' VALUES('||chr(39)||n.name||chr(39)||','||chr(39)||n.sec_type||chr(39)||','||chr(39)||n.field_num||chr(39)||',';
sql_stmt := 'INSERT INTO TARGET (name,sec_type,field_num,';
j := 0;
k := 0;
END LOOP;
END;
/
The following results are produced:
SQL> select * from stgtable;
NAME     SEC_TYPE              FIELD_NUM FIELDS
-------- -------------------- ----------     --------------------------------------------------------
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|

SQL> select * from target;
no rows selected

SQL> exec load_target
PL/SQL procedure successfully completed.

SQL> select * from target;
NAME     SEC_TYPE              FIELD_NUM   DEL_SIZE MAT_DT    START_DT
-------- -------------------- ---------- ---------- --------- ---------
CORP1    BANK                          3     324445 12-MAR-97 10-JAN-07
CORP2    BANK AMERICA                  2     324445 01-JAN-97
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.