Home » SQL & PL/SQL » SQL & PL/SQL » default(max) size of NUMBER datatype in oracle-10g
default(max) size of NUMBER datatype in oracle-10g [message #234201] Mon, 30 April 2007 07:53 Go to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Hi
what is the default (or) max size of NUMBER datatype in oracle-10g.

by
balamuralikrishnan.s
Re: default(max) size of NUMBER datatype in oracle-10g [message #234206 is a reply to message #234201] Mon, 30 April 2007 08:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
What are documentations for? Smile
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3810
Re: default(max) size of NUMBER datatype in oracle-10g [message #234228 is a reply to message #234206] Mon, 30 April 2007 09:05 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
I have created a table like
sql>create table vendor_master(vendor_id number);
This vendor_id column is auto incremented .Now my question is
upto what number vendor_id will increament ? (say 40 digits or 80 digits number)
Re: default(max) size of NUMBER datatype in oracle-10g [message #234239 is a reply to message #234228] Mon, 30 April 2007 09:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no auto-increment number in Oracle.

Regards
Michel
Re: default(max) size of NUMBER datatype in oracle-10g [message #234250 is a reply to message #234239] Mon, 30 April 2007 10:33 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Michel Cadot wrote on Mon, 30 April 2007 09:59

There is no auto-increment number in Oracle.

Regards
Michel


Identity fields are not part of Oracle, but can be emulated by using a sequence and a table trigger.
Re: default(max) size of NUMBER datatype in oracle-10g [message #234253 is a reply to message #234250] Mon, 30 April 2007 10:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You're right Bill but this has to be precise by the OP to answer his question.
Maybe it emulates this auto-numbering by another method.

Regards
Michel
Re: default(max) size of NUMBER datatype in oracle-10g [message #234342 is a reply to message #234250] Tue, 01 May 2007 00:18 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Hi Mr MichelCadot & MaheshRajendran.
There is auto increment in oracle ,I have done like this
sql>insert into vendor_master(vendor_id)values((SELECT nvl(max(vendor_id),0)+1 FROM vendor_master));

It is working(incrementing) ,Now my question is upto what number it will increment ?(say varchar2 datatype will accept 2000 bytes like that how many bytes the number datatype will accept ?)

by
balamuralikrishnan.s
Re: default(max) size of NUMBER datatype in oracle-10g [message #234343 is a reply to message #234342] Tue, 01 May 2007 00:24 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is NOT an auto-increment column.
The fact that you manually determine a value for it does not make it AUTO-increment!
number is default number(38), so this should take you some years to fill, especially since your construction does not allow for more than one user at a time to insert anything.
Re: default(max) size of NUMBER datatype in oracle-10g [message #234345 is a reply to message #234342] Tue, 01 May 2007 00:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
balamuralikrishnan,

As Frank says, it is not an autoincrement as you implement an increment, if you do something, it is not automatic. Seems logic to me.

Have a look at NUMBER datatype to get the maximum value.

Regards
Michel
Re: default(max) size of NUMBER datatype in oracle-10g [message #234352 is a reply to message #234345] Tue, 01 May 2007 01:11 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Thanks Mr Frank & Michel
I have used the above logic inside the procedure so that at a time not more then one user can increment the vendor_id.

1st doubt
---------
Because more then one user can not invoke a Procedure at a time.am i true?.

2ed doubt
---------

Why peoples telling ,Use always vendor_id(number datatype)instead of vendor_code(varchar2 datatype)column for accessing data thru Join/Subquery.Both are indexed column ,Even if both takes almost same number of bytes?

vendor_id vendor_code
---------------- --------------------
(number datatype) (varchar2 datatype)
1001 vc-01
1002 vc-02
1003 vc-03

by
balamuralikrishnan.s
Re: default(max) size of NUMBER datatype in oracle-10g [message #234354 is a reply to message #234352] Tue, 01 May 2007 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1st doubt: yes, they can invoke the procedure at the same time unless you explicitly lock table vendor_master in exclusive mode.
And if you don't you're in a mess.

2nd doubt: Which people?
How many bytes to store 1000, 1001, 1002? Do you know?

Regards
Michel

[edit: complete the first sentence to prevent from misreading it]

[Updated on: Tue, 01 May 2007 02:06]

Report message to a moderator

Re: default(max) size of NUMBER datatype in oracle-10g [message #234355 is a reply to message #234352] Tue, 01 May 2007 02:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sbmk_design wrote on Tue, 01 May 2007 08:11
1st doubt
---------
Because more then one user can not invoke a Procedure at a time.am i true?.

No, multiple people can execute a procedure at the same time.
It is the way your code is designed that requires you to lock the table:
Suppose I want to insert a record in your table. The procedure determines the max(id) (Let's say it is 42) and inserts a record for me with id 43.
At the same time (so, before I had the chance to issue my commit), you insert a record. The max(id) still is 42, so you too insert record 43.
This can only be prevented by locking the entire table.
(or by using an Oracle sequence, which is the normal way of doing this).
Re: default(max) size of NUMBER datatype in oracle-10g [message #234378 is a reply to message #234355] Tue, 01 May 2007 05:33 Go to previous messageGo to next message
sbmk_design
Messages: 88
Registered: April 2007
Location: CHENNAI
Member
Thanks a lot Mr.Frank & Mr.Michel
Instead of using exclusive lock on the table - commit statement inside the procedure will solve my problems?.I have given my procedure

body part of my insert procedure
---------------------------------
PROCEDURE pro_insert_vm ( vendor_id number,
vendor_code varchar2)IS
BEGIN
INSERT INTO vendor_master(vendor_id,vendor_code)values ((SELECT nvl(max(vendor_id),0)+1 FROM vendor_master),vendor_code);

COMMIT;
END pro_insert_vm;

Note-1
-------
before using the above style-procedure I tried with the Oracle Sequence ,but it made big mess, we invoking the procedure thru Jboss server.Due to some java coding problem,even if invoking the procedure fails vendor_id only increaments.(i.e data in the row is not inserted but sequence value only increamenting)

PROCEDURE pro_insert_vm ( vendor_id number,
vendor_code varchar2)IS
variable1 number;
BEGIN
SELECT SEQ_VM.NEXTVAL INTO variable1 FROM DUAL;
INSERT INTO vendor_master(vendor_id,vendor_code)values (variable1,vendor_code);

COMMIT;
END pro_insert_vm;

Note-2
------
can I have syntax for using exclusive lock inside the procedure ..
Thanks in advance
balamuralikrishnan.s


Re: default(max) size of NUMBER datatype in oracle-10g [message #234381 is a reply to message #234378] Tue, 01 May 2007 06:22 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ A commit validates the previous modifications and ends the current transaction. It must made in the application not in a procedure. What if your application decides to roll back its modifications after calling the procedure? It can't.

2/ Even if you commit, this does not prevent for 2 callers to call the procedure at the same time and so get the same number.

3/ To execute a DDL inside a procedure use 'execute immediate'.
But once again, you then serialize all your users. What if one session holding the table decides to no more answer (the user is going to lunch, the network shut down, the application is killed...)?

4/ If you can't use sequence this is because you don't know how to use and/or you/the application make false assumptions on some (pre-)requirements.

Regards
Michel
Previous Topic: Insufficiant privilages
Next Topic: Regular Expression In Query Help Needed...
Goto Forum:
  


Current Time: Mon Dec 05 11:03:51 CST 2016

Total time taken to generate the page: 0.12077 seconds