Re: Writing oracle/postgress generic SQL

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