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.
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
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?
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.
Joined: Nov 21, 2009
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.
Joined: Nov 21, 2009
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.
subject: how to increase the number in oracle database automatically