Tuesday, October 25, 2016

SQL Quaries

commonj.sdo.DataObject __smo = (commonj.sdo.DataObject)smo;
 int tableSize=0;

try {

Context ctx = new InitialContext();
DataSource ds = (javax.sql.DataSource) ctx.lookup("jdbc/PIMS.Oracle.DS");
Connection conn = ds.getConnection();
String sql = "SELECT PIMS.SEQ_T_OB_EDI_315.nextVal from dual";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

while (rs.next()) {
tableSize = rs.getInt(1);
   __smo.getDataObject("context").getDataObject("transient").setInt("tableSize", tableSize);

}

}
catch(Exception ex){
CommonLogger.error("---error while getting Max TID in EDI315InboundMF module from Oracle T_OB_EDI_315----"+ex.getMessage());
}

out.fire(__smo);



1.SELECT PIMS.SEQ_T_OB_EDI_315.nextVal from dual

If you have sequencing imposed and you want to get next value use this.

2. Go the BO and then to application info and then right click and go to new and then select UID.

And then give schemaname.sequencename.
EX: PIMS.SEQ_T_OB_EDI_315




3.SELECT MAX(TID) FROM PIMS.T_OB_EDI_315

to get max value of a column from a table