Directory Image
This website uses cookies to improve user experience. By using our website you consent to all cookies in accordance with our Privacy Policy.

Migrate stored procedures and functions from Oracle to PostgreSQL

Author: Simon Hopes
by Simon Hopes
Posted: Oct 24, 2019
must converted

One of the most complicated parts of database migration is converting code of stored procedures and functions into the target format. The process of migrating Oracle PL/SQL code to PostgreSQL may be represented as sequence of these steps:

  1. RETURN clause within a function prototype must be converted into ‘RETURNS’
  2. In PostgreSQL ‘DECLARE’ keyword must be used at the begin of variables definition section
  3. PostgreSQL does not need Oracle’s table dual, but since it is intensively used by Oracle users, it may be created before migration
  4. PostgreSQL requires language specification at the end of each function using ‘$$ LANGUAGE plpgsql;’ pattern
  5. Oracle packages should be migrated into schemas
  6. Oracle allows sharing variables within a package, in PostgreSQL temporary table can be used for the same purpose
  7. Stored procedures cannot be created in PostgreSQL, therefore every Oracle procedure must be converted into function
  8. Oracle FORALL cycles must be replaced by FOR … LOOP in PostgreSQL
  9. To convert Oracle cursors into PostgreSQL syntax the following replacements are required:

a. ‘REF CURSOR’ becomes ‘REFCURSOR’

b. ‘%FOUND’ becomes ’FOUND’

c. ‘%NOTFOUND’ becomes ‘NOT FOUND’

10. Oracle system functions must be converted into PostgreSQL equivalents according to the table below

Oracle

PostgreSQL

DAY($a) or DAYOFMONTH($a)

EXTRACT(day from date($a))::integer

HOUR($a)

EXTRACT(hour from $a)

INSTR($str1, $str2)

POSITION($str2 in $str1).

LCASE

LOWER

LOCATE($str1,$str2)

POSITION($str1 in $str2)

MINUTE($a)

EXTRACT(minute from $a)

MONTH($a)

EXTRACT(month from $a)

NVL($a, replace_with)

COALESCE($a, replace_with)

REGEXP_LIKE($string, $pattern)

$string LIKE $pattern

SECOND($a)

EXTRACT(second from $a)

SUBSTR($string, $from, $for)

SUBSTRING($string, $from, $for)

SYSDATE

CURRENT_DATE

UCASE

UPPER

WEEK($a)

EXTRACT(week from $a)

YEAR($a)

EXTRACT(year from date($a))

Handling Errors

While Oracle allows using predefined exception, PostgreSQL does not support this feature. All such exceptions must be replaced by custom error codes. Oracle stores error code in SQLCODE, PostgreSQL uses SQLSTATE for the same purpose.

Various data types can be treated easily by approximating the nearest type from the target database to keep up data integrity. When a source database supports advanced data formats (e.g. sub-record), however the target database doesn't, amending the applications with the database is important. Likewise, in the event the source database supports different encoding in every column for a specific table yet the target database doesn't, the programs using the database must be thoroughly evaluated.

Oracle has two macros EXCEPTION_INIT and RAISE_APPLICATION_ERROR for more convenient error handling. EXCEPTION_INIT associates a user-defined error name with an Oracle error code. RAISE_APPLICATION_ERROR throws error via user-defined error code and message. It should be replaces by ‘RAISE … USING ERRCODE = …’ in PostgreSQL.

The following example of migration from Oracle to PostgreSQL illustrates rules listed above. Assume we have Oracle stored procedure defined as:

FUNCTION Process_iName

(

v_i_name IN VARCHAR2

) RETURN VARCHAR2 AS

invalid_name EXCEPTION;

PRAGMA EXCEPTION_INIT ( invalid_name, -20001 );

BEGIN

IF (!LENGTH( v_i_name ) RAISE_APPLICATION_ERROR(

-20001, 'ERR: i_name is invalid.' );

END IF;

...

EXCEPTION

WHEN invalid_name THEN

DBMS_OUTPUT.PUT_LINE(

TO_CHAR(SQLERRM(-20001)) );

END;

It should be migrate to PostgreSQL as follows:

CREATE OR REPLACE FUNCTION Process_iName

(

v_i_name IN VARCHAR

) RETURNS VARCHAR AS $$

BEGIN

IF (!LENGTH( v_i_name ) RAISE 'ERR: i_name is invalid.'

USING ERRCODE = '20001’;

END IF;

...

EXCEPTION

WHEN SQLSTATE '20001' THEN

PERFORM DBMS_OUTPUT.PUT_LINE(

SQLSTATE || ':' || SQLERRM );

END;

$$ LANGUAGE plpgsql;

Visit http://www.convert-in.com/docs/ora2pgs/intro.htm for more information about migration from Oracle to PostgreSQL.

About the Author

With extensive research and study, Simon passionately creates blogs on divergent topics. His writings are unique and utterly grasping owing to his dedication in researching for distinctive topics.

Rate this Article
Leave a Comment
Author Thumbnail
I Agree:
Comment 
Pictures
Author: Simon Hopes
Professional Member

Simon Hopes

Member since: Feb 13, 2017
Published articles: 439

Related Articles