Home » RDBMS Server » Server Utilities » SQL Loader - Datatype in LOG file Different from CTL File (Oracle 11g)
SQL Loader - Datatype in LOG file Different from CTL File [message #651384] Tue, 17 May 2016 02:10 Go to next message
s4.ora
Messages: 63
Registered: March 2010
Member
Hi All,

I have a Control file, in which a field in specified by INTEGER EXTERNAL, but in the Log File, it is showing as CHARACTER. PFB the Code Snippet from the .ctl and .log files..

Control File:

LOAD DATA
INFILE '/home/eagudan/DEV_TELIA/OUTPUT_DATA/site_uda_0002.sql'
BADFILE '/home/eagudan/DEV_TELIA/OUTPUT_DATA/site_uda_0002.bad'
DISCARDFILE '/home/eagudan/DEV_TELIA/OUTPUT_DATA/site_uda_0002.dsc'
APPEND
INTO TABLE SITE_ATTR_SETTINGS
FIELDS TERMINATED BY "|"
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(SITE_INST_ID INTEGER EXTERNAL,
VAL_ATTR_INST_ID INTEGER EXTERNAL,
ATTR_VALUE
)

Log File:

SQL*Loader: Release 11.2.0.4.0 - Production on Fri May 13 11:18:05 2016

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

Control File: SiteAttrSettings2.ctl
Data File: /home/eagudan/DEV_TELIA/OUTPUT_DATA/site_uda_0002.sql
Bad File: /home/eagudan/DEV_TELIA/OUTPUT_DATA/site_uda_0002.bad
Discard File: /home/eagudan/DEV_TELIA/OUTPUT_DATA/site_uda_0002.dsc
(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 500000 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional

Table SITE_ATTR_SETTINGS, 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
------------------------------ ---------- ----- ---- ---- ---------------------
SITE_INST_ID FIRST * | O(") CHARACTER
VAL_ATTR_INST_ID NEXT * | O(") CHARACTER
ATTR_VALUE NEXT * | O(") CHARACTER

My question is, that why there is a difference in Datatypes in .ctl file and .log file. ??

Below is the Data I am trying to load, which is specified in the load file.

740119|1350|TECHNICAL
740119|1351|BUILDING ENTRANCE
740119|1352|ENE to EGI Migration
740119|1406|GA (Geographical Address)
740119|1407|GA (Geographical Address)
740119|1408|3F9A3601-3B49-4C73-A70F-E8933AA394B7
740119|1566|EC974B43-11A8-4314-A83C-9373B78919A8
740119|1467|10913685
740119|1468|103
740119|1646|1501721768


Kindly help.

Re: SQL Loader - Datatype in LOG file Different from CTL File [message #651385 is a reply to message #651384] Tue, 17 May 2016 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, this is expected.
See the doc.
And also this quote from same page:

Quote:
The datatype specification of a field tells SQL*Loader how to interpret the data in the field. For example, a datatype of INTEGER specifies binary data, while INTEGER EXTERNAL specifies character data that represents a number.


Re: SQL Loader - Datatype in LOG file Different from CTL File [message #651386 is a reply to message #651385] Tue, 17 May 2016 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 65203
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Forgot the moderator bit:
Please read How to use [code] tags and make your code easier to read.

Re: SQL Loader - Datatype in LOG file Different from CTL File [message #651389 is a reply to message #651386] Tue, 17 May 2016 02:49 Go to previous message
s4.ora
Messages: 63
Registered: March 2010
Member
Thank you Michel for your help.. Sorry for the Format. I'll follow the Posing Standards for my future posts.
Previous Topic: SFTP using PL/SQL
Next Topic: IMPDP- ORA-31693: Table data object failed to load/unload and is being skipped
Goto Forum:
  


Current Time: Thu Nov 23 19:59:34 CST 2017

Total time taken to generate the page: 0.06579 seconds