Stored Procedure not working from Forms 6

[NewtUK]

Hi, I wonder if anyone can help with this.

We have a stored procedure called InsertStaff that inserts records into several tables and calls another procedure called encryptPassword that has been defined in the same package.

When calling the InsertStaff procedure from SQL*Plus or via a Java application, there is no problem using this, everything works fine. However, when I call the InsertStaff procedure from Forms 6, this procedure runs fine, but the contained call to encryptPassword does not operate.

I am not getting any errors sent back (though this doesn't mean that there aren't any being generated!). Is there somewhere that will tell me what is going on here, or is there an issue nesting procedure calls that I am not aware of (though if this is the case, why is it okay running from SQL*Plus?).

Many thanks in advance for any help.

Regards



Rich

 

[sem]

How do you know it "does not operate"? Are you sure that you really call stored procedure, not the local one with the same signature? Try to debug it.

Regards, Dima

http://seminihin.narod.ru

 

[NewtUK]

The encryptPassword procedure places the encrypted value of the plaintext password into a table. This functions fine when called from SQL*Plus, but called from Forms places the original plain text value into the table.

The encryptPassword procedure is defined as follows:

PROCEDURE encryptPassword(plainText IN varchar2, encryptedText IN OUT varchar2)
AS
result raw(16);
BEGIN

result := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5(input_string=> plainText));
encryptedText := lower(rawtohex(result));

END encryptPassword;

The resulting encrypted password is assigned to a variable in the InsertStaff procedure that is then used to insert the value into the table. The original plaintext value is not passed to the InsertStaff procedure - it only goes through this one.

We do not have any local versions of these procedures.

Rich

 

[lewisp]

Are you performing a commit at any point?

 

[NewtUK]

Not at this point, no.

However, testing the system this morning has provided the correct results - the password encrypted. I guess I can only put this down to user incompetence.

Thanks for your help guys, and apologies for wasting your time. :-(


Rich