Database Technologies

Till PPAS 9.0, UTL_SMTP package has no specific named Exceptions for Transient_error, Invalid_operation and Permanent Error. However, Oracle provide following types of named exceptions for SMTP: 1. INVALID_OPERATION: Raised when an invalid operation is made. In other words, calling API other than write_data(), write_raw_data() or close_data() after open_data() is called, or calling write_data(), write_raw_data() or close_data() without first calling open_data(). 2. TRANSIENT_ERROR: Raised when receiving a reply code in 400 range. 3. PERMANENT_ERROR: Raised when receiving a reply code in 500 range. Oracle users who use SMTP packages, they also use above exceptions extensively to track the SMTP Error/Message and perform some handling on basis of exception. Since, till now PPAS doesn't have these exceptions, therefore people stuck on finding workaround for such exception. To make this easy, I did some research and made following workaround, which user can use in PPAS for

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 conver