I have a script in JAVA in which I am creating around 10 tables .....the condition is that either all should be created or none...so I decided to use transaction. but, I found that for DDL statement JDBC connection.autocommit(false) doesn't work, CREATE statement automatically gets committed.for DML statements it works fine.
import java.sql.*;
class testinORCL{
public static void main(String args[]){
java.sql.Connection connect = null;
try{
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:xxxx/XXXX@ssssss:1521:XE");
connect = ods.getConnection();
connect.setAutoCommit(false);
Statement statement = connect.createStatement();
statement.executeQuery("CREATE TABLE TEMP_table (field VARCHAR(20) , value VARCHAR(30)) ");
connect.rollback(); //the table should not be created
connect.setAutoCommit(true);
connect.close();
}catch(Exception e){System.out.println(e);}
}
}
i also checked these....
DatabaseMetaData.dataDefinitionCausesTransactionCommit() (which is true in my case)
DatabaseMetaData.dataDefinitionIgnoredInTransactions() (which is false in my case)
so if anyone can explain me how should I handle this......