java method to convert NON-ANSI ORACLE based queries to Postgres usable ANSI Queries?

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.

the issue-

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?????

PLEASE RESPOND

thanks,
travis

DO NOT CROSSPOST!!! (here).
BTW, I don't know of a "sql translator", but I would suggest, in case you are forced to modify the application, to put really simple queries in it, and use views at the database level to prepare data.

Unfortunately, I believe you are out of luck. I don't think I've ever heard of an "SQL converter" like that, nor do I think it would be a good idea even if you could find one. This would mean that every single query has to be run through an intermediary program, which will have to parse the query, rewrite it, and then send it to the database. I think it would be disastrous for performance. Much better just to dump all your SQL queries to text, and run a single Perl regex script to fix the syntax wherever it appears.

And pabloj is right. If you had followed the data independence principle from the start, then every one of your queries would be encapsulated in a view or stored procedure, making it quite easy to port your application to other database systems, simply by modifying your views or procedures. Generally, you should try not to have main SQL logic embedded in your application. Even where you do have it, it should be hidden in a class or module that can be modified without modifying the whole program.

I've just noticed that SAPDB (free, opensource etc.) can run in Oracle 7 compatibility mode.
Check http://www.sapdb.org/sap_db_features.htm#SQL_Features and http://www.sapdb.org/htmhelp/3f/fa3...11/frameset.htm

i guess i am confused as to how i would store my queries in some procedure because they are never the same... dynamic queries so to speak. (the WHERE clause is dependant upon user selections, which can be any of 600000 + different values...)
there are tables with over 600,000 records, and history tables that capture stuff on each of those rows (so about 4-6 times bigger). some of the SQL queries alone have more than 7-10 outer joins in each.

it is also impossible for us to write 'simple' queries because we are grabbing alot of data from many very large tables. i am not responsible for the start of this project, it is many years old and to redo the couple 100 thousand lines before me is not plausible right now...

yeah, i can see how converting the statements on the fly could have some performance issues, but there are down sides to either way... say, we decide to goto mySQL later or something.. then i have to go change every program AGAIN instead of just making another converter.

ill look into that link pab, looks like it is saying that oracle7 is ANSI compatible? if so, it would help with current code, thanks.

i really appreciate the replies. i would rather have an answer to my problem than a 'should of done....' on stuff i cant change from the past.

all the views and stuff make sense, but tell me if i am wrong. if i have a VIEW that goes against 3 tables, and the tables get updated, the VIEW doesnt update itself, does it? if not, a VIEW is not plausible for us for we need REAL time data, because the tables get updated CONSTANTLY and we cant be waiting to recreate or update the view all the time, because we have built views on these tables before and it took like 20 minutes to build... these arent small tables.

i am no DBA, i am a JAVA programmer trying to make the best of what i have... yep, a DBA would be nice, and so would alot of experience in databases, but i have what i know and these wonderful forums. so... if i am wrong in my ways of doing things, please reply with a better plausible way.

Hi, you wrote:

No, it says that SAPDB can work with Oracle 7 or ANSI SQL syntax.


Yes it does (even a snapshot/materialized vew on Oracle).


You should search www.databasejounal.com or Google for "dynamic PL/SQL" (for Oracle) but it's similar for SQLServer, PostgreSQL or other dbs (not MySQL).