Server Utilities :: How To Write Control File To Load Data Into Revenue Table
Aug 16, 2011
I have a table revenue
create table revenue
(
person varchar2(23),
month varchar2(3),
rev_amt number
)
and i have data in a file like below
Person Jan Feb Mar Apr Mai Jun Jul Aug Sep Oct Nov Dez
--------------------------------------------------------
Schnyder,345,223,122,345,324,244,123,123,345,121,345,197
Weber,234,234,123,457,456,287,234,123,678,656,341,567
Keller,596,276,347,134,743,545,216,456,124,753,346,456
Meyer,987,345,645,567,834,567,789,234,678,973,456,125
Holzer,509,154,876,347,146,788,174,986,568,246,324,987
Müller,456,125,678,235,878,237,567,237,788,237,324,778
Binggeli,487,347,458,347,235,864,689,235,764,964,624,347
Stoller,596,237,976,876,346,567,126,879,125,568,124,753
Marty,094,234,235,763,054,567,237,457,325,753,577,346
Studer,784,567,235,753,124,575,864,235,753,864,634,678
i want to load it into the table in the following way.
Person Month Revenue
-------------------------
Schnyder Jan 345
Schnyder Feb 223
Schnyder Mar 122
Schnyder Apr 345
Schnyder Mai 324
Schnyder Jun 244
Schnyder Jul 123
Schnyder Aug 123
Schnyder Sep 345
Schnyder Oct 121
Schnyder Nov 345
Schnyder Dez 197
........ ... ...
How to write control file to load this data into the above revenue table.
View 2 Replies
Apr 8, 2010
I am using perl script to dynamically generate the control file.If I have data in the control file as well as in the datafile, how would i write the control file in that case. Is the below one correct?
load data
INFILE '*'
INFILE '/export/home/test/test.csv'
INSERT INTO TABLE EMP fields
terminated by "," optionally enclosed by '"'
trailing nullcols
( empno, empname, sal, deptno )
[code]....
Is there any way that if my control file contains half of the data and my data file contains the other half of the data can i club this data into a logical record in the control file to populate the DB?
My exact 2nd requirement is, my DB contains 5 cols and for 1 col the data is common(countryName) which i have to pass to the control file dynamically and the .csv file contains the data for the other four cols. How could i combine these in the ctrl file and populate the DB?
so if the DB contains CountryName, empid, ename, sal and dept..I will get the CountryName to the ctrl file and csv contains the data for empid, ename, sal and dept. How would i combine these data into a logical record and populate the DB?
View 12 Replies
View Related
Oct 26, 2012
I am having query regarding sql loader. my data file is comm(,) seperated and I want to load the whole file in oracle table 'bill_temp' except 1st column data of data file.
e.g.
File name: bill_file.dat
fields seperated by comma ','
values are like
emp_id,emp_name,emp_sal,join_date
oracle table bill_temp having the below column:
emp_name,emp_sal,join_date
Here I want load the emp_name,emp_sal and join_date into oracle table bill_temp.
emp_id should not get loaded into table.
Is there any way to skip the loading of particular column data from data file into table?
View 12 Replies
View Related
May 31, 2010
what i miss to load date and time from text file to oracle table through sqlloader
this is my data in this path (c:externalmy_data.txt)
7369,SMITH,17-NOV-81,09:14:04,CLERK,20
7499,ALLEN,01-MAY-81,17:06:08,SALESMAN,30
7521,WARD,09-JUN-81,17:06:30,SALESMAN,30
7566,JONES,02-APR-81,09:24:10,MANAGER,20
7654,MARTIN,28-SEP-81,17:24:10,SALESMAN,30
my table in database emp2
create table emp2 (empno number,
ename varchar2(20),
hiredate date,
etime date,
ejob varchar2(20),
deptno number);
the control file code in this path (c:externalctrl.ctl)
load data
infile 'C:externalmy_data.txt'
into table emp2
fields terminated by ','
(empno, ename, hiredate, etime, ejob, deptno)
this is the error :
C:>sqlldr scott/tiger control=C:externalctrl.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Mon May 31 09:45:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 5
C:>
View 21 Replies
View Related
Mar 2, 2011
We have a database that is accessed by ArcSDE, a product to modify maps. It uses BLOBs to store those maps.
We ran a load on the server and the response time was slow. By running the following query:
select event, total_waits, time_waited, avg_ms, round(ratio_to_report(time_waited) over () * 100) percent
from (select substr(event, 1, 30) event, total_waits, time_waited, round(time_waited_micro / total_waits / 1000, 2) avg_ms
from v$system_event
where wait_class in ('System I/O') union
select 'CPU' event, NULL, value, NULL
from v$sysstat
where statistic# = 12
order by 3 desc)
where rownum <= 10;
I get
EVENT TOTAL_WAITS TIME_WAITED AVG_MS PERCENT
--------------------------- -------------- ------------- -------- ---------
control file parallel write 127187 6354909 499.65 70
CPU 988274 11
db file parallel write 20461 886442 433.23 10
log file parallel write 14987 870672 580.95 10
log archive I/O 1557 18094 116.21 0
control file single write 149 10590 710.71 0
control file sequential read 136502 5219 .38 0
log file single write 56 2511 448.41 0
log file sequential read 489 492 10.05 0
BUG: 733426 says to change the event="10359 trace name context forever, level 1"
View 2 Replies
View Related
Jun 13, 2012
I am loading data using sqlldr command in UNIX to an oracle table and want to concatenate timestamp to a file name in the "create_file_name" column in the code below.
I have the below code within the control file..
LOAD DATA
TRUNCATE
INTO TABLE TABLEA
TRAILING NULLCOLS
(
file_type POSITION(1:5) CHAR,
business_date POSITION(16:23) DATE "YYYYMMDD",
create_file_name "FILE_NAME" EXPRESSION "SELECT TO_CHAR(CURRENT_TIMESTAMP(3), 'YYYYMMDDHH24MISS') FROM DUAL")
The load fails with SQL Loader error: "Expecting valid column specification, ",", ")", found keyword EXPRESSION found instead of column. How the timestamp to a filename can be appended?
View 5 Replies
View Related
