- Views: 13
- Report Article
- Articles
- Technology & Science
- Communication
Migrate stored procedures and functions from Oracle to PostgreSQL
Posted: Oct 24, 2019
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:
- RETURN clause within a function prototype must be converted into ‘RETURNS’
- In PostgreSQL ‘DECLARE’ keyword must be used at the begin of variables definition section
- PostgreSQL does not need Oracle’s table dual, but since it is intensively used by Oracle users, it may be created before migration
- PostgreSQL requires language specification at the end of each function using ‘$$ LANGUAGE plpgsql;’ pattern
- Oracle packages should be migrated into schemas
- Oracle allows sharing variables within a package, in PostgreSQL temporary table can be used for the same purpose
- Stored procedures cannot be created in PostgreSQL, therefore every Oracle procedure must be converted into function
- Oracle FORALL cycles must be replaced by FOR … LOOP in PostgreSQL
- 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.
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.