Solved

oracle sqlloader Dynamic columns in flat file

Posted on 2007-03-21
3
862 Views
Last Modified: 2013-12-19
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.

0
Comment
Question by:avi_ny
3 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 18769273
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
 

Author Comment

by:avi_ny
ID: 18771401
Hi ,
Can you tell me how can I create a control file dynamically with some example.
Thanks
0
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
ID: 18774501
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now