Some Oracle Workarounds in Postgres Plus Advanced Server 9.0

Today, I though to mention some of Oracle workarounds which can be use for Migration of Oracle Database to PPAS 9.0(EnterpriseDB).

1. UTL_RAW.CAST_TO_RAW
This is a Oracle Packaged Function, which can be use to convert VARCHAR2 value into RAW Value. Lets see its example in Oracle:

SQL> select utl_raw.cast_to_raw('TESTING TESTING') from dual;

UTL_RAW.CAST_TO_RAW('TESTINGTESTING')
--------------------------------------------------------------------------------
54455354494E472054455354494E47

Since PPAS 9.0, doesn't have this package in 9.0, therefore user can use postgresql function decode() for converting of varchar datatype to RAW/bytea data. Following is an example:

edb=# select decode('TESTING TESTING','escape') from dual;
              decode              
----------------------------------
 \x54455354494e472054455354494e47
(1 row)

which is a same output.

2. UTL_RAW.CAST_TO_VARCHAR2

Using this packaged function user can convert raw value into a value of data type VARCHAR2 with the same number of data bytes. Following is an example:

SQL> select utl_raw.cast_to_varchar2(utl_raw.cast_to_raw('TESTING TESTING')) from dual;

UTL_RAW.CAST_TO_VARCHAR2(UTL_RAW.CAST_TO_RAW('TESTINGTESTING'))
--------------------------------------------------------------------------------
TESTING TESTING

For this function, PPAS 9.0 is having encode() function which can be use to convert raw/bytea value to VARCHAR/TEXT. Following is an example:

edb=# select encode(decode('TESTING TESTING','escape'),'escape') from dual;
     encode      
-----------------
 TESTING TESTING
(1 row)

For more details of above two function, following link can be use:

http://www.enterprisedb.com/docs/en/9.0/pg/functions-binarystring.html

Using above basic function, user can immitate behavior of UTL_RAW Package in PPAS/PostgreSQL.

3. DBMS_OBFUSCATION.DESEncrypt & DBMS_OBFUSCATION.DESDecrypt Procedure
The DESEncrypt/DESDecrypt procedures generates the encrypted/decrypted form of the input data. It uses DES algorithm to encrypt data. Following is an Oracle Example:

DECLARE
   input_string        VARCHAR2(16) := 'tigertigertigert';
   raw_input           RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
   key_string          VARCHAR2(8)  := 'scottsco';
   raw_key             RAW(128) := UTL_RAW.CAST_TO_RAW(key_string);
   encrypted_raw               RAW(2048);
   encrypted_string            VARCHAR2(2048);
   decrypted_raw               RAW(2048);
   decrypted_string            VARCHAR2(2048); 
   error_in_input_buffer_length EXCEPTION;
   PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -28232);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING 
EXCEPTION ***';
   double_encrypt_not_permitted EXCEPTION;
   PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -28233);
   DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

-- 1. Begin testing raw data encryption and decryption
   BEGIN
   dbms_output.put_line('> ========= BEGIN TEST =========');
   dbms_output.put_line('> Raw input                        : ' || 
                 UTL_RAW.CAST_TO_VARCHAR2(raw_input));
   BEGIN 
      dbms_obfuscation_toolkit.DESEncrypt(input => raw_input, 
               key => raw_key, encrypted_data => encrypted_raw );
      dbms_output.put_line('> encrypted hex value              : ' || 
               rawtohex(encrypted_raw));
      dbms_obfuscation_toolkit.DESDecrypt(input => encrypted_raw, 
               key => raw_key, decrypted_data => decrypted_raw);
      dbms_output.put_line('> Decrypted raw output             : ' || 
                    UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw));
      dbms_output.put_line('>  ');      
      if UTL_RAW.CAST_TO_VARCHAR2(raw_input) = 
                    UTL_RAW.CAST_TO_VARCHAR2(decrypted_raw) THEN
         dbms_output.put_line('> Raw DES Encyption and Decryption successful');
      END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');
END;

Oracle Output:

========= BEGIN TEST =========
 Raw input      : tigertigertigert
 encrypted hex value     : 5AAB8C0D278AD75CA1968790D00FD75A
 Decrypted raw output     : tigertigertigert

 Raw DES Encyption and Decryption successful

