0

I have an error in my development with java,oracle but I have not been able to solve it for hours. This is a query code.

<insert id="insert">

    insert into board (bno,title,content,writer) values

    ( seq_board.nextval, #{title}, #{content}, #{writer} )

</insert>

<insert id="addAttach">

    insert into attach (fullName, bno) values 

    ( #{fullName}, seq_board.currval )

</insert>

In the board table, the data is stored well. If i write a file with a file attached to the board, a 500 error page appears.

If i click Backspace, I see the board and the file is not uploaded.

Helll
  • 1
  • 1
  • 4
  • Sounds like the two statements are just running in different sessions. – Alex Poole Apr 20 '18 at 17:38
  • 1
    The [documenttion](https://docs.oracle.com/cd/B28359_01/server.111/b28286/pseudocolumns002.htm#SQLRF51138) clearly states, that `Before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.` Just first call `NEXTVAL`, then you can call `CURRVAL` within the session. I am not sure what other answer do you want to get ? – krokodilko Apr 20 '18 at 17:38
  • select * from v$session where username = 'username'; select * from v$session where status = 'active'; Is it related to not seeing the session when I run two queries? – Helll Apr 21 '18 at 05:07
  • When I query all sessions, I have one session with username, and when I execute the above two queries, nothing is retrieved. – Helll Apr 21 '18 at 05:07
  • Note that insert and addattach are made to run at the same time, so that only inserts will be done if the file is not attached. – Helll Apr 21 '18 at 05:08
  • In toad for oracle, select seq_board.nextval from dual; select seq_board.currval from dual; In this order, it works fine, but 500 errors occur on the web page. – Helll Apr 21 '18 at 05:14

1 Answers1

0

the reason is discussed in Oracle: sequence MySequence.currval is not yet defined in this session.

thanks for krokodilko.

from part of the configuration of xml text, it seems you are using mybatis or similiar frame.

i suggest that insert id="insert" should return the generated seq.nextval,then you get it, pass the value to insert id="addAttach" instead of use seq.currval for workaround.

yaoweijq
  • 263
  • 2
  • 12
  • select * from v$session where username = 'username'; select * from v$session where status = 'active'; Is it related to not seeing the session when I run two queries? – Helll Apr 21 '18 at 05:03
  • When I query all sessions, I have one session with username, and when I execute the above two queries, nothing is retrieved. – Helll Apr 21 '18 at 05:06
  • Note that insert and addattach are made to run at the same time, so that only inserts will be done if the file is not attached. – Helll Apr 21 '18 at 05:08
  • In toad for oracle, select seq_board.nextval from dual; select seq_board.currval from dual; In this order, it works fine, but 500 errors occur on the web page. – Helll Apr 21 '18 at 05:14
  • https://stackoverflow.com/questions/41834389/mybatis-insert-in-oracle-with-sequence-id described the details. you could search for how to return the nextval value in mybatis. – yaoweijq Apr 23 '18 at 02:40