Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

oracle sqlloader Dynamic columns in flat file

Posted on 2007-03-21
3
Medium Priority
?
902 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 32

Accepted Solution

by:
awking00 earned 1500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

670 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