Oracle DBI driver

General Usage
21

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 :slight_smile:

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

1 Like
22

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.

1 Like
23

Any news here? :slight_smile:

24

Yes! https://github.com/felipenoris/Oracle.jl

6 Likes
25

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")
26

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

1 Like
27

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.

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

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

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

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 ...
29

It’s currently on branch raw. :smile:
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.

30

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

Requirements

1 Like
31

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?

32

Hi! The way Oracle Database works, “a transaction in Oracle begins when the first executable SQL statement is encountered”.

So the first example in the Oracle.jl tutorial shows a valid transaction.

1 Like
33

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> @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)
2 Likes
34

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.

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
3 Likes
35

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?

36

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

37

Yes… The package still lacks some API to access these information in an easy manner. See Issues · felipenoris/Oracle.jl · GitHub . Hopefully I’ll be able to work on that soon.

1 Like
38

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

1 Like
39

It is similar to python’s cursor.description in DBAPI2 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! :slight_smile: