1

We use Oracle in production, but in our company it's difficult to install in development environments.

Is there a way to let Perl (or any other language) send raw SQL statements via the DBI module (or the equivalent module for any other language to send SQL commands to a database) which are translated from Oracle SQL into MySQL or SQLite or something else?

So the application would think it was talking to Oracle but actually be talking to a different, easier to install, open source/free database.

Will Sheppard
  • 3,272
  • 2
  • 31
  • 41
  • For **very** simple statements you can simply exchange the database. However, each DB has its own syntax, I.e. you really should install an Oracle Database – Wernfried Domscheit Jan 08 '16 at 17:02
  • installing Oracle CAN be easy. it can be automated even. just a thought – thatjeffsmith Jan 08 '16 at 17:04
  • 1
    The proper solution would be to use ODBC everywhere – Borodin Jan 08 '16 at 17:14
  • 2
    wouldn't you want your dev environment to be as similar to the prod environment as possible? Seems like a big mistake even if it was possible. Find out why setting up an Oracle dev instance is difficult and work through those issues imo. – tbone Jan 08 '16 at 19:26
  • I opt for VirtualBox and Vagrant. Have your dev database available in a matter of minutes. Scratch it - create new - load data - just by one command. These tools make your dev life easy. – Bjarte Brandt Jan 08 '16 at 19:39
  • For what it's worth, PostgreSQL is **similar** to Oracle in a good bit of syntax and functionality, especially if you compare it to something like Sybase or SQL Server. That said, I ditto what Dmitry said. – Hambone Jan 09 '16 at 03:26

2 Answers2

3

It is impossible. Other database engines can't process Oracle queries. Simplest query in Oracle, for example, if you want to get name of a current user:

select user from dual

This query uses DUAL table, which is not present in any other DBMS. There is also a lot of Oracle-specific constructions (CONNECT BY, MINUS, etc.) which couldn't be processed in other engines.
The best way in your situation would be to create one Oracle installation in a virtual machine, and then copy it's image to another virtual machines for work.

Dmitriy
  • 5,525
  • 12
  • 25
  • 38
  • Don't you just love compatibility? ;P – dsm Jan 08 '16 at 18:51
  • @dsm No, I just love Oracle ;) To run queries everywhere they have to be compatible with every DBMS. If author of the question is ready to throw away all features that Oracle has, then he just don't need Oracle. – Dmitriy Jan 08 '16 at 20:21
  • 1
    Date/Time functions also seem to be different between the various DBMSs. Also, Oracle has a very unique update command, in my opinion, which is vastly different if you can use the PK versus if you cannot and have to enter that extra `exists` clause that matches the join. – Hambone Jan 09 '16 at 03:25
1

The ANSI SQL is the abstraction layer you are referring to.

Assuming that all the tables exist in the target database and the RDBMS supports ANSI SQL then your query will transport to that other database flawlessly.

If you are looking to use something that is not defined in the standard, then it is unlikely to work correctly.

This is a link to the Oracle 11.2G position on the standard.

While talking about Oracle, their solution to the problem is to offer Oracle Database Gateways.

Tai Paul
  • 900
  • 10
  • 19