Home » RDBMS Server » Server Utilities » Max Primary Key Value for Oracle 10g (Oracle 10g)
Max Primary Key Value for Oracle 10g [message #335795] Wed, 23 July 2008 15:56 Go to next message
zenp25
Messages: 5
Registered: July 2008
Junior Member
I have made a table in Oracle 10g with a primary key that has a varchar2 vlaue up to 1000 characters. Is there a maximum value on the primary key? I get errors on some data I am dumping into the table and I thought it might be because the value is over 255 charactes.
Re: Max Primary Key Value for Oracle 10g [message #335802 is a reply to message #335795] Wed, 23 July 2008 16:17 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


My car has some error & now won't go.
Please tell me how to make my car go again.

Why did you decide we did not need to see exactly what you did & how Oracle responded?

Why do you expect answers when we don't know what you actually did & saw?
Re: Max Primary Key Value for Oracle 10g [message #335809 is a reply to message #335802] Wed, 23 July 2008 16:50 Go to previous messageGo to next message
zenp25
Messages: 5
Registered: July 2008
Junior Member
Thanks for the input. I am using sqlldr to steam thousands of records with a long primary key. The primary key is a string of about 300 charaters. The logfile reads: "Field in data file exceeds maximum length" even though the column is set to varchar2(1000). I looked at the .bad file and the rejected rows for the column data do not exceed 1000 characters.

I was wondering if anyone knows what the max primary key value is in Oracle 10g.
Re: Max Primary Key Value for Oracle 10g [message #335812 is a reply to message #335795] Wed, 23 July 2008 17:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/limits001.htm

The whole Oracle Doc. set can be found at http://tahiti.oracle.com

When I have to decide which more accurately reports reality between Oracle & a newbie; I trust Oracle 100% of the time.

Essentially you are claiming that Oracle has erroneously decided some records are "bad", but you KNOW these records are OK.

You could take 1 of these sample "bad" records & construct a 1 line INSERT statement to see what gets reported by SQL*Plus.

You likely won't get any good answers as long as you describe what you think is happening;
as opposed to using CUT & PASTE so we might decide for ourselves what is & is not actually occurring.
Re: Max Primary Key Value for Oracle 10g [message #335813 is a reply to message #335809] Wed, 23 July 2008 17:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The exceeded value is a default SQL*Loader limit, not a primary key limit. If you do not specify a length for the field in your SQL*Loader control file, the default is 255. So, just use:

your_column_name char (1000)
Re: Max Primary Key Value for Oracle 10g [message #335817 is a reply to message #335813] Wed, 23 July 2008 18:10 Go to previous messageGo to next message
zenp25
Messages: 5
Registered: July 2008
Junior Member
Thanks for the help!!
Re: Max Primary Key Value for Oracle 10g [message #335818 is a reply to message #335812] Wed, 23 July 2008 18:13 Go to previous messageGo to next message
zenp25
Messages: 5
Registered: July 2008
Junior Member
That is a great idea, thank you! Setting the control file to column_name char(1000) solved the issue. Wow, thanks for the quick responses and helping a newbie.

[Updated on: Wed, 23 July 2008 18:25]

Report message to a moderator

Re: Max Primary Key Value for Oracle 10g [message #336925 is a reply to message #335795] Tue, 29 July 2008 06:47 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

From Documentation about CHAR in SQL*LOADER,
Quote:


If a length is not specified, it is derived from the POSITION specification.

If a length is specified, it overrides the length in the POSITION specification.

If no length is given and there is no POSITION specification, CHAR data is assumed to have a length of 1, unless the field is delimited:

For a delimited CHAR field, if a length is specified, that length is used as a maximum.

For a delimited CHAR field for which no length is specified, the default is 255 bytes.

For a delimited CHAR field that is greater than 255 bytes, you must specify a maximum length. Otherwise you will receive an error stating that the field in the datafile exceeds maximum length.

[Updated on: Tue, 29 July 2008 06:48]

Report message to a moderator

Re: Max Primary Key Value for Oracle 10g [message #336952 is a reply to message #336925] Tue, 29 July 2008 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Arju,
Always post the link to the documentation not just a quote, above all when the whole post is just a quote.

Regards
Michel
Re: Max Primary Key Value for Oracle 10g [message #336955 is a reply to message #335795] Tue, 29 July 2008 07:53 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Yes, I though about it. But link was not found just of the topic CHAR. There was a link of details about datatype. That's why to focus only CHAR datatype I posted text.
Re: Max Primary Key Value for Oracle 10g [message #337064 is a reply to message #336955] Tue, 29 July 2008 16:03 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_field_list.htm#sthref1008
Previous Topic: how to import external data from text file
Next Topic: SQL Loader Issue
Goto Forum:
  


Current Time: Sun Dec 11 04:24:29 CST 2016

Total time taken to generate the page: 0.06589 seconds