?
Solved

Sql loader

Posted on 2006-06-14
9
Medium Priority
?
778 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

719 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