Search code examples
sqloraclemariadbunixodbc

Run query from MariaDB on Oracle

I have MariaDB and Oracle databases. I have set up ODBC Connect between the two, so that I can access Oracle from MariaDB.

I can do the following from MariaDB:

CREATE TABLE oracopy ENGINE=connect TABLE_TYPE=ODBC tabname='testtab' CONNECTION='DSN=ORCL';

This creates a table locally.

What I really want to do however is run a Query on the remote Oracle and return the results to the MariaDB session.

The query would be Oracle speficic, i.e. might containing ORACLE functions like DECODE. Also the query could include a PLSQL function call which again would need to be run on Oracle. E.g:

SELECT t.id, DECODE( t.typ,'HH', 'Val 1', 'Val 2' ) tt,
       my_package.fn_test ( t.dob ) dob
FROM testtab t;

Does MariaDB have a "run this query on XXX remote database".

Solution

  • Consider using the source definition argument, SRCDEF, as shown in docs.

    CREATE TABLE oracopy ENGINE=connect TABLE_TYPE=ODBC CONNECTION="DSN=ORCL"
SRCDEF="SELECT t.id, DECODE( t.typ,'HH', 'Val 1', 'Val 2' ) tt,
               my_package.fn_test ( t.dob ) dob
        FROM testtab t;"