Home » RDBMS Server » Server Utilities » Control file help (oracle 10g)
Control file help [message #574777] Tue, 15 January 2013 15:19 Go to next message
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 #574778 is a reply to message #574777] Tue, 15 January 2013 15:31 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
post working control file that loads the data without any default data

what about having table column have default SYSDATE value?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Control file help [message #574779 is a reply to message #574778] Tue, 15 January 2013 15:39 Go to previous messageGo to next message
ssraman
Messages: 7
Registered: January 2013
Location: usa
Junior Member
My doubt here is how the control file can identify the missing columns. Data is missing in row 2 and 4. Also no space given for date in those 2 rows.
Re: Control file help [message #574781 is a reply to message #574779] Tue, 15 January 2013 15:56 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7975
Registered: November 2002
Location: California, USA
Senior Member
Please post your control file that loads your 1st and 3rd rows.
Re: Control file help [message #574782 is a reply to message #574781] Tue, 15 January 2013 16:03 Go to previous messageGo to next message
ssraman
Messages: 7
Registered: January 2013
Location: usa
Junior Member
infile '<IN_FILE>'
badfile '<BAD_FILE>'
discardfile '<DISCARD_FILE>'
preserve blanks
into table HIERARCHY
(

column1 position(*) char(10)
, EFFECTIVE_DT position(11:20) "to_date(:EFFECTIVE_DT,'yyyy-mm-dd')"
, column3 position(*) char(2)
, column4 position(*) char(2)
)
Re: Control file help [message #574785 is a reply to message #574782] Tue, 15 January 2013 19:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7975
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 #574787 is a reply to message #574785] Tue, 15 January 2013 19:51 Go to previous messageGo to next message
ssraman
Messages: 7
Registered: January 2013
Location: usa
Junior Member
Thanks for the reply. Issue in my case is the data file wont contain the space for date.
Re: Control file help [message #574788 is a reply to message #574787] Tue, 15 January 2013 19:52 Go to previous messageGo to next message
ssraman
Messages: 7
Registered: January 2013
Location: usa
Junior Member
I mean few rows in data file doesnt match the table structure. I cannot consider them as bad record. I need to put the default value for date and insert the record.
asds 12/12/2001asas
textsd asds
asds 12/12/2001asas
ramkiy asds

Thanks
Re: Control file help [message #574789 is a reply to message #574788] Tue, 15 January 2013 20:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7975
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 #574790 is a reply to message #574788] Tue, 15 January 2013 20:08 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
You have to choose between fixing/changing the input data file or not loading the bad records.
Re: Control file help [message #574791 is a reply to message #574790] Tue, 15 January 2013 20:23 Go to previous messageGo to next message
ssraman
Messages: 7
Registered: January 2013
Location: usa
Junior Member
My output need to be like below

COLUMN1 EFFECTIVE_DT CO CO
---------- -------------------- -- --
asds 12-DEC-01 as as
textsd 01-JAN-99
asds 12-DEC-01 as as
ramkiy 01-JAN-99

I want to default the date value.
Re: Control file help [message #574792 is a reply to message #574791] Tue, 15 January 2013 20:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7975
Registered: November 2002
Location: California, USA
Senior Member
Then just change the return value in the exception section of the user-defined function, as below.

create or replace function my_to_date
  (p_date in varchar2)
  return     date
as 
  e_invalid_date  exception;
  pragma exception_init (e_invalid_date, -1858);
  v_date          date;
begin
  v_date := to_date (p_date, 'dd/mm/yyyy');
  return v_date;
exception 
  when e_invalid_date then return to_date ('01-JAN-1999', 'DD-MON-YYYY');
end my_to_date;
/

Re: Control file help [message #574795 is a reply to message #574792] Tue, 15 January 2013 21:30 Go to previous messageGo to next message
ssraman
Messages: 7
Registered: January 2013
Location: usa
Junior Member
Thanks for leading me till this. But we are still missing the real issue.

table:
create table hierarchy
(column1 varchar2(5),
effective_dt varchar2(20),
Column3 varchar2(2),
Column4 varchar2(2))


input file:
asds 12/12/2001asas
textsasds
asds 12/12/2001asas
ramkiasds


output expected:

COLUMN1 EFFECTIVE_DT CO CO
---------- -------------------- -- --
asds 12-DEC-01 as as
text 01-JAN-99 as ds
asds 12-DEC-01 as as
ramkiy 01-JAN-99 as ds


Please let me know if we can handle this scenario
Re: Control file help [message #574813 is a reply to message #574795] Wed, 16 January 2013 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.

Regards
Michel
Re: Control file help [message #574902 is a reply to message #574795] Wed, 16 January 2013 11:52 Go to previous message
Barbara Boehmer
Messages: 7975
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

Previous Topic: Import to a lower version
Next Topic: migrate ful db
Goto Forum:
  


Current Time: Wed Sep 03 01:34:18 CDT 2014

Total time taken to generate the page: 0.15076 seconds