Hello Ranchers!! I'm using JSP and Oracle to do a project. there is a table and the table details are here!!
========================================================================== column nam ========================================================================== register_num name age =========================================================================
here for the register_num it is the primary key and also have a sequence generated.. the reister_num is a varchar2(20) datatype.. and tha value it assigns is ERP/05/2006/001 here ERP is the sting that i passes /05/2006 is the current month and the year.. after doing all these thing i can do concatenate the string to produce like this
i'm using a condition also.. if(no >0 && no<=9) val = ERP/05/2006/00000 if(no >=10 && no <=99) val = ERP/05/2006/0000 if(no >=100 && no <=999) val = ERP/05/2006/000 if(no >=1000 && no <=9999) val = ERP/05/2006/00 if(no >=10000 && no <=99999) val = ERP/05/2006/0
this is what i've to do .. but there is no change in the sequence.. the value that has to the inserted is ERP/05/2006/000001 ERP/05/2006/000002 ERP/05/2006/000003 ERP/05/2006/000004 ERP/05/2006/000005 ----- ERP/05/2006/000100 ERP/05/2006/000101 like that...
--------------------------------------------------------------- so if i insert the value the query will be like this insert into table_name(register_no,name,age) values(reg_no.nextval,name,age)
--------------------------------- this query is doing the inserting proceedure.. but it is just inserting the register_no as 1,2,3 etc
i want to chage that value and has to insert the String like this ERP/05/2006/000001 ================
please help me ranchers.. how can i do this problem
Generally speaking, using a long String/varchar/varchar2 as a primary key tends to hurt performance, mostly because of the larger storage size required for the key. For example, your primary key index is going to be something like 3 to 5 times bigger using a varchar2(20) than if you used a number. Getting the index from disk will take longer and comparing 2 keys will take longer.
However, you're probably already stuck on with that decision...
You can do something like:
Joined: Dec 28, 2005
thank you very much STU.. I can now insert the value like this ERP/05/2006/00001
but there is also a problem for this it is taking a space value ie., the value inserting in table is ERP/05/2006/ 00001 there is a space b/w 2006/ and 00001
i'm copying the whole.. Prepare Statement.. =========================================================== String query = "insert into system.ERP_MAIN(id,username,password,confirm,admin,status,country,subcountry,branch)values(?||to_char(sysdate,'/mm/yyyy/') || to_char(system.login_user_id.nextval,'0000'),?,?,?,?,?,?,?,?)";
It completely depends on your implementation !!! If you want to use same sequence number twice !! then that is one way to do it.
For me I never rely on sequences directly. because If your transaction fails, the sequence number does not rollback.
I always use sequence number pool, using singleton classes. If transaction fails I return the sequence number to pool
At a time I keep a configurable number of sequences in pool.
So it totally depends on your implementation
Joined: Dec 28, 2005
Hello Ranchers Again the sequence is creating problems with me.. so i've planned to put the column id as integer so while using the insert statement how to write!! that String sql = insert into table_name (id,name,age)values(reg_id.nextval,name,age); PreparedStatement ps = dbCon.prepareStatement(query); ps.setInt(1,0); ps.setString(2,name); ps.setInt(3,age);