I have a requirement to decrypt a password coming in source extract file in our project. Currently the same thing is happening through a SQL server stored procedure. But the SQL server will be decommissioned in near future and the whole thing needs to be done in oracle. I have tried the approach described below:-

I have tried to replicate the sql server stored procedure in Oracle. It is being able to decrypt password for the character but failed to do the same for Numerals. Please find below the anonymous block for your reference.

Existing SQL Server stored procedure
------------------------------------

CREATE PROCEDURE spSE_EncryptDecryptPwd( @encrypted_pwd_vc varchar(100) output, @password_vc varchar(100))
AS
DECLARE @encrypted_password_vc varchar(100),
@index_i int, @char_c char(1)
if @password_vc is null
BEGIN
select @encrypted_password_vc = NULL
END
else
BEGIN
select @encrypted_password_vc = ''
select @index_i = 1
WHILE (1=1)
BEGIN
select @char_c = ''
select @char_c = SUBSTRING(@password_vc, @index_i, 1)
if (ltrim(@char_c) = '')
BEGIN
BREAK
END
select @encrypted_password_vc = @encrypted_password_vc +
CHAR(ASCII(@char_c) ^ 239)
select @index_i = @index_i + 1
END
SELECT @encrypted_pwd_vc = @encrypted_password_vc
END


My replicated Oracle Stored Procedure
-------------------------------------
declare
encrypted_password_vc varchar(100);
password_vc varchar(100);
index_i int;
char_c varchar2(100);
ascii_val int;
dec_char_var int;

begin
password_vc :='¢Š›ƒ†‰ŠÝßßÜ';
if password_vc is null THEN
begin
encrypted_password_vc := null;
END;
else
begin
encrypted_password_vc := '';
index_i := 1;
WHILE (index_i<=length(password_vc))
loop
char_c := SUBSTR(password_vc, index_i, 1);
ascii_val := ascii(CONVERT(char_c,'WE8MSWIN1252'));
dec_char_var :=(ascii_val+239)-BITAND(ascii_val,239)*2;
encrypted_password_vc := encrypted_password_vc || chr(dec_char_var);
index_i := index_i + 1;
end loop;
dbms_output.put_line(encrypted_password_vc);

end;
END IF;
end;

It is giving output "Metlife" in oracle whereas the expected output(which is correctly showing in sql server) is "Metlife2003"


The source encryption algorithm is not known. However, the encryption can easily be decrypted by the sql server sp but not be oracle replica. Also, the password should return ‘Metlife2003’ instead only ‘Metlife’.

It will be great if you can provide a solution on this.

Thanks in Advance.

Regards,
Suman Roychowdhury

ORA-29275: partial multibyte character