CODEWITHSUNDEEP
oracle-databaseinsert
stuckedunderflow
stuckedunderflow

Reputation: 3767

Make sure Oracle insert statement in order

I have an ArrayList let's say with 4 rows in defined order. I want to insert these 4 rows into Oracle table accordingly. But weird, it gets into the table but with wrong order.

for (int x = 0; x < myArray.size(); x++) {
   //Some validation
   //insert statement
   MyJB myJB = (MyJB) myArray.get(x);
   String query = "insert into table X values(myJB.getColumn1,myJB.getColumn2)";
   log.debug(query);
   stmt = conn.prepareStatement(query);
   stmt.executeUpdate();
   stmt.close();
}

The log will show the correct order, i.e from x=0 to x=3. But select * from table X will result 4 rows but with different order

How to force Oracle will insert in correct order as in ArrayList?

Thanks

Upvotes: 0

Views: 551

Answers (1)

Leos Literak
Leos Literak

Reputation: 9474

Unless you define sort order, you cannot rely on order of elements. The best way is to create primary key from sequence and order by it.

Alternatively you may try oracle specific rownum pseudocolumn: http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm but IMHO it is not what you want.

EDIT

CREATE SEQUENCE mySequence;

PreparedStatement statement = conn.prepareStatement("insert into table XY values (mySequence.nextval, ?, ?)");
statement.setObject(1, myJB.getColumn1);
statement.setObject(2, myJB.getColumn2);
statement.executeUpdate();

And then 

select * from XY order by Z asc

where Z is name of first column will do what you want

Upvotes: 1

Related Questions