Is anyone using oracle with RoR successfully?


#1

Hi,

Is anyone using Oracle with RoR successfully?

Is everything running smoothly?
What are the main issues?

any response is appreciated
thanks,
Chris


#2

Is anyone using Oracle with RoR successfully?

Yep, 8.1, rails server under Windows XP

Is everything running smoothly?

Nothing justify changing database if you use existing databases, but
if you start a new project there are possible threads

What are the main issues?

Some are related to the AR Adapter. (read oci_adapter.rb in
activerecord source tree)

  • Each table must have a sequence if you plan to Create…
  • No LIMIT in Oracle, simulated with subquery, more slower when you
    paginate
  • Oracle’s syntax for join (+) is not used in the query builder who
    respect SQL92 with plain JOINs. only for Ora < 9i. So if you use 8i
    you can’t use :join but fallback to by_sql.

And globally with Rails

  • Scaffolding sometimes hang when generating subforms, you have to
    edit them, but as a real Ror programmer you don’t use scaffold :slight_smile:
  • Most of us, Oracle use mean using an existing legacy schema, and
    IMHO that’s the big issues when you spend most of your time for
    writing Models.
  • Development environment with Webrick+Oracle is slooww, think to use
    Apache+FastCGI or better(?)

#3

I have also gotten Rails (1.1) to work successfully with Oracle (10g). I
am running Apache and FCGI locally, contacting a remote 10g database.
Oracle in my case does in fact mean legacy and there was quite a bit of
finagling to be done with the models. The queries are a little on the
sloppy side, but work. Most of all, the DBA hates not having query
control.

Biggest pitfall so far (and coincidentally the reason this enterprise is
hesitant to support Rails at all) is lack of integrated stored procedure
support. My next task is to get stored procedures working in Rails.
Those who have already done this, please post.


#4

David!

I was very pleased to read your post. I am about to setup a RHEL
4/Apache2/fcgi/RoR/Oracle 10g site. I’ve tested everything but the
Oracle bit (running with Postgresql at the moment). Haven’t quite got
fcgi working.

Did basic Oracle <-> RoR give you much trouble? I’d be very grateful
for any hints or things to watch out for.

Regards,
Bealach


#5

Thanks for the pointers. I might be back here with questions later…

Bealach


#6

Oracle in Rails has worked well for me several times with a very
similar stack to what you describe. The only advice I have is to
triple-check your environment variables.
You’ll want to make sure the oci libraries are in your library path,
and that you’ve built the Ruby OCI8 libraries against them. TNS_ADMIN
also needs to be set, both in your development environment and in the
FastCGI environment.


#7

Hi, i created the initial post all the way back in January.

I’ve been using ROR + Oracle pretty much every day since then and
everything has gone great!


#8

On 1/12/06, Mathieu C. removed_email_address@domain.invalid wrote:

Some are related to the AR Adapter. (read oci_adapter.rb in
activerecord source tree)

  • Each table must have a sequence if you plan to Create…

This isn’t a limitation per se on the oci_adapter.rb, but on the
current Rails requirement that tables have a simple auto-numbered
primary key field, and in Oracle, you do this with sequences, because
there is no autonumber attribute for Integer fields ala Postgres, SQL
Server, MySQL, etc. Yes, it completely blows if you’re using a
database that wasn’t set up this way, however…

It’s a reasonable assumption at this point, but it’ll need to be
addressed if supporting existing database schemas is a desired
long-term goal of Rails for the more flexible RDBMSs like Oracle.

Sequences work great in Oracle because they’re guaranteed to be
transaction-safe, and they’re not tightly tied to the table’s design.

  • No LIMIT in Oracle, simulated with subquery, more slower when you paginate

Otherwise, the alternative would seem to be to set up a package in
Oracle that could run the query (pass in the SQL, do some basic SQL
injection checking, run the SQL as dynamic sql) in a cursor that
returns pages of records. This would probably freak out most Oracle
DBAs when they look at some of the system tables and see all sorts of
open cursor handles, etc.

If you look at the paging code for other adapters (i.e.,
sqlserver_adapter.rb), it’s really no better for them, either, unless
their SQL flavor supports non-standard syntax to do it, of which TSQL
doesn’t, so it’s nested SQL query hacks like the one above is required
for other adapters, too.

Don’t want to suck the entire dataset into Rails just to do it
in-memory on the server though.
Kind of defeats the purpose of the database server, and adds all sorts
of complexity downstream…

  • Oracle’s syntax for join (+) is not used in the query builder who
    respect SQL92 with plain JOINs. only for Ora < 9i. So if you use 8i
    you can’t use :join but fallback to by_sql.

oci_adapter.rb could override the default SQL presented by
activerecord::base, based on perceived (or configured in database.yml)
Oracle version, no (by reimplement the ActiveRecord::Base methods
involved)?

I’m not sure if anyone has tried using the sqlserver_adapter.rb
against older versions of SQL Server that don’t support ANSI join
syntax, either (6.0? 6.5?), either.

And globally with Rails

  • Scaffolding sometimes hang when generating subforms, you have to
    edit them, but as a real Ror programmer you don’t use scaffold :slight_smile:
  • Most of us, Oracle use mean using an existing legacy schema, and
    IMHO that’s the big issues when you spend most of your time for
    writing Models.

Actually, this will be a problem for any existing database system (I’m
thinking of people who write apps against SAP, Lawson, PeopleSoft,
etc. databases, where the design considerations of those databases do
not map well to the conventions or requirements of Rails
development…)

  • Development environment with Webrick+Oracle is slooww, think to use
    Apache+FastCGI or better(?)

If there is one thing that would be nice for Oracle to improve it
would be to increase the speed of opening database connections…