nested exception is org.hibernate.exception.SQLGrammarException: ORA-02289: sequence does not exist

Question

EDIT:

@NamedQueries({
    @NamedQuery(name="getOLTsByProcessStateAndAssignee",query="select o from Olt o where o.activityProcessId IN(:procId) order by modifiedtime desc"),
    @NamedQuery(name="getOLTsByProcessStateAndAssigneeForSearch",query="select o from Olt o where o.activityProcessId IN(:procId) and o.name like :name order by modifiedtime desc"),
    @NamedQuery(name="findOltbyname",query="select o from Olt o where o.name=:oltname and o.jioCenter.id=:jioCenterId"),
 })
@XmlRootElement(name="Olt") @Audited @Entity
@Table(name="olt")
public class Olt extends BaseEntity implements Serializable
{
    /**
     * 
     */
    private static final long serialVersionUID = 1L;


    /*@GeneratedValue(generator="olt_id_gen")
    @GenericGenerator(name = "olt_id_gen",
        strategy = "com.inn.fttx.model.IntegerSequenceGenerator",
        parameters = {
            @Parameter(name="sequence" , value="OLT_ID_SEQ")
        })
    @Id  */



    @SequenceGenerator(name = "olt_id_SEQ", sequenceName = "olt_id_SEQ", allocationSize=1)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="olt_id_SEQ")

here I am getting the following exception -

I have seen different threads of coderanch and stackoverflow, what i found:

the Oracle database is running (Most likely)
you app is connected to it (Most likely)
You are connected to the correct database (Maybe/Maybe not)
There is actually a sequence table called "olt_id_SEQ"

I just pasted this points above, so i can show what I have done already.

But in my Oracle database, I have this sequence and i can perform select with nextval on it.

Please let me know, if trouble is from database side or java? Any suggestion/advice would be greatly appreciated.

Never mind other problems on other posts, what specific problem do you have? I don't see a question in your post. — JamesB
– JamesB, Commented Jul 10, 2014 at 11:27
Are you sure your sequence exists in the DB schema your application is using? — JamesB
– JamesB, Commented Jul 10, 2014 at 11:40
@JamesB yes it is there. i have also recreated it. But still getting the same exception. I have also performed select CUSTOMER_ID_sequence.nextval which giving me correct result. — Aamir
– Aamir, Commented Jul 10, 2014 at 11:43
As stated below, you may need to check the grants on this sequence if the application is using a different schema to the owner of the sequence. — JamesB
– JamesB, Commented Jul 10, 2014 at 11:45
See answer below from Jorge Campos. I did not take your original post literally. You have to change the sequence name in your code to customer_id_sequence. — JamesB
– JamesB, Commented Jul 10, 2014 at 11:46

Jorge Campos · Accepted Answer · 2014-07-10 12:05:06Z

2

If your statement There is actually a sequence table called "CUSTOMER_ID_sequence" makes no sense since your code is trying to reach the olt_id_SEQ sequence. And a sequence is not a table.

So there are two options here:

1 - The user you are using to connect to oracle does not have proper grants on that sequence olt_id_SEQ.

2 - You are using a wrong sequence in your code, since you mentioned it should be

 @SequenceGenerator(name = "CUSTOMER_ID_sequence", 
                    sequenceName = "CUSTOMER_ID_sequence", allocationSize=1)
 @GeneratedValue(strategy=GenerationType.SEQUENCE, 
                 generator="CUSTOMER_ID_sequence")

Edit

The OP edited his question but the problem here still are the same as I mentioned above.

1 - The user you are using to connect to oracle does not have proper grants on that sequence olt_id_SEQ or this sequence doesn't exists at all. To check that get the user and password that you use on your application and run this query:

select * from all_objects where object_name = 'OLT_ID_SEQ'

If this query do not return any row the sequence does not exist or it doesn't have a grant to it.

See what was the schema that is the owner of the sequence. If you are using an user that has limited permission you may need to use: [schemaName].olt_id_SEQ or create a public synonim to this sequence.

edited Jul 10, 2014 at 12:05

answered Jul 10, 2014 at 11:43

Jorge Campos

23.9k7 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aamir Over a year ago

4 points i mentioned was from other site. And CUSTOMER_ID_sequence was just an example. Actually I am using olt_id_SEQ sequence and using the same in code too. And I have the same sequence in my db too. And I have to tried grant also.

JamesB Over a year ago

I thought customer_id_sequence was just an example of the problem but it turns out this is the sequence in use and the code is not referring to it.

JamesB Over a year ago

@Aamir, you need to be careful how you post, it is very confusing. Look at the comments above: You stated you ran the statement "select CUSTOMER_ID_sequence.nextval" against your database and it was successful. Now you are saying it is just an example name.

Aamir · Accepted Answer · 2014-07-10 12:12:44Z

0

My problem is solved now. Hibernate was using a sequence automatically. So I provided the grant to that sequence now its working.

answered Jul 10, 2014 at 12:12

Aamir

7583 gold badges18 silver badges43 bronze badges

1 Comment

Jorge Campos Over a year ago

You should accept the answer I provided because it stated this problem about the grant and it was in fact your problem.

Collectives™ on Stack Overflow

nested exception is org.hibernate.exception.SQLGrammarException: ORA-02289: sequence does not exist

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related