Oracle RDBMS provides the ability to encode and decode data through the package UTL_ENCODE. This functionality can be extremely helpful in the transmission of data over open networks where plan text transmission would not be wanted. In this article we will review the use of encoding plain text and decoding the string produced.
1. Logon to your Oracle database server as the Oracle software owner.
2. Logon to SQLPLUS with SYSDBA privileges.
mylinux:> sqlplus '/ as sysdba'
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jun 30 20:00:09 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
3. Execute the following PL/SQL block to produce the encoded text string for: Test encode procedure.
set serveroutput on
declare
v_text varchar2(100):='Test encode procedure';
v_encoded_text varchar2(200);
v_ct number;
begin
v_encoded_text:=utl_encode.text_encode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
dbms_output.put_line(v_encoded_text);
end;
/
Output from SQL*PLUS
SQL> declare
2
3 v_text varchar2(100):='Test encode procedure';
4 v_encoded_text varchar2(200);
5 v_ct number;
6
7 begin
8
9 v_encoded_text:=utl_encode.text_encode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
10
11 dbms_output.put_line(v_encoded_text);
12
13 end;
14 /
VGVzdCBlbmNvZGUgcHJvY2VkdXJl
PL/SQL procedure successfully completed.
SQL>
4. The PL/SQL block executed generated the encoded version of ‘Test encode procedure’ as VGVzdCBlbmNvZGUgcHJvY2VkdXJl. Now we will decode the string with the text decode function.
set serveroutput on
declare
v_text varchar2(100):='VGVzdCBlbmNvZGUgcHJvY2VkdXJl';
v_encoded_text varchar2(200);
v_ct number;
begin
v_encoded_text:=utl_encode.text_decode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
dbms_output.put_line(v_encoded_text);
end;
/
Output from SQL*PLUS:
SQL>
SQL> declare
2
3 v_text varchar2(100):='VGVzdCBlbmNvZGUgcHJvY2VkdXJl';
4 v_encoded_text varchar2(200);
5 v_ct number;
6
7 begin
8
9 v_encoded_text:=utl_encode.text_decode(v_text,'WE8ISO8859P1',
UTL_ENCODE.BASE64);
10
11 dbms_output.put_line(v_encoded_text);
12
13 end;
14 /
Test encode procedure
PL/SQL procedure successfully completed.
SQL>
The decode function displays the original text presented to the encode function.
Larry J. Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com