No question at this time
DBA Top 10
1 M. Cadot 9300
2 B. Vroman 7100
3 A. Kavsek 4600
4 P. Wisse 2400
5 J. Péran 1700
6 J. Schnackenberg 1400
7 T. Boles 800
8 D. Dave 400
9 S. Spb 300
9 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48613
Total active users1400
Act. users last 24h3
Act. users last hour0
Registered user hits last week107
Registered user hits last month432
Go up

sql loader failing with date field
Next thread: uploading files on my support
Prev thread: extract result using substr function

Message Score Author Date
Hi masters, i am facing one issue with sql load...... javed akhtar Jun 04, 2021, 03:46
Hi Javed, If you specify BADFILE you can see th...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse Jun 05, 2021, 13:06
Hello Javed, -A- you can test the following: pu...... Bruno Vroman Jun 05, 2021, 15:27
Hello Javed, having access to databases I've do...... Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts Bruno Vroman Jun 07, 2021, 12:06
thanks a lot to all masters for giving the time... javed akhtar Jun 18, 2021, 16:05

Follow up by mail Click here


Subject: sql loader failing with date field
Author: javed akhtar, India
Date: Jun 04, 2021, 03:46, 21 days ago
Os info: linux
Oracle info: 12c
Message: Hi masters,

i am facing one issue with sql loader. please find the background of this issue.

i m using the below query for creating the cvs.

select host_name||'~'||instance_name||'~'||version||'~'|| comments||'~'|| action||'~'|| Apply_date
from (
select host_name,instance_name,i.version, comments, action,
to_char(action_time,'DD-MON-YYYY') Apply_date
from v\$instance i,registry\$history r where (comments like '%OJVM PSU%' or comments like '%PSU%') and action !='ROLLBACK' order by action_time desc)
where ROWNUM <3 order by instance_name;


after query executing i am getting below mentioned output.

CHICOTSTDB1~SCPOTST~11.2.0.4.0~PSU 11.2.0.4.180116~APPLY~25-FEB-2018
CHICOTSTDB1~SCPOTST~11.2.0.4.0~PSU 11.2.0.4.180717~APPLY~20-AUG-2018

CHICODEVDB1~SCPOTST~11.2.0.4.0~PSU 11.2.0.4.180717~APPLY~20-AUG-2018

CHICODEVDB1~SCPOTST~11.2.0.4.0~OJVM PSU post-install~APPLY~20-AUG-2018

i am using below mentioned control file for loading the data.

load data
infile '/backups/patch_script/Patch_details_11g.txt'
into table host_db_details_patch append
fields terminated by '~'
TRAILING NULLCOLS
( server_name ,
db_name char ,
version ,
patch_details ,
status ,
apply_date date 'DD-MON-YYYY'
)

while i am loading the data from cvs file to table i am getting below mention err.

Record 50: Rejected - Error on table HOST_DB_DETAILS_PATCH, column APPLY_DATE.
Field in data file exceeds maximum length


i remove apply_date from control file it is loading perfactly.

can anyone please help.

thanks in advance.

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sql loader failing with date field
Author: Philip Wisse, Netherlands
Date: Jun 05, 2021, 13:06, 19 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi Javed,

If you specify BADFILE you can see the rejected records, this usually helps.

Regards, Philip
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sql loader failing with date field
Author: Bruno Vroman, Belgium
Date: Jun 05, 2021, 15:27, 19 days ago
Message: Hello Javed,

-A- you can test the following: put the date as "before last" column instead of last one and see if things are ok or if Oracle complains now about the new "last column". Maybe you can also add a '~' at the end of the line.
I've not used SQL*Loader for many years and have no test environment but you should easily find working examples in the documentation or on the web.

-B1- something else: maybe your final "where rownum < 3" is only for a test, but note that things like
  WHERE rownum < 3 ORDER BY this_or_that
doesn't work (more precisely: "probably doesn't work like you expect".
Example: if a result set has values 1, 2, 3, ..., 100 and if when you do SELECT x FROM result_set WHERE ROWNUM < 3 ORDER BY x; you expect to see "1 and 2", you will have a bad surprise. In fact you can have any couple of values, simply ordered "first_one,second_one" like "17,88"
-B2- What's more your select has 2 "order by":
  SELECT ... FROM ( SELECT ... ORDER BY action_time ) ... ORDER BY instance_name
the syntax is not forbidden but once again what will be the result? Clarify what you want exactly (I cannot guess) and implement it in a safer way, maybe
  SELECT .. FROM ( SELECT ... ORDER BY instance_name, action_time ) WHERE ROWNUM < nnn

-C- to give a last comment: 11.2.0.4? I advise you to upgrade to 19c if you expect some support from Oracle... Imagine that you experience a sever problem in PROD and open a Service Request... Will you be happy with the answer "your version is not supported anymore, upgrade to a supported version and come back"?

Best regards,

Bruno Vroman
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sql loader failing with date field
Author: Bruno Vroman, Belgium
Date: Jun 07, 2021, 12:06, 17 days ago
Score:   Score: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 PtsScore: 400 Pts
Message: Hello Javed,

having access to databases I've done a small test (Oracle 12.1) with data similar to what you've provided (just a bit simplified).
Successful...

-A- create t table
CREATE TABLE uuu.load_tst( d VARCHAR2(10 CHAR), v VARCHAR2(15 CHAR), s VARCHAR2(10 CHAR), dt DATE );
-B- load.txt
SCPOTST~11.2.0.4.0~APPLY~25-FEB-2018

SCPOTST~11.2.0.4.0~APPLY~20-AUG-2018
SCPOTST~11.2.0.4.0~APPLY~20-AUG-2018
SCPOTST~11.2.0.4.0~APPLY~20-AUG-2018
-C- command file
load data
infile '/tmp/load_tst.txt'

into table load_tst append
fields terminated by '~'
TRAILING NULLCOLS
( d ,v, s, dt date 'DD-MON-YYYY')
-D- sqlloader session
$ sqlldr uuu@bbb control=ld.cmd

Password: ****
SQL*Loader: Release 12.1.0.2.0 - Production on Mon Jun 7 11:54:29 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Conventional
Commit point reached - logical record count 4
Table LOAD_TST:
4 Rows successfully loaded.
Check the log file:
ld.log
for more information about the load.

$ cat ld.log
SQL*Loader: Release 12.1.0.2.0 - Production on Mon Jun 7 11:54:31 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Control File: ld.cmd
Data File: /tmp/load_tst.txt
Bad File: load_tst.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table LOAD_TST, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------- ---------- ----- ---- ---- ---------------------
D FIRST * ~ CHARACTER
V NEXT * ~ CHARACTER
S NEXT * ~ CHARACTER
DT NEXT * ~ DATE DD-MON-YYYY

Table LOAD_TST:
4 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array: 66048 bytes(64 rows)
Read buffer bytes: 1048576

Total logical records skipped: 0
Total logical records read: 4
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Mon Jun 07 11:54:31 2021
Run ended on Mon Jun 07 11:54:35 2021

Elapsed time was: 00:00:03.81
CPU time was: 00:00:00.03
About <<Record 50: Rejected - Error on table HOST_DB_DETAILS_PATCH, column APPLY_DATE.
Field in data file exceeds maximum length >>, what about having a look at the row 50?

HTH,

Bruno
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: sql loader failing with date field
Author: javed akhtar, India
Date: Jun 18, 2021, 16:05, 6 days ago
Message: thanks a lot to all masters for giving the time
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here