Porting applications from Oracle to PostgreSQL

Alexandru Gyulai PostgreeSQL, Oracle E Business Suite

Share

If you are planning to migrate a database application from Oracle to PostgreSQL, this article can help you to understand and solve some of the issues occurring in such a process. PostgreSQL has recently become feature-complete in many areas and therefore porting projects from Oracle might be a feasible option in much more cases. Oracle and PostgreSQL both conform to standard SQL. However, they contain several extensions and implementation details that differentiate one from the other. Here is table with some of the differences and how to deal with them.

Oracle 10g Postgres 8.4 Observations
char(size) char(size) Oracle treats a character value with a length of zero as null. This “weird and inconsistent” behavior is not true for PostgreSQL. Consequently, the ” string must be replaced with NULL.
number(p,s) NUMERIC(precision, scale) For performance reasons, number (p, s) should be replaced with INTEGER, when scale is zero.
varchar2 varchar or text
date date SYSDATE → current_timestamp or now()
clob text
sysdate current_timestamp
decode (expression,
search, result
[, search, result]…
[, default] ) 		CASE expression
WHEN value THEN result
[WHEN …]
[ELSE result]
END
or
CASE WHEN condition
THEN result
[WHEN …]
[ELSE result]
END		 The general rule is that NULL is not equal to NULL. But, as an exception, in a DECODE function Oracle considers two nulls to be equivalent. If expression is null, then Oracle returns the result of the first search that is also null (nulls are considered equal in this comparison). This is not true for the SQL-compliant conditional expressions available in PostgreSQL. Therefore, based on the possible values for ‘expression’ and ‘search’, the decode replacement is one of the following:

- CASE expression WHEN search...
- CASE WHEN expression IS NULL...
- CASE WHEN expression = search OR expression IS NULL and search IS NULL...
Outer JOIN Outer JOIN PostgreSQL only supports the SQL-standard outer join syntax. But it is quite easy to translate from the Oracle-specific syntax.
For example,
SELECT * FROM a, b WHERE a.x = b.y(+)
becomes
SELECT * FROM a LEFT JOIN b ON a.x = b.y
Analytic Functions Window Functions Both the Oracle Analytic functions and Postgres Window functions allow you to rank a data set and work with its top-ranked or bottom-ranked rows. A lot of extra work is required because the implementations are very different. Moreover, in PostgreSQL, unlike Oracle, if there is a need to filter or group rows after the window calculations are performed, you have to use a sub-select like in the following simple example:

SELECT depname, empno, salary, enroll_date FROM(SELECT depname, empno, salary, enroll_date, rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos FROM empsalary) AS ss WHERE pos < 3
Functions (Oracle’s PL/SQL language) Functions (PostgreSQL’s PL/pgSQL language) Need to register a new language:
CREATE LANGUAGE plpgsql;
In PostgreSQL, the function body is considered to be a string literal, so you need to use quote marks or dollar quotes around it; RETURN key word in the function prototype (not the function body) becomes RETURNS in PostgreSQL; join and cursors syntax must be adjusted, etc.
Months_Between
(Oracle/PLSQL: Function)		 No direct replacement is available; but a similar function can be implemented in PostgreSQL’s PL/pgSQL language. Oracle’s MONTHS_BETWEEN (date1, date2) function returns the number of months between dates date1 and date2. This number is computed using a quite intricate algorithm.
|| Operator || Operator An important aspect here is that in PostgreSQL (unlike Oracle) concatenation with a null column nulls the result. If this is not be the behavior you expect, there are several solutions to handle this situation including the use of the SQL CASE WHEN expression, COALESCE or ARRAY_TO_STRING built-in functions.
You can also make use of the PostgreSQL extensibility and create a custom version of the CONCAT operator.
ALTER SESSION
SET CURRENT_SCHEMA = … 		SET search_path TO … To permanently alter the search path set on each connection, use:
ALTER USER user_name SET search_path to ...;
This change will only take effect after reconnecting to the database.
date (data type);

timestamp (data type);

to_char(timestamp, text) function;

sign(number) function;

type casts;

substr(string, from [, count]) function;

‘FROM dual’ syntax;

Porting is straightforward

Finally, there’s another very important peculiarity of what does Cialis that brings it so high above its alternatives. It is the only med that is available in two versions – one intended for use on as-needed basis and one intended for daily use. As you might know, Viagra and Levitra only come in the latter of these two forms and should be consumed shortly before expected sexual activity to ensure best effect. Daily Cialis, in its turn, contains low doses of Tadalafil, which allows to build its concentration up in your system gradually over time and maintain it on acceptable levels, which, consequently, makes it possible for you to enjoy sex at any moment without having to time it.

Tags:
3 thoughts on “Porting applications from Oracle to PostgreSQL”

Comments are closed.

By continuing to use the site, you agree to the use of cookies. More information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close