Here is the same example from
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:791026226790 <code>
written as procedures. Bind variables are simply replaced with formal parameters (thats the only change I've made)...
Here it is:
ops$tkyte@DEV816> variable x varchar2(25)
ops$tkyte@DEV816> exec :x := 'How Now Brown Cow';
PL/SQL procedure successfully completed.
X
--------------------------------
How Now Brown Cow
ops$tkyte@DEV816> create or replace procedure crypt( p_str in out varchar2 )
2 as
3 l_data varchar2(255);
4 begin
5 l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
6
7 dbms_obfuscation_toolkit.DESEncrypt
8 ( input_string => l_data,
9 key_string => 'MagicKey',
10 encrypted_string=> p_str );
11 end;
12 /
Procedure created.
ops$tkyte@DEV816> create or replace procedure decrypt( p_str in out varchar2 )
2 as
3 l_data varchar2(255);
4 begin
5 dbms_obfuscation_toolkit.DESDecrypt
6 ( input_string => p_str,
7 key_string => 'MagicKey',
8 decrypted_string=> l_data );
9
10 p_str := rtrim( l_data, chr(0) );
11 end;
12 /
Procedure created.
ops$tkyte@DEV816> set autoprint on
ops$tkyte@DEV816> exec crypt( :x )
PL/SQL procedure successfully completed.
X
--------------------------------
Rr=p]w]pSWq:]PtJ?T,
ops$tkyte@DEV816> exec decrypt( :x )
PL/SQL procedure successfully completed.
X
--------------------------------
How Now Brown Cow
follow up to comment one. Well, I guess I would write a FUNCTION then. Something like this:
2 function crypt( p_str in varchar2 ) return varchar2
3 as
4 l_data varchar2(255);
5 begin
6 l_data := rpad( p_str, (trunc(length(p_str)/8)+1)*8, chr(0) );
7
8 dbms_obfuscation_toolkit.DESEncrypt
9 ( input_string => l_data,
10 key_string => 'MagicKey',
11 encrypted_string=> l_data );
12
13 return l_data;
14 end;
15 /
Function created.
scott@DEV816>
scott@DEV816> create or replace
2 function decrypt( p_str in varchar2 ) return varchar2
3 as
4 l_data varchar2(255);
5 begin
6 dbms_obfuscation_toolkit.DESDecrypt
7 ( input_string => p_str,
8 key_string => 'MagicKey',
9 decrypted_string=> l_data );
10
11 return rtrim( l_data, chr(0) );
12 end;
13 /
Function created.
scott@DEV816> column decrypt format a30
scott@DEV816> drop table t;
Table dropped.
scott@DEV816> create table t ( data varchar2(32) );
Table created.
scott@DEV816> insert into t values ( crypt( 'Hello World' ) );
1 row created.
scott@DEV816> select data, decrypt(data) decrypt from t;
DATA DECRYPT
-------------------------------- ------------------------------
7??-,?P I{CK??! Hello World
scott@DEV816>