Solved

Sql loader

Posted on 2006-06-14
9
719 Views
Last Modified: 2012-06-27
table recordset_tmp


        RECORDCOUNT,
        PID,
        CREATIONDATE,
        CREATIONTIME,
        CONTENTS ,
        ORIENTATION ,
        SPLITFILE ,
        CLIENTID ,
        JOBID ,
        MODULEID ,
        SEQNUMBER,
        TIMEKEY ,
        LASTRUNDATE,
        LASTRUNTIME,
        RUNDATE ,
        RUNTIME ,
        DATABANK,
        FREQUENCY,
        STARTINGPERIOD,
        ENDINGPERIOD ,
        EMAILVERIFYADDRESS


------------------
Table series_data
HEADERID      
SERIESNAME      
DESCR      
STATUS      
UPDATAEDATE      
UPDATETIME      
LONGNAME      
DOC1      
DOC2      
DOC3      
DOC4      
---------------
table  recordset
        headerid
        RECORDCOUNT,
        PID,
        CREATIONDATE,
        CREATIONTIME,
        CONTENTS ,
        ORIENTATION ,
        SPLITFILE ,
        CLIENTID ,
        JOBID ,
        MODULEID ,
        SEQNUMBER,
        TIMEKEY ,
        LASTRUNDATE,
        LASTRUNTIME,
        RUNDATE ,
        RUNTIME ,
        DATABANK,
        FREQUENCY,
        STARTINGPERIOD,
        ENDINGPERIOD ,
        EMAILVERIFYADDRESS
        jobloadstas
       
---------------------------
data file(CSV)file
---------------------
"RECORDCOUNT",000000726
"PID",8066
"CREATIONDATE",06/08/2006
"CREATIONTIME",18:07:44
"CONTENTS","HISTORY"
"ORIENTATION","ACROSS"
"CLIENTID","SMA"
"JOBID","ALL1U106"
"SEQNUMBER","S001"
"TIMEKEY",060608180150
"DATAFIRM","DOOM"
"FREQUENCY","MONTHLY"
"EMAILVERIFYADDRESS","eever@yahoo.com"

"SERIESNAME","ATTRIBUTE","VALUE"
"BUCK","ACTION","INSERTOVERWRITE"
"BUCK","DECS",1
"BUCK","STATUS",0
"BUCK","UPDATEDATE",06/02/2006
"BUCK","UPDATETIME",08:38:39
"BUCK","LONGNAME"," CIVILIAN "
"BUCK","DOC1","RATE - CIVILIAN "
"BUCK","DOC2","PERCENT,ADJUSTED"
"BUCK","DOC3","statS"
"BUCK","DOC4","SEHOLD DATA, SITUATION'"
"BUCK",1/31/1948,3.4
"BUCK",2/29/1948,3.8
"BUCK",3/31/1948,4
"BUCK",4/30/1948,3.9
"BUCK",5/31/1948,3.5
"BUCK",6/30/1948,3.6
"BUCK",7/31/1948,3.6
"BUCK",8/31/1948,3.9
"BUCK",9/30/1948,3.8
"BUCK",10/31/1948,3.7


I want to load starting 13 lines into table RECORDSET.
These 13 rows to be loaded in RECORDSET_TMP also  but RECORDSET_TMP will have some other values like headerid and jobloadstas.During load headerid should generate automatically simple like 1,2,3.... and jobloadstas  will have default value 'N'.
rest of the data after 13 lines is to be loaded in to table SERIES_DATA.
Please tell me how can i do this.using SQL loader.
I am able to load data in to series_data but having truble to load starting 13 loads.
Please it is very urgent.reply ASAP

0
Comment
Question by:avi_ny
  • 5
  • 2
9 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16908974
Is there only one input data file? What is the record seperator and what is field terminator?

Is columns names are also hard coded within the datafile. If so then it is required to use some programming language like PRO*C, JAVA, or PL?SQL.
0
 

Author Comment

