Tag Archives: text

11g, Oracle, Oracle 10g, oracle 9i, SQL

Oracle – Encoding and Decoding Oracle data with UTL_ENCODE Package

Leave a comment

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

hp-ux, itanium, linux, unix

Shell script to perform string replacement in multiple files for UNIX and LINUX:

Leave a comment

As a DBA, regardless of RDBMS type, you will come across the need to replace text strings in dozens if not hundreds of files to facilitate the completion of your job. In this article we will cover the use of bash and perl scripts to perform text replacement of multiple files within a UNIX or LINUX environment.

1. Logon to your UNIX or LINUX server as the owner of the files you want to update or a user which has permission to update these files.

2. In this procedure we will create a file named files.txt containing a listing of all files we wish to update.

mylinux:> more files.txt
./test1.txt
./test2.txt
./test3.txt
./test4.txt

3. Next create a file called update.sh with the following text.

dt=`date “+%m%d%Y”` # Gets current date.
cat ./files.txt|while read line # Reads in all files from files.txt one line at a time.
do # Opens a loop
cp $line $line$dt # copies original file to backup with file_name+date.
ls $line |xargs perl -pi -e ‘s/{old_string}/{new_string}/g’ # if found replace old_string with new_stirng
done # ends loop

4. Change permissions on the update.sh to 770, so it will execute.

mylinx:>:>chmod 770 update.sh
mylinx:>:>

5. View the contents of one of the files in you files.txt file.

mylinx:>:>cat test*
one
one
one
one
mylinx:>:>

6. In this example, all of the files contain the text “one” which we will replace with the string “two”. Thus your update.sh file will look like the example below.

dt=`date “+%m%d%Y”`
cat ./files.txt|while read line
do
cp $line $line$dt
ls $line |xargs perl -pi -e ‘s/one/two/g’
done

7. Execute the update.sh file with the command: ./update.sh.

mylinx:>:>./update.sh
mylinx:>:>

8. Now cat all files named test*

mylinx:>:>cat test*
two
two
two
two
mylinx:>

As you can see all strings of “one” have been replaced with the string “two”. This completes replacement of strings in UNIX and LINUX.

Larry Catt, OCP 9i, 10g
oracle@allcompute.com
www.allcompute.com