?
Solved

Encrypt and Decrypt

Posted on 2009-04-21
1
Medium Priority
?
1,138 Views
Last Modified: 2013-12-18
I need to storage in a variable a varchar encrypted and decrypt that same variable. I get the following error: PLS-00307: too many declarations of 'DESENCRYPT' match this call in the lines:
encryptedString := sys.dbms_obfuscation_toolkit.DESEncrypt(vlsPassword, vlsKey);
and
sys.dbms_obfuscation_toolkit.DESDecrypt(encryptedString, vlsKey, decryptedString );
DECLARE 
 
		encryptedString  VARCHAR2(100); 		               
		decryptedString  VARCHAR2(100);
		vlsPassword		 VARCHAR2(20);
		vlsKey			 VARCHAR2(20);
 
 
BEGIN 
  
  vlsPassword := 'TOPSECRET';
  vlsKey 	  := 'MYKEY';
  
  encryptedString := sys.dbms_obfuscation_toolkit.DESEncrypt(vlsPassword, vlsKey); 
  
  DBMS_OUTPUT.PUT_LINE('ENCRYPTED: '|| encryptedString);
  
  sys.dbms_obfuscation_toolkit.DESDecrypt(encryptedString, vlsKey, decryptedString );
  
  DBMS_OUTPUT.PUT_LINE('DECRYPTED: '|| decryptedString);
  
END;

Open in new window

0
Comment
Question by:itzy
1 Comment
 
LVL 25

Accepted Solution

by:
lwadwell earned 500 total points
ID: 24200805
Hi itzy,

Your error, as I understand it, is due to Oracle not being able to figure out which of the overloaded procedure versions to call.  With the help of this article, http://www.databasejournal.com/features/oracle/article.php/3358671/Decrypting-Oracles-DBMSOBFUSCATIONTOOLKIT.htm, I managed to get a working version.

Note that the only way I could get it work was to ensure the length of the 'key' was 8 bytes and the length of the string to be encrypted was a multiple of 8 bytes.

lwadwell
DECLARE 
 
    encryptedString  VARCHAR2(2048);                            
    decryptedString  VARCHAR2(2048);
    vlsPassword      VARCHAR2(80)       := 'TOPSECRET';
    vlsKey           VARCHAR2(8)        := 'MYKEY';
 
 error_in_input_buffer_length EXCEPTION;
 
 PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES ***'; 
BEGIN 
  
  vlsPassword := RPAD(vlsPassword, 8 * (TRUNC(LENGTH(vlsPassword)/8) + 1));
  vlsKey      := RPAD(vlsKey, 8);
  
--  encryptedString := 
  DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT( input_string     => vlsPassword, 
                                       key_string       => vlsKey,
                                       encrypted_string => encryptedString); 
  DBMS_OUTPUT.PUT_LINE('ENCRYPTED: '|| encryptedString || '!');
  
  DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT( input_string     => encryptedString, 
                                       key_string       => vlsKey, 
                                       decrypted_string => decryptedString);
  DBMS_OUTPUT.PUT_LINE('DECRYPTED: '|| decryptedString || '!');
  
EXCEPTION
   WHEN error_in_input_buffer_length THEN
      DBMS_OUTPUT.PUT_LINE('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
END;

Open in new window

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month15 days, 18 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question