Home » RDBMS Server » Server Utilities » sql loader (oracle 11.2 windows 7)
sql loader [message #594273] Tue, 27 August 2013 03:29 Go to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
Hello

I am using sql loader to upload data from text file. I use to insert dates from text file to oracle table by using control file and syntax is column_9 position(27:36) date "dd/mm/yyyy".

Now I want to add 5 days on date column so what is the syntax of adding date I am using this -----
column_8 position(16:25) date "dd/mm/yyyy column_8+5"
but it is wrong

Please send me the syntax

Report message to a moderator

Re: sql loader [message #594275 is a reply to message #594273] Tue, 27 August 2013 03:31 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Load values as they are. Then update that column:
update your_table set
  column_8 = column_8 + 5;

Report message to a moderator

Re: sql loader [message #594277 is a reply to message #594275] Tue, 27 August 2013 03:39 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
No
Because I have to insert daily new file

Report message to a moderator

Re: sql loader [message #594278 is a reply to message #594277] Tue, 27 August 2013 03:47 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
OK then, here you go.
SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 DATUM                                              DATE

SQL>

Control file:
load data
infile *
into table test
replace
fields terminated by ','
trailing nullcols
  (
   id,
   datum "to_date(:datum, 'dd/mm/yyyy') + 5"
  )

begindata
100,4/3/2013
101,10/8/2013
102,29/10/2013

Loading session & the result:
SQL> $sqlldr scott/tiger@ora10 control=test9.ctl log=test9.log

SQL*Loader: Release 11.2.0.2.0 - Production on Uto Kol 27 10:47:35 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

SQL> select * from test;

        ID DATUM
---------- ----------
       100 09.03.2013
       101 15.08.2013
       102 03.11.2013

SQL>

Report message to a moderator

Re: sql loader [message #594280 is a reply to message #594278] Tue, 27 August 2013 04:00 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
Please look into my code and error

control file:
COLUMN_8 16:25 10 DATE to_date(:column_8, 'dd/mm/yyyy') + 5

log file:
Record 1: Rejected - Error on table RAJEMITRA.LU824910915321210000001, column COLUMN_8.
ORA-00907: missing right parenthesis

text file:
LL 9 9 Z 22/08/2013

Please resolve

Report message to a moderator

Re: sql loader [message #594282 is a reply to message #594280] Tue, 27 August 2013 04:03 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If line you posted really is part of your control file, then - to me - it looks invalid.

Compare your control file with mine (line that loads date value), find the differences, fix what's wrong.

Report message to a moderator

Re: sql loader [message #594283 is a reply to message #594282] Tue, 27 August 2013 04:05 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
Thanks for prompt reply!

My control file starts with following lines:

load data
infile 'LU824910915321210000001.txt'
badfile 'LU824910915321210000001.bad'
append into table LU824910915321210000001

Report message to a moderator

Re: sql loader [message #594285 is a reply to message #594283] Tue, 27 August 2013 04:08 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's irrelevant. I said that you should compare this
COLUMN_8 16:25 10 DATE to_date(:column_8, 'dd/mm/yyyy') + 5

with
datum "to_date(:datum, 'dd/mm/yyyy') + 5"

Report message to a moderator

Re: sql loader [message #594286 is a reply to message #594283] Tue, 27 August 2013 04:09 Go to previous messageGo to next message
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
Senior Member
And those lines have nothing to do with COLUMN_8 so aren't helpful.
LF showed you what you need to do. If you think you've followed his instructions (check first) and it still doesn't work then post the full control file here in code tags.

Report message to a moderator

Re: sql loader [message #594288 is a reply to message #594285] Tue, 27 August 2013 04:15 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
If I use this code
column_8 "to_date(:column_8, 'dd/mm/yyyy') + 5" ,

Then where I have to give these line numbers (16:25)

Report message to a moderator

Re: sql loader [message #594289 is a reply to message #594285] Tue, 27 August 2013 04:16 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
Thank you so much

datum "to_date(:datum, 'dd/mm/yyyy') + 5"

It works...

Report message to a moderator

Re: sql loader [message #594290 is a reply to message #594289] Tue, 27 August 2013 04:17 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
But
In table column is blank and One more thing is neede that is position where I have to give

[Updated on: Tue, 27 August 2013 04:20]

Report message to a moderator

Re: sql loader [message #594293 is a reply to message #594290] Tue, 27 August 2013 04:19 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, post CREATE TABLE statement, complete control file and several lines taken from the input file so that we'd see what's going on.

Report message to a moderator

Re: sql loader [message #594296 is a reply to message #594293] Tue, 27 August 2013 04:24 Go to previous messageGo to next message
nehaverma
Messages: 80
Registered: January 2010
Location: JAIPUR
Member
My text data:
  LL  9    9 Z 22/08/2013|22/08/2013      99      99 LLLLLL                         LLLLLLL              LLLL LLLLLLLLLLL                           9 2013


My control file:
load data
infile 'LU824910915321210000001.txt'
badfile 'LU824910915321210000001.bad'
append into table  rajemitra.LU824910915321210000001
replace
fields terminated by ','
trailing nullcols
(column_1  position(3:4)  char,
column_2  position(6:7)  char,
column_3  position(9:12)  char,
column_4  position(14:14)  char,
column_5  position(148:149)  char,
column_6  position(151:154)  char,
column_7  Filler,
column_8 "to_date(:column_8, 'dd/mm/yyyy')+5" ,
column_9  position(27:36)  date  "dd/mm/yyyy",
column_10  position(38:44)  char,
column_11  position(46:52)  char,
column_12  position(52:82)  char,
column_13  position(85:104)  char,
column_14  position(106:125)  char,
column_15  Filler)


My table:
CREATE TABLE RAJEMITRA.LU824910915321210000001
(
  COLUMN_1   VARCHAR2(2 BYTE),
  COLUMN_2   NUMBER(2),
  COLUMN_3   NUMBER(4),
  COLUMN_4   VARCHAR2(1 BYTE),
  COLUMN_5   NUMBER(2),
  COLUMN_6   NUMBER(4),
  COLUMN_7   DATE,
  COLUMN_8   DATE,
  COLUMN_9   DATE,
  COLUMN_10  NUMBER(10),
  COLUMN_11  NUMBER(10),
  COLUMN_12  VARCHAR2(50 BYTE),
  COLUMN_13  VARCHAR2(30 BYTE),
  COLUMN_14  VARCHAR2(30 BYTE),
  COLUMN_15  VARCHAR2(30 BYTE)
)


[mod-edit: code tags added by bb]

[Updated on: Sat, 16 November 2013 19:18] by Moderator

Report message to a moderator

Re: sql loader [message #601133 is a reply to message #594296] Sat, 16 November 2013 19:28 Go to previous message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
column_8 position(16:25) "to_date(:column_8, 'dd/mm/yyyy')+5"

SCOTT@orcl12c> HOST TYPE LU824910915321210000001.txt
  LL  9    9 Z 22/08/2013|22/08/2013      99      99 LLLLLL                         LLLLLLL              LLLL LLLLLLLLLLL
                  9 2013

SCOTT@orcl12c> HOST TYPE test.ctl
load data
infile 'LU824910915321210000001.txt'
badfile 'LU824910915321210000001.bad'
append into table LU824910915321210000001
trailing nullcols
(column_1  position(3:4)  char,
column_2  position(6:7)  char,
column_3  position(9:12)  char,
column_4  position(14:14)  char,
column_5  position(148:149)  char,
column_6  position(151:154)  char,
column_7  Filler,
column_8 position(16:25) "to_date(:column_8, 'dd/mm/yyyy')+5" ,
column_9  position(27:36)  date  "dd/mm/yyyy",
column_10  position(38:44)  char,
column_11  position(46:52)  char,
column_12  position(52:82)  char,
column_13  position(85:104)  char,
column_14  position(106:125)  char,
column_15  Filler)

SCOTT@orcl12c> CREATE TABLE LU824910915321210000001
  2  (
  3    COLUMN_1   VARCHAR2(2 BYTE),
  4    COLUMN_2   NUMBER(2),
  5    COLUMN_3   NUMBER(4),
  6    COLUMN_4   VARCHAR2(1 BYTE),
  7    COLUMN_5   NUMBER(2),
  8    COLUMN_6   NUMBER(4),
  9    COLUMN_7   DATE,
 10    COLUMN_8   DATE,
 11    COLUMN_9   DATE,
 12    COLUMN_10  NUMBER(10),
 13    COLUMN_11  NUMBER(10),
 14    COLUMN_12  VARCHAR2(50 BYTE),
 15    COLUMN_13  VARCHAR2(30 BYTE),
 16    COLUMN_14  VARCHAR2(30 BYTE),
 17    COLUMN_15  VARCHAR2(30 BYTE)
 18  )
 19  /

Table created.

SCOTT@orcl12c> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log

SQL*Loader: Release 12.1.0.1.0 - Production on Sat Nov 16 17:26:08 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1

Table LU824910915321210000001:
  1 Row successfully loaded.

Check the log file:
  test.log
for more information about the load.

SCOTT@orcl12c> SELECT * FROM LU824910915321210000001
  2  /

CO   COLUMN_2   COLUMN_3 C   COLUMN_5   COLUMN_6 COLUMN_7        COLUMN_8
-- ---------- ---------- - ---------- ---------- --------------- ---------------
COLUMN_9         COLUMN_10  COLUMN_11
--------------- ---------- ----------
COLUMN_12
--------------------------------------------------
COLUMN_13                      COLUMN_14
------------------------------ ------------------------------
COLUMN_15
------------------------------
LL          9          9 Z          9       2013                 Tue 27-Aug-2013
Thu 22-Aug-2013         99         99
9 LLLLLL
LLLLLLL                        LLLL LLLLLLLLLLL



1 row selected.

Report message to a moderator

Previous Topic: Field in data file exceeds maximum length
Next Topic: Oracle 11.2.0.1 imp.exe Appcrash
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Feb 11 18:13:31 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.