How to call a Oracle database sequence number by using MyBatis?

Question

I want to call a sequence number from my Oracle Database 10g by using MyBatis, but I only get an error message like the following:

ORA-02289: Sequence is not available.

How can I call a sequence number from an Oracle Database?

Here is my Maven Project Dependency concerning the current MyBatis Version:

<dependency>
   <groupId>org.mybatis</groupId>
   <artifactId>mybatis-spring</artifactId>
   <version>1.1.1</version>
</dependency>

Here is my Dao Java class:

long mySeqNumber = myDaoClass.getNewNumber(); // here I get an exception

Here is my xml declaration:

<select id="getNewNumber" resultType="java.lang.Long" >
    SELECT mySeq.nextval
    FROM dual
</select>

and presumably running the select from the sequence works perfectly when logged in to the database as the same user that Java is logged in as? — ninesided, May 04 '15 at 09:35
`SELECT mySeq.nextval FROM dual` change to `SELECT nextVal('mySeq')` — Afsun Khammadli, May 04 '15 at 09:36
@Afsun Khammadli: With your solution I get the error ORA-00904: "NEXTVAL": invalid identifier — , May 04 '15 at 09:40
For what you will use sequence nextval?I think you will use for inserting isn't it? — Afsun Khammadli, May 04 '15 at 09:43
Yes for an SQL Insert, but first I need to get the next Sequence number and than I put the sequence number into a XML Insert Statement as parameter — , May 04 '15 at 09:44
` SELECT nextVal('my_sequence') INSERT INTO myobject_table (objectId,objectName) VALUES (#{objectId},#{objectName}) ` Please check this. — Afsun Khammadli, May 04 '15 at 09:47
maybe try adding a public synonym, or specify schema/owner (select SOMESCHEMA.SOMESEQ.nextval ...) — tbone, May 04 '15 at 17:59

score 0 · Answer 1 · edited Apr 13 '17 at 07:07

0

I think you use nextval for inserting.Try following:

<insert id="insertPerson" parameterType="Person" useGeneratedKeys="true"> 
  <selectKey keyProperty="personId" resultType="int" order="BEFORE">
    SELECT nextVal('mySeq')
  </selectKey>
  INSERT INTO person (personId,PersonName) VALUES (#{personId},#{personName}) 
</insert>

Also instead of SELECT nextVal('mySeq') you can use this SELECT mySeq.nextVal from dual

edited Apr 13 '17 at 07:07

sjngm

12,423
14
84
114

answered May 04 '15 at 10:00

Afsun Khammadli

2,048
4
18
28

Now I get this error: java.lang.IllegalArgumentException: Mapped Statements collection does not contain value for myClass – May 04 '15 at 10:02
You must change parameters for your table structure. – Afsun Khammadli May 04 '15 at 10:03
Sorry but i have no idea, what muste be changed here: SELECT nextVal('mySeq') INSERT INTO ${mySchema}.myTable ( id, loggingtime) VALUES( #{myId}, sysdate) – May 04 '15 at 10:09
I think my answer is true.There is any problem may be in your code.Please change `sysdate` to `sysdate()` – Afsun Khammadli May 04 '15 at 10:20
No sysdate is not the problem. The problem ist anywhere else in my sourcecode, but I can´t find the mistake – May 04 '15 at 11:17

score 0 · Answer 2 · answered Aug 12 '20 at 09:42

I was able to achieve this,

<dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.2</version>
    </dependency> 



<select id="getUploadId" resultType="int">
    select {schema}.SEQUENCE_NAME.NEXTVAL from dual
</select>

How to call a Oracle database sequence number by using MyBatis?

2 Answers2

Linked