Search code examples
javaoracle-databasejdbcsequences

Does the SGBD oracle always execute the queries of an anonymous plsql in the same order?

This a part of my code java constructing a function in an anonymous plsql block:

*String p ="FUNCTION get_result RETURN varchar2 AS "
+"BEGIN "
+"sql_error:='ok';"
+query1+";"
+query2+";"
+query3+";"
+"COMMIT;"
+"RETURN sql_error; "
+"EXCEPTION "
+"when others then "
+"ROLLBACK;"
+"end;";*

where queries 1,2 and 3 are sql queries; sometimes query2 is not executed and this does not generate a transaction error and queries 1 and 3 and committed. I would like to Know how is it possible and, how can I correct it. I would like also to know which procedure is better between calling anonymous block statements and statement.addBatch

I want to know how the manage sessions that execute transaction that manipulate many sequences. I found that using many seauences in a transaction causes errors that oracle do not send back to the user. Do they sometimes get the same value for 2 executions on sequence.nextval ? thank you

Solution

  • I've seen the bug I was creating. I fact, all the queries were not in the same transaction, due to the fact that I was using a static attribute that was collecting the queries of various transactions and sending them to the SGBD oracle through anonymous plsql.

    so I changed the static variable to simple and public one, collector of the queries assessed through an instance of the class, so that all the queries of a transaction should be always sent at once and executed in the same order.