Problem calling stored procedure from Java

From: ohaya <ohaya_at_cox.net>
Date: Mon, 20 Jun 2005 23:43:53 -0400
Message-ID: <42B78CF9.93C8297B@cox.net>

Hi,

I'm trying to call a simple Oracle stored procedure (which I created and which uses the DBMS_OBFUSCATION_TOOLKIT) from my Java code, but am having problems.

Here's my stored procedure:

CREATE or REPLACE PROCEDURE encrypt_password (key_string varchar2, uname varchar2, password varchar2) IS

input_string VARCHAR2(16) := to_char(password); encrypted_string VARCHAR2(2048);

BEGIN

	dbms_obfuscation_toolkit.DESEncrypt(
				input_string => input_string,
				key_string => key_string,
				encrypted_string => encrypted_string);
	UPDATE XXDB set secure_pwd = encrypted_string WHERE username = uname;

END; The Java code looks like:

public static void doEncryption(String enKey, String uName, String pwd) throws SQLException {

        	Connection conn = null;
        	CallableStatement proc = null;

       try


       {

        // Load the Oracle JDBC driver
        DriverManager.registerDriver(new

oracle.jdbc.driver.OracleDriver());

        System.out.println("In Provider/initialize: About to open connection to Oracle DB...");

	    // Open the connection
        conn = DriverManager.getConnection
("jdbc20:oracle:thin:@jimnew:1521:XX","XX_OWNER", "XXXXXX");
       	System.out.println("In Provider/initialize:  Finished opening
connection to Oracle DB...");
       	
          proc = conn.prepareCall("{ call encrypt_password(?, ?, ?) }");
          proc.setString(1, enKey);
          proc.setString(2, uName);
          proc.setString(3, pwd);
          System.out.println("In Provider/initialize:  Finished setting
parameters - about to execute procedure");
          proc.execute();
          System.out.println("In Provider/initialize:  Just returned
from proc.execute()");
       }
       finally


       {

          try
          {
             proc.close();
          }
          catch (SQLException e) {}
          conn.close();
       }

    }

The output I'm getting is as follows:

.
.

In Provider/initialize: About to open connection to Oracle DB...
In Provider/initialize:  Finished opening connection to Oracle DB...
In Provider/initialize:  Finished setting parameters - about to execute

procedure

There is no other output after that last line, i.e., it seems to have done the "proc.execute()" but never returned. I don't see the last println ("Just returned from proc.execute()"), and I don't get any errors!

Can anyone tell me what the problem might be? Or, how I might try to diagnose why it's not returning from the "proc.execute()"?

Thanks,
Jim Received on Mon Jun 20 2005 - 22:43:53 CDT