aspose file tools*
The moose likes JDBC and the fly likes how to increase the number in oracle database automatically Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to increase the number in oracle database automatically" Watch "how to increase the number in oracle database automatically" New topic
Author

how to increase the number in oracle database automatically

pankaj saxena
Ranch Hand

Joined: Nov 21, 2009
Posts: 46
sir,

I have one frame in jave that takes different types of data entered by user and save it in oracle database.
Datas entered by user are: name, address, data, etc.

a Table in oracle named 'CandidateDetails' has following fields : RollNo,name, address, data,etc.

My question is:
i want to auto increment the rollno in database as and when any data is entered by user.
I know there is one option named : sequence in oracle that can solve this problem, but my problem is when i try to insert the data(rollno, name , address, etc ) from java program it displays error for sequence name.

for eg:
I have one sequence : sq_rollno
The sequence which i have created in oracle is:
CREATE SEQUENCE sq_rollno
START WITH 100001
INCREMENT BY 1
MINVALUE 100001
MAXVALUE 3000000
CACHE 5;

compiler generates error on this line in java program : INSERT INTO CandidateDetails VALUES(sq_rollno.NEXTVAL,'Rajes','Delhi');
compiler says: it can't resolve symbol sq_rollno.NEXTVAL.

Or , Is there any other option to solve this issue?

thanks.
regards.



Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1135

Pankaj,
You wrote:

compiler generates error on this line in java program

This indicates to me that there is an error in your java code.
Hence I suggest you post the relevant part of your java code and perhaps someone will be able to spot the error for you.

Good Luck,
Avi.
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1851
    
  16

I'm an Oracle devloper, not a Java developer, so maybe this won't help. But here goes anyway...

Your SQL looks OK if you were running it in Oracle, which will recognise the NEXTVAL bit, but maybe Java is trying to compile it as generic SQL instead? Did you get an Oracle error ("ORA-nnnnn") or just a Java error?

Try running your SQL in Oracle via SQL*Plus to make sure it's OK.

If you're using Hibernate, you can tell Hibernate to use an Oracle sequence for auto-incrementing the RollNo value instead. Check the Hibernate documentation for details.

Within Oracle, you can create a before-insert-for-each-row trigger to set the RollNo automatically using the sequence, regardless of where the insert comes from. If you do this, then you do not need to provide the RollNo value at all in the INSERT statement.

Incidentally, it's generally a good idea to include the column names in your INSERT statement, as it makes it easier to ensure they are being matched correctly to your input values e.g. INSERT INTO candidateDetails (RollNo,name, address, data) values (....).

Also, is there any reason for the MAXVALUE of your sequence being 3000000? If not, I would use NOMAXVALUE instead. It doesn't make any difference to storage, and it will ensure that you never run out of values in your sequence.


No more Blub for me, thank you, Vicar.
pankaj saxena
Ranch Hand

Joined: Nov 21, 2009
Posts: 46
Sir Crish,

I am following your advise to use a trigger to auto increment the RollNo.

If I find any problem in this regard , i will definitely consult with you.

thanks.
regards.
pankaj.
pankaj saxena
Ranch Hand

Joined: Nov 21, 2009
Posts: 46
Sir,

This is my code that exists in one of java files:
the problem is with the line : ps.setInt(1,sq_roll_no.NEXTVAL);

It is showing the problem with sq_roll_no, earlier I didn't use the commented line but to make sure whether it works fine, I added it.
If the above problem gets solved , I would like to add another column as 'Id' that will take the input as sq_roll_no.CURRVAL. If NEXTVAL works , CURRVAL will definitely work.
I would like to ask whether I should write this sequence creation code in Java file or in database.



thanks.
regards.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to increase the number in oracle database automatically