Re: Problem calling stored procedure from Java

From: ohaya <ohaya_at_cox.net>
Date: Tue, 21 Jun 2005 01:35:09 -0400
Message-ID: <42B7A70D.BCB4BFE9@cox.net>

ohaya wrote:
>
> >
> > put a System.out.println(e) in the catch block, that should help
> > debugging.
>
> Chet,
>
> I just tried that, a couple of different ways actually, but still get
> the same messages, and no additional messages. Here's the new Java
> code:
>
> 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:CT","XX_OWNER", "XXXXX");
> System.out.println("In Provider/initialize: Finished opening
> connection to Oracle DB...");
>
> // con = connectionPool.getConnection();
> 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");
> try {
> proc.execute();
> } catch (Exception x){
> System.out.println("Inside exception: " + x);
> }
> System.out.println("In Provider/initialize: Just returned
> from proc.execute()");
> }
> finally
> {
> try
> {
> proc.close();
> }
> catch (SQLException e) {
> System.out.println("Error in doEncryption: " + e);
> }
> catch (Exception f) {
> System.out.println("Error in doEncryption/Exception:" + f);
> }
> conn.close();
> }
> }
>
> Jim

Hi,

I think that I've been able to fix this, or at least get it to prevent hanging. What I ended up doing was add a "COMMIT" at the end of the procedure. The working procedure is now:

CREATE or REPLACE PROCEDURE encrypt_password (key_string IN varchar2, uname IN varchar2, password IN 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 LHDB set secure_pwd = encrypted_string WHERE username = uname;
	COMMIT;

END; Once I did that, when I run the Java program, it goes through the rest of the code.

Can anyone explain this (in as much detail as possible)?

Thanks,
Jim Received on Tue Jun 21 2005 - 00:35:09 CDT