Ecto Oracle adapter

Welcome on-board, I will check the library out. Thanks.

Hi, and welcome to the forum.

Are you saying you can provide an Ecto adapter for Oracle? Some of us have been waiting for something like that for months or even years. As I said above, jamdb is the closest we have.

Thanks,

I am new to elixir and learning. If ecto adapter is in demand I will give it a try and my learning exercise.

Oranif is pure erlang NIF wrapper of Oracle ODPI-C thin OCI wrapper. This deprecates erloci (I wrote a while ago) the old port driver to access directly OCI.

Oranif has has out performed in throughput of insert/select/update of erloci and Erlang ODBC quite significantly so far.

So if you are looking for performance and compiling NIFs in deps isn’t an issue then oranif should help.

I haven’t performance compare against jamdb or odbc though yet!

Thank you. Other than ETS I haven’t really used Erlang modules directly at all, but I’ll give oranif a try.

I don’t know if you already aware of this two resources:

Article series on latest MySQL Ecto adapter:

There’s a talk at this year Elixir conference as well, and:

Also, I’m not experienced in Elixir enough to help you with the code, but let me know if you need help testing.

I looked into postgres elixir driver and postgres ecto adapter together with ecto_sql.
If my understanding is correct so far, I need to first wrap oranif in elixir which will integrate eventually ecto Oracle adapter.
So if I manage to write a functional a elixir wrapper driver in the mean time I will let you know and you can try that out instead oranif Erlang calls.
Will much appreciate a code review and test when the driver is ready to use.
TIA

Some updates on oracle ecto adapter dev activity (using oranif)

I made some progress in implementing a basic DBConnect interface for ecto and managed to connect to an test oracle DB succesfully. It seems I can follow jamdb_oracle ecto adapter design for an oranif ecto adapter. It is, of course, not as simple as just replacing all :jamdb_oracle*.* calls with oranif but I am making progress slowly (while also learning elixir).

In the mean time, out of curiosity and in parallel, I thought I would just try to compare some basic performance between jamdb_oracle and oranif so wrote ora_bench. A priliminary result shows that:

JAMDB inserted 22186 rows in 29.338234 seconds # ~750 rows / second
JAMDB selected 22185 rows in 29.619194 seconds # ~750 rows / second

ORANIF inserted 14000000 rows in 67.489112 seconds  # ~210000 rows / second
ORANIF selected 13946410 rows in 115.466077 seconds # ~121000 rows / second

In each of the tests, I tried to insert into a varchar2(4000) column 10 decimal digits of an incremental counter till DB limits are reached.

In case of oranif I could successfully get ORA-01653: unable to extend table SCOTT.TEST by 1024 in tablespace SYSTEM out of DB so my test stopped due to system limits (as I hoped for).

However, for jamdb I got ORA-01000: maximum open cursors exceeded around 22K rows insert/select (what I wasn’t expecting)! Furthermore, to make the jamdb test better I wanted to use COMOFF; (auto commit off) but that made cursor overflow happen even much earlier (at barely around ~10K rows).

@Cruz when you used jamdb (with ecto) did you ran into any cursor overflow? Do you know what is the right way to close cursors with jamdb so I can avoid this overflow. All I am doing in my test is :jamdb_oracle.sql_query(ConnRef, "select ITEM from test"). Googling didn’t help to know what I might be missing!

@kodepett did you ever got around trying jamdb with your 18m rows use case? How did that perform for you?

This config will sort of show the basic setup I need to run these test.

I would like bring jabdb test to its best possible performance so the comparison with oranif happens on fair ground. I look forword to any advices to improve this test.

TIA for your comments.

I didn’t find that issue, but I didn’t try so many queries. I took a look directly into the github repo of jamdb, and found the issue was reported and supposedly fixed before:

https://github.com/erlangbureau/jamdb_oracle/issues/16

But since you’re finding the issue with the current version, the fix might not apply to all scenarios. There are three changesets mentioned there. I took a quick look but it’s Erlang code. Perhaps they’ll be more useful to you.

BTW, the author of jamdb might appreciate knowing about the defect reappering in this version. Do you want me to open an issue, or do you prefer doing it on your own? He might also be able to help you on this project.

Thanks a lot. The thing is, I am not sure if my code is correct. Though it quite simple still wondering if I did something silly. Will try get in touch with them.

This is slightly off-topic but since this project is of interest to me, does anybody have a tried-and-tested Oracle XE 18 Docker container or a VMWare / VirtualBox VM? I’d like to test this library with Oracle XE 18 but I am not willing to use my [now secondary] Windows PC for it. I’m operating on a Mac and Oracle XE doesn’t work natively on it.

I am aware that Oracle themselves provide these:

• GitHub - oracle/docker-images: Official source of container configurations, images, and examples for Oracle products and projects · GitHub
• GitHub - oracle/vagrant-projects: Vagrant projects for Oracle products and other examples · GitHub

But I was wondering if somebody could report success by using those, or other, images?