Tech

Migrate stored procedures and functions from Oracle to PostgreSQL  

Clare Louise
Clare Louise
posted on

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: 
    1. ‘REF CURSOR’ becomes ‘REFCURSOR’
    2. ‘%FOUND’ becomes ’FOUND’
    3. ‘%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. 

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;

 

You Might Also Like