• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to increase the number in oracle database automatically

 
Ranch Hand
Posts: 47
C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.



 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

 
pankaj saxena
Ranch Hand
Posts: 47
C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 47
C++ Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic