Hi there,
Try this out.
/**
* @(#) OracleDBServlet.java
*
* (C) Copyright Satish Kumar Kasala
*
*/
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import oracle.jdbc.driver.*;
public class OracleDBServlet extends HttpServlet{
Connection con;
public void init(ServletConfig sc) throws ServletException
{
super.init(sc);
}
public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
{
res.setContentType("text/html");
ServletOutputStream out = res.getOutputStream();
String TestValue=req.getParameter("TestValue");
String ServerName = req.getParameter("ServerName");
out.println("<html>");
out.println("<head><title>Oracle Database Servlet</title></head>");
out.println("<body>");
//Establish connection to the Database (Oracle JDBC)
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String ConnectString = "jdbc
racle:thin:@(description=(address=(host="+ServerName+")(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))";
con = DriverManager.getConnection (ConnectString,"scott", "tiger");
}
catch(Exception e){
out.println(e.toString());
}
//Execute SQL Statement
try{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from tab");
out.println("<h3>Table Names : </h3>");
while(rs.next()){
String TableName = rs.getString(1);
out.println("<P>"+TableName+"</p>");
}
}
catch(Exception e){
out.println(e.toString());
}
//Calling a Stored Procedure
try{
CallableStatement cs = con.prepareCall("{call TestProcedure(?)}");
cs.setString(1,TestValue);
cs.execute();
}
catch(Exception e){
out.println(e.toString());
}
//Using REFCURSOR
try{
CallableStatement cs = con.prepareCall("{? = call PackageName.ProcedureName(?)}");
cs.registerOutParameter (1, OracleTypes.CURSOR);
cs.setString(2,TestValue);
cs.execute ();
ResultSet rs = (ResultSet)cs.getObject (1);
while(rs.next()){
String ReturnValue=rs.getString("1");
out.println("<p>"+ReturnValue+"</p>");
}
}
catch(Exception e) {
out.println(e.toString());
}
out.println("</body></html>");
try{
con.close();
}
catch(Exception e){
out.println(e.toString());
}
}
}
the difference to be noted here is the connect string used to connect to the oracle database.
String ConnectString = "jdbc
racle:thin:@(description=(address=(host="+ServerName+")(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))";
ServerName : specify the name of the box on which oracle db is running eg: oracleserver
sid: specify the oracle instance name.
Hope this helps
Satish
racle:thin:@hostname :portnumber:dbname","user","password"
