Calling a Stored Procedure

[scripter73]

Hi,

I need to call a stored procedure that's in an Oracle DB to perform a data extraction.

I'm accustomed to SQL server and don't know my way around Oracle or the lingo involved (packages, the extensions, etc.).

Here's the information I do know:

- HostName
- Port
- SID


A few questions:
* Do I need to "pull" a certain DB file into my web application environment area, like into (WEB-INF\lib)?
* Does anyone have a sample of how I'd call the stored proc from a servlet, etc? I'm not asking for total code, its best if I write myself, but just a point in the right direction.

Hope I've provided enough information.

Thanks so much in advance.

scripter73


Change Your Thinking, Change Your Life.

 

[sedj]

I'm usually a fan of posting code if the questioner has not posted some of their own, but I had this lying around anyway (and you did ask so nicely !!!) ...

Take a look at the notifyError() method - it calls a proc ...

import java.sql.*;
import oracle.jdbc.driver.OracleDriver;
import java.io.*;
import java.util.*;

public class ProcUtil {
    /**
     * Method getConnection.
     *
     * Get connection to database, given sid, user, passw, host etc.
     * @param host
     * @param port
     * @param SID
     * @param user
     * @param password
     * @return Connection
     * @throws SQLException
     */
  	public Connection getConnection(String host, String port, String SID, String user, String password) throws SQLException {
			 try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
				Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@" +host+":" +port +":" +SID, user, password);
				return conn;
			} catch (ClassNotFoundException cnfe) {
				System.err.println("[ProcServlet] Cannot connect to DB (ClassNotFoundException)...  - " +cnfe);
				return null;
			}
	}

	public void closeConnection(Connection connection) {
		try {
			if (connection != null && !connection.isClosed()) {
				connection.close();
			}
		} catch (SQLException sqle) {
			sqle.printStackTrace(System.err);
		}
	}

  /**
   * Method notifyError.
   *
   * Update database in event of an error
   *
   * @param host
   * @param port
   * @param SID
   * @param user
   * @param password
   * @param ok
   * @param message
   * @throws SQLException
   */
	public void notifyError(Connection connection, String host, String port, String SID, String user, String password, String ok, String message) throws SQLException {
		CallableStatement cs = null;
		//Connection connection = null;
		try {
				System.err.println("[ProcUtil] notifyError(" +host +" " +port+" " + SID+" " + user+" " +password +" " +ok +" " +message +")");
				// Prepare the query.
				//connection = getConnection(host,port, SID, user, password);
				cs = connection.prepareCall("{call order_control_package.set_order_errored(?,?)}");
				cs.setInt(1, Integer.parseInt(ok));
				cs.setString(2, message);

				cs.execute();
			} finally {
				cs.close();
				//connection.close();
			}
	}
}

 

[scripter73]

Thanks, sedj, you're so cool to do that.

A few more questions. If I wanted to see the fields appear in a web page, can I just compile the above into a class and then create a jsp that calls getConnection.

Also, although I will definitely be using username and password really soon to "login", I think I may take out temporarily so that I can test my Host, SID, and port.

So I don't need to have an Oracle-type things installed on my machine. Basically, I'm running Tomcat 4 and the Java SDK 1.2.

Sorry for all of the questions, but I'm brand new at using the Oracle database and I'm having a hard time believing the connection can be obtained this way.

Thanks for all of the great help.

scripter73


Change Your Thinking, Change Your Life.

 

[sedj]

If the stored proc returns a cursor (which is a ResultSet in Java land) you must register it as an out parameter like this :

// I think OracleTypes is in the oracle.sql package
cs.registerOutParameter(3, OracleTypes.CURSOR);

and then retrieve (after execute()) it like this :

ResultSet rs = (ResultSet)cs.getObject(3);


You must have classes12.jar (comes with Oracle) in tomcat_home/common/lib or on your CLASSPATH.

I would certainly have the ProcUtil class as a helper class that your JSP calls to get Connections and execute SQL etc ...