| Control file help [message #574777] |
Tue, 15 January 2013 15:19  |
 |
ssraman
Messages: 7
Registered: January 2013
Location: usa
|
Junior Member |
|
|
Hi,
My Table structure
column1 varchar(10)
column2 Date
Column3 varcahr(2)
Column4 varcahr(2)
By Data file
asds 12/12/2001asas
textsd asds
asds 12/12/2001asas
ramkiy asds
I still want to insert row 2 and row 4 into table by defaulting the date. Can someone please give me how can I handle this in control file?
[mod-edit: code tags added by bb]
[Updated on: Tue, 15 January 2013 15:31] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Control file help [message #574785 is a reply to message #574782] |
Tue, 15 January 2013 19:44  |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your control file does not match your data file or your table structure. Based on your control file, apparently your data file is fixed format. If so, then the following is an example of what I think you are trying to do.
-- test.dat data file:
asds 12/12/2001asas
textsd asds
asds 12/12/2001asas
ramkiy asds
-- test.ctl control file:
load data
infile 'test.dat'
badfile 'test.bad'
discardfile 'test.dsc'
preserve blanks
into table HIERARCHY
(
column1 position(*) char(10)
, EFFECTIVE_DT position(11:20) NULLIF effective_dt=BLANKS "to_date(:EFFECTIVE_DT,'dd/mm/yyyy')"
, column3 position(*) char(2)
, column4 position(*) char(2)
)
-- table:
SCOTT@orcl_11gR2> create table hierarchy
2 (column1 varchar2(10),
3 effective_dt varchar2(20),
4 Column3 varchar2(2),
5 Column4 varchar2(2))
6 /
Table created.
-- load:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Jan 15 17:37:57 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
-- results:
SCOTT@orcl_11gR2> select * from hierarchy
2 /
COLUMN1 EFFECTIVE_DT CO CO
---------- -------------------- -- --
asds 12-DEC-01 as as
textsd as ds
asds 12-DEC-01 as as
ramkiy as ds
4 rows selected.
|
|
|
|
|
|
|
|
| Re: Control file help [message #574789 is a reply to message #574788] |
Tue, 15 January 2013 20:07 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When you post without using code tags, it destroys the original spacing, so we can't see what the spacing in your data actually is. The following is another example using a user-defined function.
-- test.dat data file:
asds 12/12/2001asas
textsd asds
asds 12/12/2001asas
ramkiy asds
-- test.ctl control file:
load data
infile 'test.dat'
badfile 'test.bad'
discardfile 'test.dsc'
preserve blanks
into table HIERARCHY
(
column1 position(*) char(10)
, EFFECTIVE_DT position(11:20) "my_to_date(:EFFECTIVE_DT)"
, column3 position(*) char(2)
, column4 position(*) char(2)
)
-- table:
SCOTT@orcl_11gR2> create table hierarchy
2 (column1 varchar2(10),
3 effective_dt varchar2(20),
4 Column3 varchar2(2),
5 Column4 varchar2(2))
6 /
Table created.
-- user-defined function:
SCOTT@orcl_11gR2> create or replace function my_to_date
2 (p_date in varchar2)
3 return date
4 as
5 e_invalid_date exception;
6 pragma exception_init (e_invalid_date, -1858);
7 v_date date;
8 begin
9 v_date := to_date (p_date, 'dd/mm/yyyy');
10 return v_date;
11 exception
12 when e_invalid_date then return null;
13 end my_to_date;
14 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
-- load:
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 11.2.0.1.0 - Production on Tue Jan 15 18:02:13 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
-- results;
SCOTT@orcl_11gR2> select * from hierarchy
2 /
COLUMN1 EFFECTIVE_DT CO CO
---------- -------------------- -- --
asds 12-DEC-01 as as
textsd
asds 12-DEC-01 as as
ramkiy
4 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Control file help [message #574902 is a reply to message #574795] |
Wed, 16 January 2013 11:52 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I hope you realize that if you had posted a clear problem like we keep asking you to, using code tags to preserve the spacing in your input file, with matching control file, and table description, and consistent desired output, you would have had a prompt solution early yesterday.
The following uses two control files and a user-defined function.
-- test.dat data file:
asds 12/12/2001asas
textsasds
asds 12/12/2001asas
ramkiasds
-- test1.ctl control file:
LOAD DATA
INFILE 'test.dat'
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'
PRESERVE BLANKS
INTO TABLE hierarchy
( column1 POSITION(*) CHAR(5)
, effective_dt POSITION(6:15) "TO_DATE (:effective_dt, 'DD/MM/YYYY')"
, column3 POSITION(*) CHAR(2)
, column4 POSITION(*) CHAR(2))
-- test2.ctl control file:
LOAD DATA
INFILE 'test.dat'
BADFILE 'test.bad'
DISCARDFILE 'test.dsc'
PRESERVE BLANKS
INTO TABLE hierarchy
APPEND
TRAILING NULLCOLS
( column1 POSITION(*) CHAR(5)
, column3 POSITION(*) CHAR(2)
, column4 POSITION(*) CHAR(2)
, effective_dt POSITION(10:19) "my_to_date (:effective_dt)")
-- table:
SCOTT@orcl_11gR2> CREATE TABLE hierarchy
2 ( column1 VARCHAR2 ( 5)
3 , effective_dt VARCHAR2 (20)
4 , column3 VARCHAR2 ( 2)
5 , column4 VARCHAR2 ( 2))
6 /
Table created.
-- user-defined function:
SCOTT@orcl_11gR2> create or replace function my_to_date
2 (p_date in varchar2)
3 return date
4 as
5 v_date date;
6 begin
7 if p_date is null then
8 return to_date ('01-JAN-1999', 'DD-MON-YYYY');
9 else
10 v_date := to_date (p_date, 'dd/mm/yyyy');
11 return v_date;
12 end if;
13 end my_to_date;
14 /
Function created.
SCOTT@orcl_11gR2> show errors
No errors.
-- loads:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test1.ctl LOG=test1.log
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Jan 16 09:46:08 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test2.ctl LOG=test2.log
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Jan 16 09:46:09 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
-- results:
SCOTT@orcl_11gR2> SELECT * FROM hierarchy
2 /
COLUM EFFECTIVE_DT CO CO
----- -------------------- -- --
asds 12-DEC-01 as as
asds 12-DEC-01 as as
texts 01-JAN-99 as ds
ramki 01-JAN-99 as ds
4 rows selected.
[Updated on: Wed, 16 January 2013 11:56] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