by:avi_ny
ID: 16910017
Commas (,) separate the various fields in a record and strings are enclosed in double quotes ("). A Null Value (,,) or string ("") is used to indicate meaningless, missing, or unavailable data.
Records vary in length and use a <NL> or <CR><LF> as a line delimiters. Control Record’s contains two fields, a  Attribute, and a Value; Data Record’s contains three, a Series Name, Attribute, and Value.
A single blank line divides the file in half and separates the Control and Data sections from each other.

There are 4 csv files i have and all have same format data.But i will get more data files in future(regular basis) and i have to put this sql loader job in crontab.
loding data for series_data i think is simple i can skip 14 records and load(correct me if i am wrong)
*but starting 13 rows are diffrent????????
*second i dont  know how to stop sqlloader after 13 rows?
*third how to insert default values for feilds when other feilds values are generated by loader from data files.?

I have to do it with sql loader.
pls respond ASAP it is urgent.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16910082
It will not be possible to accomplish everything using sqlloader. Create a temporary staging table. Load data into this staging table with sequential id. To update the id use the trigger and sequence.

create table stg_temp(
id number,
attr varchar2(100),
val varchar2(100));

create sequence stg_temp_seq stert with 1 increment by 1;

create or replace trigger stg_temp_trg before insert on stg_temp for each row
BEGIN
select stg_temp_seq.nextval INTO :new.id from dual;
END;
/

The control file is

LOAD DATA
infile file1.dat
APPEND INTO stg_temp
fields terminated by ','
(attr char,
var char)


0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 16

Expert Comment

by:MohanKNair
ID: 16910087
Use PL/SQL to read data from staging table and insert into normal tables.
0
 

Author Comment

by:avi_ny
ID: 16911143
Hi,
SERIES_data and recordset  are staging tables.
Actual tables are seperate.You are right i am going to load data in to actula table from these staging tables by using PL/SQL. I have issue only to load data in staging tables.
How can i load the data given in this format
"RECORDCOUNT",000000726
"PID",8066
"CREATIONDATE",06/08/2006
"CREATIONTIME",18:07:44
"CONTENTS","HISTORY"
"ORIENTATION","ACROSS"
"CLIENTID","SMA"
"JOBID","ALL1U106"
"SEQNUMBER","S001"
"TIMEKEY",060608180150
"DATAFIRM","DOOM"
"FREQUENCY","MONTHLY"
"EMAILVERIFYADDRESS","eever@yahoo.com"


in to a table like
table  recordset
        headerid
        RECORDCOUNT,
        PID,
        CREATIONDATE,
        CREATIONTIME,
        CONTENTS ,
        ORIENTATION ,
        SPLITFILE ,
        CLIENTID ,
        JOBID ,
        MODULEID ,
        SEQNUMBER,
        TIMEKEY ,
        LASTRUNDATE,
        LASTRUNTIME,
        RUNDATE ,
        RUNTIME ,
        DATABANK,
        FREQUENCY,
        STARTINGPERIOD,
        ENDINGPERIOD ,
        EMAILVERIFYADDRESS
        jobloadstas
       

thanks for sequnece and all i got that.
If you tell some ctl file for this type of load.
Thanks
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 16911291
From the staging table a group of records form a record in actual table.  A sequence id is required here. Otherwise when a query like "select * from stage_tab" is issued then the order of fetch depends on the way in which the data is stored in blocks.  The id needs to be populated at the time of loading only so that the order in which the data is found in CSV file is also maintained in the staging table.
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 500 total points
ID: 16911419
Load data into staging table using sqlloader. The id has to be updated so that related records get same value.

update stage_tab set attr=ltrim(rtrim(upper(attr),'"'), '"'), val=ltrim(rtrim(upper(val),'"'), '"');

select         id,
         max(decode(val, 'RECORDCOUNT', attr,null) RECORDCOUNT,
         max(decode(val, 'CREATIONDATE', attr,null) CREATIONDATE,
         max(decode(val, 'CONTENTS', attr,null) CONTENTS,
         max(decode(val, 'ORIENTATION', attr,null) ORIENTATION,
         max(decode(val, 'CLIENTID', attr,null) CLIENTID,
         max(decode(val, 'JOBID', attr,null) JOBID,
         max(decode(val, 'SEQNUMBER', attr,null) SEQNUMBER,
         max(decode(val, 'TIMEKEY', attr,null) TIMEKEY,
         max(decode(val, 'DATAFIRM', attr,null) DATAFIRM,
         max(decode(val, 'FREQUENCY', attr,null) FREQUENCY,
         max(decode(val, 'EMAILVERIFYADDRESS', attr,null) EMAILVERIFYADDRESS
from stag_tab
group by id;
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

17 Experts available now in Live!

Get 1:1 Help Now