Home » RDBMS Server » Server Utilities » Varchar data type
Varchar data type [message #202127] Wed, 08 November 2006 05:02 Go to next message
dharmbir
Messages: 7
Registered: October 2006
Junior Member
Hi
I am facing one issue.
I have a table with varchar(10) data type.
I am trying to load data into it using SQL*Loader.
In the control file when I give Column name and datatype Varchar(10) it does not load the data and gives me error
Record 1: Rejected - Error on table SCOTT.VCHAR, column VCH.
Variable length field exceeds maximum length.

I am not able to fine solution fot it.

If same thing i do using char(10) it loads the data.

Thanks
Dharmbir
Re: Varchar data type [message #202141 is a reply to message #202127] Wed, 08 November 2006 06:24 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL Loader Data Types CHAR
DECIMAL EXTERNAL
INTEGER EXTERNAL 

Re: Varchar data type [message #202147 is a reply to message #202141] Wed, 08 November 2006 07:02 Go to previous messageGo to next message
dharmbir
Messages: 7
Registered: October 2006
Junior Member
How do I write it into Control file
Re: Varchar data type [message #202152 is a reply to message #202147] Wed, 08 November 2006 07:12 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

example.

column name datatype
like.

empname char,

hope this helps.
Re: Varchar data type [message #202154 is a reply to message #202152] Wed, 08 November 2006 07:16 Go to previous messageGo to next message
dharmbir
Messages: 7
Registered: October 2006
Junior Member
Actually what I am trying to ask is can't we use VARCHAR instead of char

example

Table
col1 Varchar(10)

so in control file

can't I write

col1 varchar(10)

why do I have to write

Col1 char(10)
Re: Varchar data type [message #202157 is a reply to message #202154] Wed, 08 November 2006 07:32 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

what error you got.
or can you post here ur data.

Re: Varchar data type [message #202276 is a reply to message #202157] Wed, 08 November 2006 22:28 Go to previous messageGo to next message
dharmbir
Messages: 7
Registered: October 2006
Junior Member
Error Message what I get in Log File.

SQL*Loader: Release 10.2.0.1.0 - Production on Thu Nov 9 09:54:02 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Control File: c:\cont.ctl
Data File: c:\data.dat
Bad File: c:\bad.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 SCOTT.LEN, 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
------------------------------ ---------- ----- ---- ---- ---------------------
LL FIRST 12 VARCHAR

Record 1: Rejected - Error on table SCOTT.LEN, column LL.
end of logical record found when reading length of varying length field
Record 2: Rejected - Error on table SCOTT.LEN, column LL.
end of logical record found when reading length of varying length field
Record 3: Rejected - Error on table SCOTT.LEN, column LL.
end of logical record found when reading length of varying length field
Record 4: Rejected - Error on table SCOTT.LEN, column LL.
end of logical record found when reading length of varying length field

Table SCOTT.LEN:
0 Rows successfully loaded.
4 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: 896 bytes(64 rows)
Read buffer bytes: 1048576

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

Run began on Thu Nov 09 09:54:02 2006
Run ended on Thu Nov 09 09:54:02 2006

Elapsed time was: 00:00:00.36
CPU time was: 00:00:00.05

Data File
AA
BB
CC
DD

Table
Create Table SCOTT.LEN (LL Varchar(10))

and Control file is
LOAD DATA
Append into table SCOTT.LEN
EVALUATE CHECK_CONSTRAINTS
REENABLE DISABLED_CONSTRAINTS
TRAILING NULLCOLS
(
LL Varchar(10)
)
Re: Varchar data type [message #202286 is a reply to message #202276] Thu, 09 November 2006 00:02 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

use char instead of varchar.



hope this helps.
Mohammad Taj.
Re: Varchar data type [message #202287 is a reply to message #202286] Thu, 09 November 2006 00:05 Go to previous messageGo to next message
dharmbir
Messages: 7
Registered: October 2006
Junior Member
Just curious, why can't we use Varchar. Is there any limitation.
If so where can I find documentation for it.
Re: Varchar data type [message #202289 is a reply to message #202287] Thu, 09 November 2006 00:14 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL*LOADER DATATYPES

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#sthref952

hope this helps
Mohammad Taj.
Re: Varchar data type [message #202290 is a reply to message #202289] Thu, 09 November 2006 00:25 Go to previous messageGo to next message
dharmbir
Messages: 7
Registered: October 2006
Junior Member
I will go through it.

thanks for you time.

Dharmbir
Re: Varchar data type [message #202291 is a reply to message #202290] Thu, 09 November 2006 00:30 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi,
You're welcome

Cool
Re: Varchar data type [message #202433 is a reply to message #202276] Thu, 09 November 2006 14:36 Go to previous message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
varchar might have worked if you didn't give it a length. I don't think I have ever seen a length associated with columns in SQL*Loader. Maybe I just can't remember properly, but for fixed length data, you give starting and ending postions, and for delimited data you just give datatype.
Previous Topic: UTl_File - Problem - Records written intermittently
Next Topic: DISABLE INDEXES SQL LOADER
Goto Forum:
  


Current Time: Wed Dec 07 12:59:46 CST 2016

Total time taken to generate the page: 0.05944 seconds