OraFAQ Forum: Server Utilities » Max Primary Key Value for Oracle 10g

Home » RDBMS Server » Server Utilities » Max Primary Key Value for Oracle 10g (Oracle 10g)

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

Max Primary Key Value for Oracle 10g [message #335795]

Wed, 23 July 2008 15:56

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.

Report message to a moderator

Re: Max Primary Key Value for Oracle 10g [message #335802 is a reply to message #335795]

Wed, 23 July 2008 16:17

BlackSwan
Messages: 26766
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?

Report message to a moderator

Re: Max Primary Key Value for Oracle 10g [message #335809 is a reply to message #335802]

Wed, 23 July 2008 16:50

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.

Report message to a moderator

Re: Max Primary Key Value for Oracle 10g [message #335812 is a reply to message #335795]

Wed, 23 July 2008 17:04

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Max Primary Key Value for Oracle 10g [message #335813 is a reply to message #335809]

Wed, 23 July 2008 17:07

Barbara Boehmer
Messages: 9092
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)

Report message to a moderator

Re: Max Primary Key Value for Oracle 10g [message #335817 is a reply to message #335813]

Wed, 23 July 2008 18:10

zenp25
Messages: 5
Registered: July 2008

Junior Member

Thanks for the help!!

Report message to a moderator

Re: Max Primary Key Value for Oracle 10g [message #335818 is a reply to message #335812]

Wed, 23 July 2008 18:13

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

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

Michel Cadot
Messages: 68649
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

Report message to a moderator

Re: Max Primary Key Value for Oracle 10g [message #336955 is a reply to message #335795]

Tue, 29 July 2008 07:53

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.

Report message to a moderator

Re: Max Primary Key Value for Oracle 10g [message #337064 is a reply to message #336955]

Tue, 29 July 2008 16:03

Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA

Senior Member

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

Report message to a moderator

Previous Topic:	how to import external data from text file
Next Topic:	SQL Loader Issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 11 01:38:35 CDT 2024