Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 783
  • Last Modified:

Sql loader

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
avi_ny
Asked:
avi_ny
  • 5
  • 2
1 Solution
 
MohanKNairCommented:
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
 
avi_nyAuthor Commented:
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
 
MohanKNairCommented:
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
Independent Software Vendors: 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!

 
MohanKNairCommented:
Use PL/SQL to read data from staging table and insert into normal tables.
0
 
avi_nyAuthor Commented:
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
 
MohanKNairCommented:
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
 
MohanKNairCommented:
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

Industry Leaders: 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!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now