Oracle：如何调用重载过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5869881/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: How to call an overloaded procedure?
提问by MRalwasser
How to properly call DBMS_OBFUSCATION_TOOLKIT.DESEncrypt? (without using PL/SQL if possible)
如何正确调用DBMS_OBFUSCATION_TOOLKIT.DESEncrypt？（如果可能，不使用 PL/SQL）
select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt('x','y') from dual;
doesn't work because DESEncrypt is overloaded:
不起作用，因为 DESEncrypt 过载：
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?
有没有办法选择 DESENCRYPT（可能是 VARCHAR2 变体）的一种实现来避免此错误？
回答by Tony Andrews
In Oracle 11G you can use named notation like this:
在 Oracle 11G 中，您可以像这样使用命名符号：
select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>'x',key_string=>'y')
from dual;
I don't think it is possible to call these functions unambiguously in earlier versions of Oracle, except by creating a wrapper function and calling that instead.
我认为在早期版本的 Oracle 中不可能明确地调用这些函数，除非创建一个包装函数并调用它。
回答by Harrison
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
M5??w5Z
note, your key needs to be at least 8 bytes:
请注意，您的密钥至少需要 8 个字节：
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.
ORA-28234: 密钥长度太短 ORA-06512: 在 "SYS.DBMS_OBFUSCATION_TOOLKIT_FFI", 第 21 行 ORA-06512: 在 "SYS.DBMS_OBFUSCATION_TOOLKIT", 第 126 行 28234. 00000 - "Cause 指定的密钥长度：太短对于算法来说太短了。DES 需要至少 8 个字节的密钥。三重 DES 在两密钥模式下需要至少 16 个字节的密钥，在三密钥模式下需要至少 24 个字节。*操作：指定更长的密钥。
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
由于您使用的是 10g，您可能需要使用 DBMS_CRYPTO 包 http://www.stanford.edu/dept/itss/docs/oracle/10g/network.101/b10773/apdvncrp.htm
回答by tbone
here's a crypt/decrypt using the older dbms_obfuscation_toolkit:
这是使用旧的 dbms_obfuscation_toolkit 的加密/解密：
create or replace function crypt(p_str in varchar2, p_key in varchar2) return varchar2
as
l_data varchar2(255);
begin
l_data := rpad(p_str, (trunc(length(p_str)/8)+1)*8,chr(0));
dbms_obfuscation_toolkit.DESEncrypt
(input_string=>l_data,
key_string=>p_key,
encrypted_string=>l_data);
return l_data;
end;
And for decrypt:
对于解密：
create or replace function decrypt(p_str in varchar2, p_key in varchar2) return varchar2
as
l_data varchar2(255);
begin
dbms_obfuscation_tookit.DESDecrypt
(input_string=>p_str,
key_string=>p_key,
decrypted_string=>l_data);
return rtrim(l_data,chr(0));
end;
And usage:
和用法：
declare
l_data varchar2(100);
l_key varchar2(100);
l_encrypted varchar2(100);
l_decrypted varchar2(100);
begin
l_data := 'This is secret!!!';
l_key := 'My secret key';
dbms_output.put_line(l_data);
l_encrypted := crypt(l_data, l_key);
dbms_output.put_line(l_encrypted);
l_decrypted := decrypt(l_encrypted, l_key);
dbms_output.put_line(l_decrypted);
end;
