Search code examples
sqloracle-databaseencryptionplsqlora-06553

Oracle: How to call an overloaded procedure?

How to properly call DBMS_OBFUSCATION_TOOLKIT.DESEncrypt? (without using PL/SQL if possible)

select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt('x','y') from dual;

doesn't work because DESEncrypt is overloaded:

ORA-06553: PLS-307: Too many declarations of "DESENCRYPT" match this call
06553. 00000 -  "PLS-%s: %s"
*Cause:    
*Action:

Is there a way to choose one implementation of DESENCRYPT (possibly the VARCHAR2 variant) to avoid this error?

Solution

  • here you go, just let it know which overload to use by supplying the param names!

    select DBMS_OBFUSCATION_TOOLKIT.DesEncrypt(INPUT_STRING=>'11112abc',KEY_STRING=>'4578ccde') 
from dual ;

    returns

    M5��w5Z

    note, your key needs to be at least 8 bytes:

    ORA-28234: key length too short ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", line 21 ORA-06512: at "SYS.DBMS_OBFUSCATION_TOOLKIT", line 126 28234. 00000 - "key length too short" *Cause: The key specified is too short for the algorithm. DES requires a key of at least 8 bytes. Triple DES requires a key of least 16 bytes in two-key mode and 24 bytes in three-key mode. *Action: Specify a longer key.

    You may always try it with a wrapper function (as tony suggested)

    create or replace
function DesEncrypt(pinputString IN VARCHAR2 , pKeyString in VARCHAR2) RETURN varchar2
IS
BEGIN
return DBMS_OBFUSCATION_TOOLKIT.DesEncrypt(INPUT_STRING=>INPUTSTRING,KEY_STRING=>KEYSTRING);
END DesEncrypt;
/
select DesEncrypt('11112abc' , '4578ccde') from dual ;

    Since you are on 10g, you may want to use the DBMS_CRYPTO package http://www.stanford.edu/dept/itss/docs/oracle/10g/network.101/b10773/apdvncrp.htm