Hi,

I originally posted the msg below on the Oracle.server NG, and since
then, I've found that I was able to get this working by simply adding a
COMMIT at the end of my PL/SQL procedure (see msg below for code).  Now
that it's kind of working, I am trying to understand "why?".

Can anyone explain why the PL/SQL appeared to not "return" to Java when
the COMMIT was absent in the PL/SQL procedure?  

When writing PL/SQL procedures that are intended to work with Java,
should I always add a COMMIT at the end of the PL/SQL procedures?

Thanks,
Jim

======= Previous msg posted in comp.databases.oracle.server
================
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;

<======= ADDING A "COMMIT;" here seemed to eliminate the problem
==========>

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