PL/SQL procedure successfully completed.

In PPAS/PostgreSQL, user can use encrypt()/decrypt() function, which is a part of pgcrypto module supplied with PostgreSQL/PPAS. Following is an example in PPAS 9.0:

DECLARE
   input_string        VARCHAR2(16) := 'tigertigertigert';
   raw_input           RAW(128) := decode(input_string,'escape');
   key_string          VARCHAR2(8)  := 'scottsco';
   raw_key             RAW(128) := decode(key_string,'escape');
   encrypted_raw               RAW(2048);
   encrypted_string            VARCHAR2(2048);
   decrypted_raw               RAW(2048);
   decrypted_string            VARCHAR2(2048); 
   error_in_input_buffer_length EXCEPTION;
   PRAGMA EXCEPTION_INIT(error_in_input_buffer_length, -20010);
   INPUT_BUFFER_LENGTH_ERR_MSG VARCHAR2(100) :=
    '*** DES INPUT BUFFER NOT A MULTIPLE OF 8 BYTES - IGNORING 
EXCEPTION ***';
   double_encrypt_not_permitted EXCEPTION;
   PRAGMA EXCEPTION_INIT(double_encrypt_not_permitted, -20012);
   DOUBLE_ENCRYPTION_ERR_MSG VARCHAR2(100) :=
    '*** CANNOT DOUBLE ENCRYPT DATA - IGNORING EXCEPTION ***';

-- 1. Begin testing raw data encryption and decryption
   BEGIN
   dbms_output.put_line('> ========= BEGIN TEST RAW DATA =========');
   dbms_output.put_line('> Raw input                        : ' || 
                 encode(raw_input,'escape'));
   BEGIN 
     select encrypt(raw_input,raw_key,'des') into encrypted_raw from dual;
      dbms_output.put_line('> encrypted hex value              : ' || 
               encode(encrypted_raw,'hex'));

     select decrypt(encrypted_raw,raw_key,'des') into decrypted_raw from dual;
      dbms_output.put_line('> Decrypted raw output             : ' || 
                    encode(decrypted_raw,'escape'));
      dbms_output.put_line('>  ');      
      if encode(raw_input,'escape') = 
                    encode(decrypted_raw,'escape') THEN
         dbms_output.put_line('> Raw DES Encyption and Decryption successful');
      END if;
   EXCEPTION
      WHEN error_in_input_buffer_length THEN
             dbms_output.put_line('> ' || INPUT_BUFFER_LENGTH_ERR_MSG);
   END;
   dbms_output.put_line('>  ');
END;

Output:

========= BEGIN TEST RAW DATA =========
 Raw input                        : tigertigertigert
 encrypted hex value              : 5aab8c0d278ad75ca1968790d00fd75a9931b6116d9d4346
 Decrypted raw output             : tigertigertigert
  
 Raw DES Encyption and Decryption successful  

EDB-SPL Procedure successfully completed

4. UTL_HTTP.REQUEST Function.
Oracle provides UTL_HTTP.RQUEST function, which user can use to access data on the Internet. This function returns 2000 bytes of the data retrieved from the given URL.
Since PPAS/PostgreSQL doesn't have direct support for this Function, so user can write plperlu function to get same features. Following is a small function/example:

CREATE OR REPLACE FUNCTION view_web_source(text) returns text   
as  
$$  
use LWP::Simple;
use LWP::UserAgent;  
$ENV{PERL_LWP_SSL_VERIFY_HOSTNAME}=0; 
my $ua = LWP::UserAgent->new;   
$pageURL=$_[0];  
$URLsource=get($pageURL);  
my $req = HTTP::Request->new('GET', $pageURL);  
my $res = $ua->request($req);  
if ($res->status_line == "200 OK")  
{ return $URLsource; }  
 else  
{  
   return $res->status_line;  
}  
$$ language plperlu;  
  

CREATE OR REPLACE FUNCTION url_request(url text) returns text
as
$$
select substring(view_web_source($1) from 1 for 2000);
$$ language sql;

Oracle Output:

PPAS output snapshot: