Can yugabyte DB be compatible with Oracle by loading the Oracle extension module

General
1

Hi
It’s a great honor and pleasure to exchange technical issues with you.
Through research, I found that some database products are compatible with Oracle through Orafce extension module. Orafce is an open source PostgreSQL extension module that provides Oracle compatible functions and types.
The processing of user’s operation request is as follows:
SQL parsing and execution: The database kernel parses the user’s SQL statements, and calls the functions and types provided by Orafce during execution to achieve Oracle compatibility.
Data interaction: realize data interaction with Oracle through standard protocols such as ODBC and JDBC. In this process, it makes the format, type and encoding of data consistent with Oracle through the support of Orafce.

So, for our YugabyteDB, it is compatible with PostgreSQL. Can we also make the format, type and encoding of data consistent with Oracle by extending Orafce?

2

Oraforce doesn’t exist. I think you’re talking about orafce, correct?

If yes, we already support it: PostgreSQL extensions | YugabyteDB Docs

The problem is that we have limited resources as a company, and we need to see enough users/company requests to see it as a viable scenario. And even if there is, we still can’t spread resources on multiple big paths.

The oracle-db is very big/complex in features/bugs, so it will take a lot of engineering effort to arrive there.

While with PostgreSQL we can re-use the code because it’s open-source.

1 Like
3

It sounds like what you have mind is you want to use the orafce extension for YugabyteDB to create a situation where you can YugabyteDB with that extension as a “drop in replacement” for an Oracle database, which means that you can run an Oracle application/database client without making any changes?

That is not how the extension works. An extension is a way to add or change behaviour in a PostgreSQL and therefore YugabyteDB by letting the database call the extension code. This means that the database client already must use the PostgreSQL line protocol to first logon and get a database session, and use the PostgreSQL line protocol to perform executions.

What the orafce extension provides is an in-database API of selective and limited set of functionality to help with changing an application from Oracle to PostgreSQL and therefore YugabyteDB. It doesn’t change line and packet formats and mimics protocols.

4

Sorry，it’s orafce, not oraforce.
When YugabyteDB supports Oracle through Orafce, it can execute Oracle-compatible SQL statements. Here is a simple example:
Oracle statement is as follow:
SELECT SYSDATE FROM DUAL;

YugabyteDB statement is as follow:
SELECT SYSDATE();

In this query, SYSDATE is a function of Oracle to return the current system date and time. In YugabyteDB, we use the SYSDATE() function provided by Orafce to achieve the same effect.
Maybe, I will try to forward the user’s SQL statements to postgreSQL for execution. The premise is that PostgreSQL extends orafce.
Is this feasible？
Thanks.

5

Hi
Yes, you’re right.
Maybe, I want to use the conversion implementation of SQLines to convert the syntax and protocol of Oracle into PostgreSQL for execution, and then write the data to yugabyte through PostgreSQL syntax and protocol ? This SQLines supports convert oracle to PostgreSQL.
Is this feasible？

6

The description you give is not enough for me to determine what exactly and technically you mean, and makes me to guesses about what you actually mean.

I want to use the conversion implementation of SQLines to convert the syntax and protocol of Oracle into PostgreSQL for execution

Is sqlines a product, or do you mean you want to try to take the a postgres client and transform the SQL you have into postgres SQL?

Please mind that SQL, despite being a standard requires modifications to actually work with the different implementations of SQL by databases like Oracle, SQL-Server, MySQL, PostgreSQL. So the ‘standard’ means a general SQL implementation is standardised, but for each database to use specific implementations of certain database features, you will have to change it.

One of the more hidden but really important topics is that each database implements a slightly different implementation of how it behaves with read consistency. The reason I say hidden, is that the basic idea is the same for all the databases when running in the same isolation level. But you will find that there are nuances if you look at the details.

1 Like
7

Okay，I got it now，thanks.