OraFAQ Forum: Server Utilities » SQL Loader - Datatype in LOG file Different from CTL File

Home » RDBMS Server » Server Utilities » SQL Loader - Datatype in LOG file Different from CTL File (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Loader - Datatype in LOG file Different from CTL File [message #651384]

Tue, 17 May 2016 02:10

s4.ora
Messages: 71
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.

Report message to a moderator

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

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

Report message to a moderator

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

s4.ora
Messages: 71
Registered: March 2010

Member

Thank you Michel for your help.. Sorry for the Format. I'll follow the Posing Standards for my future posts.

Report message to a moderator

Previous Topic:	Inputs to SQL Loader
Next Topic:	IMPDP- ORA-31693: Table data object failed to load/unload and is being skipped

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 17:05:55 CST 2025