Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

TransactionGuardExample.java

ExaGridDba Jul 22nd, 2013 106 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. import java.sql.SQLException;
  2. import oracle.jdbc.OraclePreparedStatement;
  3. import oracle.jdbc.OracleResultSet;
  4. import oracle.jdbc.pool.OracleDataSource;
  5. import oracle.jdbc.OracleConnection;
  6. import oracle.jdbc.LogicalTransactionId;
  7. import oracle.jdbc.OracleCallableStatement;
  8. import oracle.jdbc.OracleTypes;
  9. import java.util.Properties;
  10.  
  11. public class TransactionGuardExample
  12. {
  13.         private static OracleConnection Connect()
  14.         throws SQLException
  15.         {
  16.                 OracleDataSource ods = new OracleDataSource();
  17.  
  18.                 String url = "jdbc:oracle:thin:@//192.168.1.17:1521/wailua_hr";
  19.                 ods.setURL(url);
  20.                 ods.setUser("hr");
  21.                 ods.setPassword("hr123");
  22.  
  23.                 OracleConnection conn = (OracleConnection) ods.getConnection();
  24.                 System.out.println("connected as hr");
  25.                 conn.setAutoCommit(false);
  26.  
  27.                 displayLogicalTransactionId( conn );
  28.  
  29.                 return conn;
  30.         }
  31.  
  32.         public static void displayLogicalTransactionId( LogicalTransactionId ltxid )
  33.         throws SQLException
  34.         {
  35.                 if ( ltxid == null )
  36.                 {
  37.                         throw new RuntimeException ("Can't get LogicalTransactionId. Set the service COMMIT_OUTCOME to true and try again.");
  38.                 }
  39.                 byte[] barray = ltxid.getBytes();
  40.  
  41.                 StringBuilder sb = new StringBuilder();
  42.                 for(byte b : barray){
  43.                         sb.append(String.format("%02x", b & 0xff));
  44.                 }
  45.                 System.out.println( "trans id " + sb );
  46.         }
  47.  
  48.         public static void displayLogicalTransactionId( OracleConnection conn )
  49.         throws SQLException
  50.         {
  51.                 displayLogicalTransactionId( conn.getLogicalTransactionId() );
  52.         }
  53.  
  54.         private static void killSession ( OracleConnection conn )
  55.         throws SQLException
  56.         {
  57.                 OracleConnection sconn = ConnectSys();
  58.                 Integer sidary[] = Sid( conn );
  59.  
  60.                 String sql = "alter system kill session '" + sidary[ 0 ] + "," + sidary[ 1 ] + "' immediate";
  61.                 OraclePreparedStatement pstmt = ( OraclePreparedStatement ) sconn.prepareStatement( sql );
  62.  
  63.                 pstmt.execute();
  64.                 System.out.println( "sid " + sidary[ 0 ] + " ser " + sidary[ 1 ] + " killed" );
  65.         }
  66.  
  67.         private static OracleConnection ConnectSys()
  68.         throws SQLException
  69.         {
  70.                 OracleDataSource ods = new OracleDataSource();
  71.  
  72.                 String url = "jdbc:oracle:thin:@//192.168.1.17:1521/wailua";
  73.                 ods.setURL(url);
  74.                 Properties prop = new Properties();
  75.  
  76.                 prop.setProperty("user","sys");
  77.                 prop.setProperty("password","sys.n1te");
  78.                 prop.setProperty("internal_logon","sysdba");
  79.                 ods.setConnectionProperties(prop);
  80.                 OracleConnection sconn = (OracleConnection) ods.getConnection();
  81.                 System.out.println("connected as sys");
  82.  
  83.                 return sconn;
  84.         }
  85.  
  86.         private static Integer[] Sid ( OracleConnection conn )
  87.         throws SQLException
  88.         {
  89.                 String sql = "select sid, serial# ser from v$session where sid = ( select sid from v$mystat where rownum <= 1 )";
  90.                 OraclePreparedStatement pstmt = ( OraclePreparedStatement ) conn.prepareStatement( sql );
  91.                 OracleResultSet rs = ( OracleResultSet ) pstmt.executeQuery();
  92.                 Integer sid = null;
  93.                 Integer ser = null;
  94.                 while ( rs.next() )
  95.                 {
  96.                         sid = rs.getInt( 1 );
  97.                         ser = rs.getInt( 2 );
  98.  
  99.                 }
  100.  
  101.                 Integer sidary[] = { sid, ser };
  102.                 return sidary;
  103.         }
  104.  
  105.         private static void displayOutcome( OracleConnection conn, LogicalTransactionId ltxid )
  106.         throws SQLException
  107.         {
  108.                 String sql = "declare"
  109.                         + "     l_comm boolean;"
  110.                         + "     l_compl boolean;"
  111.                         + "begin"
  112.                         + "     dbms_app_cont.get_ltxid_outcome ( ?, l_comm, l_compl);"
  113.                         + "     ? := case when l_comm then 1 else 0 end;"
  114.                         + "     ? := case when l_compl then 1 else 0 end;"
  115.                         + "end;";
  116.  
  117.                 OracleCallableStatement cstmt = ( OracleCallableStatement ) conn.prepareCall(sql);
  118.                 cstmt.setBytes(1, ltxid.getBytes());
  119.                 cstmt.registerOutParameter(2, OracleTypes.NUMBER);
  120.                 cstmt.registerOutParameter(3, OracleTypes.NUMBER);
  121.  
  122.                 cstmt.execute();
  123.  
  124.                 Boolean committed = cstmt.getInt(2) == 1;
  125.                 Boolean callcompleted = cstmt.getInt(3) == 1;
  126.  
  127.                 System.out.println("LTXID committed? " + committed);
  128.                 System.out.println("User call completed? " + callcompleted);
  129.         }
  130.  
  131.         public static void main(String[] args)
  132.         throws Exception
  133.         {
  134.                 OracleConnection oconn = Connect();
  135.  
  136.                 String sql = "update employees set first_name = ? where employee_id = ?";
  137.                 OraclePreparedStatement pstmt = ( OraclePreparedStatement ) oconn.prepareStatement( sql );
  138.  
  139.                 pstmt.setString( 1 , "Taylor" );
  140.                 pstmt.setInt( 2 , 107 );
  141.                 pstmt.execute();
  142.  
  143.                 System.out.println("after execute:");
  144.                 displayLogicalTransactionId( oconn );
  145.                 LogicalTransactionId saveltxid = oconn.getLogicalTransactionId();
  146.                 System.out.println("saveltxid:");
  147.                 displayLogicalTransactionId( saveltxid );
  148.                 // oconn.commit();
  149.                 killSession( oconn );
  150.  
  151.                 OracleConnection nconn = Connect();
  152.                 System.out.println("after reconnect, saveltxid:");
  153.                 displayLogicalTransactionId( saveltxid );
  154.                 displayOutcome( nconn, saveltxid );
  155.         }
  156. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top