No question at this time
The DBA-Village forum
as RSS feed
Site Statistics| Ever registered users | 48613 | | Total active users | 1400 | | Act. users last 24h | 3 | | Act. users last hour | 0 | | Registered user hits last week | 107 | | Registered user hits last month | 432 |
|
Go up
sql loader failing with date field
| 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...... |
  |
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...... |
|
Bruno Vroman |
Jun 07, 2021, 12:06 |
| thanks a lot to all masters for giving the time... |
|
javed akhtar |
Jun 18, 2021, 16:05 |
| 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: |
|
| 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: |
|
| 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 tableCREATE TABLE uuu.load_tst( d VARCHAR2(10 CHAR), v VARCHAR2(15 CHAR), s VARCHAR2(10 CHAR), dt DATE ); -B- load.txtSCPOTST~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 datainfile '/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
|