Solved

Sql loader

Posted on 2006-06-14
9
774 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

MongoDB Through a MySQL Lens

This article looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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…

623 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