Ben Edwards wrote:
Anyone know of any guidelines for writing SQL which works under Oracle witch will also work under postgress. This is to ensure that SQL written for an Oracle database can be migrated to postgress later.
I converted a fairly complex data collection application from Oracle to PG about 2 yrs ago. I was pleasantly surprised at how little DML I had to change, and some of it had deeply nested subqueries. Here are the snags I hit:
(1) Stored procedures had to be rewritten by hand. You might want to look at EnterpriseDB, as they've added on to PG to enhance Oracle compatibility.
(2) I had to change all the stored procedure invocations that used Oracle's "call myproc()" syntax. If we had used JDBC standard calling conventions, this would not have been necessary. (Just realized you didn't say which language you are using.)
(3) Stay away for Oracle proprietary SQL features, like their use of (+) for outer joins. This was a version 8 oddity, and they support standard outer join syntax now.
(4) We had significant use of Oracle dblinks in our SQL, and of course that doesn't translate. PG has a dblink capability in contrib, but it is not as complete an implementation as Oracle's.
Hope that helps. -- Guy Rouillier
