An example of JSP with Oracle

This example of JSP with Oracle shows how to produce an Oracle database report. The result is a list of active transactions and of the rollback segments being used, which can be very useful. Very often the DBA has to identify such statements and having this report on the Intranet can give an immediate answer.

If the webserver has already been set up for JSP, in order to access an Oracle database the Oracle JDBC classes must be put in the CLASSPATH variable.

iPlanet CLASSPATH

These classes come with the Oracle installation and can be found on the directory $ORACLE_HOME/jdbc/lib


xyz@server1: pwd
/u00/app/oracle/product/9.0.1/jdbc/lib
xyz@server1: ls -l
total 59532
-rw-r--r--   1 oracle   dba      1823916 May  7  2002 classes111.zip
-rw-r--r--   1 oracle   dba      2097576 May  7  2002 classes111_g.zip
-rw-r--r--   1 oracle   dba      1985518 May  7  2002 classes12.zip
-rw-r--r--   1 oracle   dba      2132947 May  7  2002 classes12_g.zip
-rw-r--r--   1 oracle   dba        97158 Aug 23  2000 jndi.zip
-rw-r--r--   1 oracle   dba        14810 May  7  2002 jta.zip
-rw-r--r--   1 oracle   dba      10155207 Jun 28  2000 nls_charset11.zip
-rw-r--r--   1 oracle   dba      12097171 Jun 28  2000 nls_charset12.zip

The report is called by a simple input form which accepts a username and a password and offers a list of connections, specified as
hostname:port:INSTANCE

The hostname can be replaced by an IP address.

Input form for the JSP Oracle connection

We can pass the login information by using a simple input form

Login form to Oracle


<FORM METHOD=POST ACTION="activeTransactions.jsp">
<TABLE border=1>
<TR>
	<TD>Username:</TD>
	<TD><INPUT TYPE="text" NAME="uid"></TD>
</TR>
<TR>
	<TD>Password:</TD>
	<TD><INPUT TYPE="password" name="pwd"></TD>
</TR>
<TR>
	<TD>URL:</TD>
	<TD>
	<SELECT NAME="url">
	  <OPTION>hostLondon:1521:PROD
	  <OPTION>hostRome:1523:UAT
	</SELECT>
	</TD>
</TR>
<TR>
  <TD colspan=2 align="center"><INPUT TYPE="submit" value="Go"></TD>
</TR>

</TABLE>
</FORM>

What is essential in this form is that it defines three fields uid, pwd and url and that it calls the file activeTransactions.jsp.

The jsp servlet reads the contents of these three variables by calling request.getParameter()

<%
  
  String url        = request.getParameter("url");
  String sPassword  = request.getParameter("pwd");
  String sUsername  = request.getParameter("uid");
  java.util.Date date = new java.util.Date();
  

%>

It is important to remember that the variables are case sensitive and must be identical in the form definition and in the JSP call.

The next step is to load the Oracle JDBC driver with java.lang.Class.forName("oracle.jdbc.driver.OracleDriver") and establish the connection with java.sql.DriverManager.getConnection(url,sUsername,sPassword)

  
  url   = "jdbc:oracle:thin:@" +  url ; 
  java.sql.Connection con      = null;
	        
  try 
  {
    java.lang.Class.forName("oracle.jdbc.driver.OracleDriver");
  }
  catch (java.lang.Exception e)
  {
    out.println("Exception: could not load the driver?");
    return;
  }
 
  con  =  java.sql.DriverManager.getConnection(url,sUsername,sPassword);

Connection -> Statement -> ResultSet

 
     java.sql.Statement  stmt_transac = con.createStatement();
     java.sql.ResultSet  rs_transac   = stmt_transac.executeQuery("SELECT osuser o,
     s.sid sid, s.serial# ser, s.machine machine, terminal, process, username u, 
     r.segment_name s,  substr(sa.sql_text,1,200) txt 
	 FROM v$session s,     v$transaction t, dba_rollback_segs r, v$sqlarea sa 
	 WHERE s.taddr=t.addr
	 AND t.xidusn=r.segment_id(+) 
	 AND  s.sql_address=sa.address(+) ");

The ResultSet returns the results in a loop:

 

 while (rs_transac.next())
 {
    String ssOsuser    = rs_transac.getString("o");
    String ssSid       = "" + rs_transac.getInt("sid");
	... ... 
 }

and the database records are displayed generating HTML records.

 
   <TR>
	 <TD><FONT FACE="SANSSERIF" SIZE=2><%= ssOsuser    %></TD>
	 <TD><FONT FACE="SANSSERIF" SIZE=2><%= ssSid       %></TD>
	 ... ... ...
   </TR>

Here is the output of this JSP servlet: The user SCOTT is deleting a record from the table emp and is using the UNDO _SYSSMU4$. The SID, serial# are shown and they can be used to kill the session. Please find the source code in activeTransactions.jsp Home
[Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic]

Rate this article ...
Very poor	Poor	Average	Good	Very good