01-14-03, 17:23 #1Registered User
- Join Date
- Jan 2003
Unanswered: java method to convert ORA-Query to PSQL query?
basically. is there such a thing that will convert a non-ansi query, written to work in PRE-ORACLE9i databases (which now supports ansi) to a query that will work in both ORACLE9i AND postgres??????????
i am faced with a decision of either a massive code overhaul or a nasty method/program to convert my Oracle based queries into a Oracle9i / Postgresql friendly manner.
all my code was initially written against oracle7, oracle8i using sql queries that ran in these databases, for clients that used them. honestly, there is over a couple hundred thousand lines of code spread out through over 500+ java programs (using tags, with TLD's). a couple hundred of these tags/programs have the sql embedded with them. they have OUTER joins in them and based on ORACLE back then, it did not support the ANSI sql logic (using 'left outer join on'), so the outer join code is table.column(+)=table2.column.....
ORACLE is expensive, so alot of people want to use my code but want to use postgres as their database. i would like 1 set of code that could use either database, assuming the databases are set up exactly the same (already done). ORACLE9i now supports ANSI sql. So, I can go through the 500+ programs and change every single OUTER JOIN to ANSI sql, which could honestly take me months... also changing stuff like sequences (how they are called) and 'sysdate' to 'now()' and stuff like that....
OR - a java method that could take a NON-ANSI query and convert it to an ANSI QUERY and i could run the queries through this converter at runtime depending on the environment my client is running.
this way, all the code could be written as it always has been, and the converter could take care of all query changes.
the converter method is also preferential because if another difference is found that needs changed, it could be changed in the converter method 1 time, rather than the 500 programs, which have to be re-compiled and what not....
SO, my question is... has anyone heard of something like this method already existing?????
01-15-03, 08:07 #2Registered User
- Join Date
- Sep 2001
I'm no oracle expert but you find some scripts that do some conversion from oracle to postgres
Maybe this url helps you out:
If not the repost it in the usenet on the JDBC group.
Maybe they know some hints to get rid of the problemhttp://www.postgresql.org
--Postgresql is the only kind of thing--