create or replace function scb_encrypt ( paramIn in varchar2)
return raw is

	input_raw     raw(2000);
	key_raw       raw(2000);
	encrypted_raw raw(2000);
	encrypted_string varchar2(1000);

begin

	input_raw:=UTL_RAW.CAST_TO_RAW(CONVERT(paramIn,'AL32UTF8','US7ASCII'));

	key_raw:=UTL_RAW.CAST_TO_RAW(CONVERT('DesignStreamDs','AL32UTF8','US7ASCII'));

	encrypted_raw := dbms_crypto.Encrypt
			 (
				src => input_raw,
				typ => DBMS_CRYPTO.DES_CBC_PKCS5,
				key => key_raw
			  );

	return(UTL_RAW.CAST_TO_RAW(encrypted_raw));

end scb_encrypt;
/	
	
create or replace function scb_decrypt ( paramIn in raw)
return varchar2 is
	
	key_raw		  raw(2000);
	decrypted_raw	  raw(2000);
	decrypted_string  raw(2000);

begin

	key_raw:=UTL_RAW.CAST_TO_RAW(CONVERT('DesignStreamDs','AL32UTF8','US7ASCII'));

	decrypted_raw := dbms_crypto.Decrypt
                   	 (
				src => paramIn, 
				typ => DBMS_CRYPTO.DES_CBC_PKCS5, 
				key => key_raw
			 );

	decrypted_string := CONVERT(UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw),'US7ASCII','AL32UTF8');

	return(decrypted_string);

end scb_decrypt;
/
	

create table test
(
	col1 raw(1000)
);

insert into test (col1) values (scb_encrypt('text to encrypt'));

exec dbms_output.put_line(scb_decrypt(select col1 from test where rownum=1));

for rec in (select col1 from test) loop
  dbms_output.put_line(scb_decrypt(rec.col1));
end loop;

begin
	for rec in (select col1 from test where rownum=1) loop
		dbms_output.put_line(scb_decrypt(rec.col1));
	end loop;
end;
/

exec dbms_output.put_line(scb_decrypt(select col1 from test where rownum=1));