Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > ActiveRecord, Oracle oci8 and LEFT OUTER JOIN

Reply
Thread Tools

ActiveRecord, Oracle oci8 and LEFT OUTER JOIN

 
 
Brian Candler
Guest
Posts: n/a
 
      03-20-2007
Hello,

This is an ActiveRecord question / issue. I hope it's OK to raise it here(*)

I'm using activerecord-1.15.2 as part of a Rails app. I started development
using Sqlite3 as the backend for expediency (laptop programming on a
plane!). I'm now trying to move it to Oracle 10.2. Unfortunately, the eager
loading of related objects using :include seems to generate SQL which Oracle
barfs on.

The error message I get is:
--------------------------------------------------------------------------
Showing app/views/entities/show.rhtml where line #1 raised:

OCIError: ORA-00920: invalid relational operator: SELECT entities.id AS
t0_r0, entities.key_attribute_id AS t0_r1, entities.key_value AS t0_r2,
entities.state AS t0_r3, avpairs.id AS t1_r0, avpairs.entity_id AS t1_r1,
avpairs.attribute_id AS t1_r2, avpairs.value AS t1_r3 FROM entities LEFT
OUTER JOIN avpairs ON avpairs.entity_id = entities.id WHERE (0 OR
(entities.key_attribute_id=2 AND entities.key_value='vpn1s4') OR
(entities.key_attribute_id=3 AND entities.key_value='000001'))
--------------------------------------------------------------------------

And the source which generates this query:

cond = ["0"]
links.each do |pri, key_attribute_id, key_value|
cond.first << " OR (entities.key_attribute_id=? AND entities.key_value=?)"
cond << key_attribute_id << key_value
end
entities = Entity.find(:all, :conditions => cond, :include => :avpairs)

The 'links' array includes a list of keys to Entity objects that I wish to
load, together with their linked Avpair objects. This all works just dandy
under Sqlite3.

Does this mean that AR eager loading can't work at all with Oracle? Or is
there something specific about this particular query which is causing the
problem?

In the "Agile" book I see a footnote on page 362:
"In fact, it might not work at all! If your database doesn’t support
left outer joins, you can’t use the feature. Oracle 8 users, for instance,
will need to upgrade to version 9 to use preloading."

But then I'd have thought I'd be fine with Oracle 10.2

The Oracle SQL reference starts at
http://download-uk.oracle.com/docs/c...b14200/toc.htm
and the documentation for SELECT is at
http://download-uk.oracle.com/docs/c...2.htm#i2065646

It shows:

join_clause ::= table-reference (inner | outer_join_clause)+

outer_join_clause ::= ... outer_join_type JOIN table_reference ON condition

outer_join_type ::= (FULL|LEFT|RIGHT) OUTER

so I can't actually see why it would reject the SQL generated by AR. Any
ideas?

Regards,

Brian.

(*) At http://ar.rubyonrails.com/ it does say:

"For other information, feel free to ask on the ruby-talk mailing list
(which is mirrored to comp.lang.ruby) or contact http://www.velocityreviews.com/forums/(E-Mail Removed)"

However if there's a more appropriate ActiveRecord mailing list please point
me at it. There are a couple of other minor AR issues/suggestions I'd like
to raise too.

 
Reply With Quote
 
 
 
 
Brian Candler
Guest
Posts: n/a
 
      03-20-2007
> OCIError: ORA-00920: invalid relational operator: SELECT entities.id AS
> t0_r0, entities.key_attribute_id AS t0_r1, entities.key_value AS t0_r2,
> entities.state AS t0_r3, avpairs.id AS t1_r0, avpairs.entity_id AS t1_r1,
> avpairs.attribute_id AS t1_r2, avpairs.value AS t1_r3 FROM entities LEFT
> OUTER JOIN avpairs ON avpairs.entity_id = entities.id WHERE (0 OR
> (entities.key_attribute_id=2 AND entities.key_value='vpn1s4') OR
> (entities.key_attribute_id=3 AND entities.key_value='000001'))


Doh! Running the query under yasql shows the error to be at
WHERE (0 OR ...
and removing it makes the problem goes away. And this was really just
laziness on my part in formulating the SQL statement builder.

Sorry for the noise.

Regards,

Brian.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
connecting to Oracle using OCI8 and DBI Peter Bailey Ruby 11 11-30-2009 02:27 PM
LINQ left outer join John Thomas ASP .Net 1 09-01-2009 05:41 PM
How to pass arrays in and/or out of Oracle PL/SQL Package using OCI8 Jason Vogel Ruby 4 11-21-2006 08:45 PM
Outer join problem PW ASP General 4 05-31-2006 03:27 AM
install_driver(Oracle) failed: Can't load 'C:/Perl/site/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle: load_file:The specified procedure could not be found at C:/Perl/lib/DynaLoader.pm line 230. Feyruz Perl Misc 4 10-14-2005 06:47 PM



Advertisments