Hi, Is anyone using Oracle with RoR successfully? Is everything running smoothly? What are the main issues? any response is appreciated thanks, Chris
on 2006-01-12 17:19
on 2006-01-12 18:33
> 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 :) - 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(?)
on 2006-04-26 01:37
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.
on 2006-04-26 01:49
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
on 2006-04-26 02:33
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.
on 2006-04-26 02:36
Thanks for the pointers. I might be back here with questions later.... Bealach
on 2006-04-26 13:52
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!
on 2006-04-26 20:53
On 1/12/06, Mathieu C. <firstname.lastname@example.org> 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 :) > - 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...