# Oracle DBI driver

**URL:** <https://discourse.julialang.org/t/oracle-dbi-driver/8205>\
**Category:** General Usage\
**Created:** [January 6, 2018, 7:01pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205 "2018-01-06T19:01:00Z")\
**Posts on this page:** 19\
**Page:** 2

<div class="post-metadata">

**Author:** ![avik](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/avik/32/17_2.png) [@avik](https://discourse.julialang.org/u/avik)\
**Post date:** [March 10, 2018, 3:58pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/21 "2018-03-10T15:58:44Z")

</div>

Sorry you find that a turnoff, but, the same sentiment can be expressed saying _There are no known crashing bugs in JavaCall, but there are no warranties in open source software_. I suppose this is a “glass half full” situation 🙂

But seriously, since this was written a couple of years ago, I’ve not had any reports of memory corruption.

---

<div class="post-metadata">

**Author:** ![tk3369](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tk3369/32/2824_2.png) [@tk3369](https://discourse.julialang.org/u/tk3369)\
**Post date:** [March 10, 2018, 6:13pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/22 "2018-03-10T18:13:07Z")

</div>

> [@avik](#):
>
> But seriously, since this was written a couple of years ago, I’ve not had any reports of memory corruption

That’s encouraging!

In fact, given such observation I would argue to remove that statement entirely. There’s no 100% bug free software and if anyone bump into such a problem it would definitely surface and become visible.

---

<div class="post-metadata">

**Author:** ![Liso](https://avatars.discourse-cdn.com/v4/letter/l/898d66/32.png) [@Liso](https://discourse.julialang.org/u/Liso)\
**Post date:** [May 24, 2018, 10:51am UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/23 "2018-05-24T10:51:17Z")

</div>

Any news here? 🙂

---

<div class="post-metadata">

**Author:** ![felipenoris](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/felipenoris/32/553_2.png) [@felipenoris](https://discourse.julialang.org/u/felipenoris)\
**Post date:** [February 12, 2019, 2:26am UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/24 "2019-02-12T02:26:28Z")

</div>

Yes! [https://github.com/felipenoris/Oracle.jl](https://github.com/felipenoris/Oracle.jl)

---

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [February 12, 2019, 7:54am UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/25 "2019-02-12T07:54:05Z")

</div>

Great!

I’m looking at the readme and I’m wondering if there is a simple extract to dataframe command.

Something like

```
data = Oracle.query(conn, "select * from table")

```

---

<div class="post-metadata">

**Author:** ![avik](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/avik/32/17_2.png) [@avik](https://discourse.julialang.org/u/avik)\
**Post date:** [February 12, 2019, 11:07am UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/26 "2019-02-12T11:07:42Z")

</div>

Hi Felipe,

This is great, thanks! I’m excited by the possibilities for this. I’ve added some thoughts as comments or issues, feel free to use or disregard as you see fit.

Regards

Avik

---

<div class="post-metadata">

**Author:** ![felipenoris](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/felipenoris/32/553_2.png) [@felipenoris](https://discourse.julialang.org/u/felipenoris)\
**Post date:** [February 12, 2019, 11:11am UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/27 "2019-02-12T11:11:24Z")

</div>

~~There is `collect(Oracle.query(...))`, but the result is not a DataFrame. I’ll add some methods to make it easier to convert to DataFrame, but I’ll avoid a dependency on DataFrames in this package.~~

Now this will return an instance of `ResultSet`. It will fetch all data from the query.

```julia
data = Oracle.query(conn, "select * from table")

```

There’s also the original implementation that is stream-based:

```julia
Oracle.query(conn, "select * from table") do cursor
    for row in cursor
        println(row["column_name"])
    end
end
```

---

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [February 12, 2019, 3:03pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/28 "2019-02-12T15:03:58Z")

</div>

Thanks for the quick work

But I’m still getting this error when running the first command

```
data = Oracle.query(conn, "select * from table")
ERROR: MethodError: no method matching query(::Oracle.Connection, ::String)

```

But the stream based command does work

```
Oracle.query(conn, "select * from table") do cursor ...

```

---

<div class="post-metadata">

**Author:** ![felipenoris](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/felipenoris/32/553_2.png) [@felipenoris](https://discourse.julialang.org/u/felipenoris)\
**Post date:** [February 12, 2019, 3:07pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/29 "2019-02-12T15:07:03Z")

</div>

~~It’s currently on branch `raw`. 😄  
There are some things to do before merging the new `query` method to master.~~

Merged to master. I updated the Readme file with an example.

---

<div class="post-metadata">

**Author:** ![aalexandersson](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/aalexandersson/32/2927_2.png) [@aalexandersson](https://discourse.julialang.org/u/aalexandersson)\
**Post date:** [February 12, 2019, 9:40pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/30 "2019-02-12T21:40:44Z")

</div>

Thanks! It would be great to allow Windows OS in a future update.

> ## Requirements
> 
> - [Julia](https://julialang.org/) v0.6, v0.7 or v1.0.
> - Oracle’s [Instant Client](https://www.oracle.com/technetwork/database/database-technologies/instant-client/overview/index.html).
> - Linux or macOS.
> - C compiler.

---

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [February 27, 2020, 10:25am UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/31 "2020-02-27T10:25:01Z")

</div>

I don’t see anything in the documentation about transactions. How, if possible, would one go about making multiple inserts/updates within a transaction in this interface?

---

<div class="post-metadata">

**Author:** ![felipenoris](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/felipenoris/32/553_2.png) [@felipenoris](https://discourse.julialang.org/u/felipenoris)\
**Post date:** [February 28, 2020, 6:28pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/32 "2020-02-28T18:28:32Z")

</div>

Hi! The way Oracle Database works, [“a transaction in Oracle begins when the first executable SQL statement is encountered”.](https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm)

So the [first example](https://felipenoris.github.io/Oracle.jl/dev/tutorial/#Executing-a-Statement-1) in the Oracle.jl tutorial shows a valid transaction.

---

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [February 28, 2020, 7:25pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/33 "2020-02-28T19:25:35Z")

</div>

Thanks, I figured that out eventually.

By the way, I tested out the performance of this interface, vs ODBC on a large extraction with a lot of mixed data types and it looks very good compared to ODBC in terms of time, although not sure why there are so many more allocations

This query involves 1 million rows and 150 columns of mixed data types (numbers and long strings)

```julia
julia> @time data = ODBC.query(dsn, querystring);
751.219655 seconds (83.12 M allocations: 7.166 GiB, 0.59% gc time)

julia> @time data = Oracle.query(conn, querystring);
146.536639 seconds (1.07 G allocations: 23.228 GiB, 7.91% gc time)

```

---

<div class="post-metadata">

**Author:** ![felipenoris](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/felipenoris/32/553_2.png) [@felipenoris](https://discourse.julialang.org/u/felipenoris)\
**Post date:** [February 28, 2020, 9:15pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/34 "2020-02-28T21:15:16Z")

</div>

This is awesome!

The thing is that `Oracle.query` returns a `ResultSet` that stores all resulting rows in memory. Maybe you wanna use a cursor for querying big data and avoid allocations.

```julia
Oracle.query(conn, "SELECT * FROM TB_BIND") do cursor
    for row in cursor
        # row values can be accessed using column name or position
        println( row["ID"] ) # same as row[1]
        println( row["FLT"] )
        println( row["STR"] )
        println( row["DT"] ) # same as row[4]
    end
end

```

---

<div class="post-metadata">

**Author:** ![jonjilla](https://avatars.discourse-cdn.com/v4/letter/j/7ba0ec/32.png) [@jonjilla](https://discourse.julialang.org/u/jonjilla)\
**Post date:** [February 29, 2020, 7:46am UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/35 "2020-02-29T07:46:05Z")

</div>

Sorry, one more question the documentation isn’t clear about. Is there a way to access the keys of the Oracle resultset? i.e. the column names and types returned from the query?

---

<div class="post-metadata">

**Author:** ![Liso](https://avatars.discourse-cdn.com/v4/letter/l/898d66/32.png) [@Liso](https://discourse.julialang.org/u/Liso)\
**Post date:** [February 29, 2020, 12:17pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/36 "2020-02-29T12:17:24Z")

</div>

I just read the code and `resultset.schema.column_query_info` seems to be what you are looking for.

---

<div class="post-metadata">

**Author:** ![felipenoris](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/felipenoris/32/553_2.png) [@felipenoris](https://discourse.julialang.org/u/felipenoris)\
**Post date:** [February 29, 2020, 2:29pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/37 "2020-02-29T14:29:58Z")

</div>

Yes… The package still lacks some API to access these information in an easy manner. See [Issues · felipenoris/Oracle.jl · GitHub](https://github.com/felipenoris/Oracle.jl/issues) . Hopefully I’ll be able to work on that soon.

---

<div class="post-metadata">

**Author:** ![felipenoris](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/felipenoris/32/553_2.png) [@felipenoris](https://discourse.julialang.org/u/felipenoris)\
**Post date:** [February 29, 2020, 2:50pm UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/38 "2020-02-29T14:50:34Z")

</div>

Please feel free to open issues at [Issues · felipenoris/Oracle.jl · GitHub](https://github.com/felipenoris/Oracle.jl/issues) on API that is missing or things that the docs are lacking. I’ll be glad to work on them.

---

<div class="post-metadata">

**Author:** ![Liso](https://avatars.discourse-cdn.com/v4/letter/l/898d66/32.png) [@Liso](https://discourse.julialang.org/u/Liso)\
**Post date:** [March 1, 2020, 9:19am UTC](https://discourse.julialang.org/t/oracle-dbi-driver/8205/39 "2020-03-01T09:19:33Z")

</div>

It is similar to python’s cursor.description in [DBAPI2](https://www.python.org/dev/peps/pep-0249/#cursor-attributes) so I am fine with it.

Although I would probably use their variable names (you could bring or satisfy more people coming from python’s ecosystem) but it is on your decision! 🙂

[Previous page](https://discourse.julialang.org/t/oracle-dbi-driver/8205.md?page=1)