Perl DBI help

[lnr]

Argh, I've got an SQL query which works fine if I run it from Access, and which I think has no errors, but if I try run it from Perl I'm getting an error from the database. Can anyone tell me what obvious thing I'm doing wrong?

The relevant code snippet is:

my $sth = $dbh->prepare("SELECT committed.engid,
                                committed.crsid,
                                committed.uid_num,
                                committed.gid_num,
                                committed.name,
                                committed.college,
                                committed.path
                         FROM c_yj_yp_live_committed committed LEFT OUTER JOIN c_yj_yp_live_working working ON committed.engid = working.engid
                         WHERE working.engid is null");

And this gives the following error:

DBD::Oracle::db prepare failed: ORA-00933: SQL command not properly ended (DBD ERROR: error possibly near <*> indicator at char 384 in 'SELECT committed.engid,
                                committed.crsid,
                                committed.uid_num,
                                committed.gid_num,
                                committed.name,
                                committed.college,
                                committed.path
                         FROM c_yj_yp_live_committed committed <*>LEFT OUTER JOIN c_yj_yp_live_working working ON committed.engid = working.engid
                         WHERE working.engid is null') [for Statement "SELECT committed.engid,
                                committed.crsid,
                                committed.uid_num,
                                committed.gid_num,
                                committed.name,
                                committed.college,
                                committed.path
                         FROM c_yj_yp_live_committed committed LEFT OUTER JOIN c_yj_yp_live_working working ON committed.engid = working.engid
                         WHERE working.engid is null"] at ./yj_correlate.pl line 33.

Any clues? (Apologies for some long lines, I do intended to split it up a bit more eventually, but at the moment I think it slightly aids readability)

Ah, it seems that in fact the oracle database does *not* support left outer joins, and that the reason it works in Access is because Access translates the query into a form that this version of oracle does support. Many thanks to Paul for popping in to explain (I could have just gone and asked him obviously, but he's been rather busy with exam marking recently). What a nuisance, since this is obviously a much nicer way to do the query than faffing about with a "where not in (subquery)" or similar. (We suspect it is supported in the new version of Oracle Paul's working on migrating to, but sadly I think my code is needed before then).

Double correction: it does support them, but with weird syntax. The following code works:

SELECT committed.engid,
       committed.crsid,
       committed.uid_num,
       committed.gid_num,
       committed.name,
       committed.college,
       committed.path
FROM c_yj_yp_live_committed committed, c_yj_yp_live_working working
WHERE committed.engid = working.engid (+)
      and working.engid is null

Comments

[simont]

My SQL is pretty basic, but my first instinct would be to try changing the identifier "committed" on the grounds that something in the big stack of confusing abstraction layers might be treating a keywordish word like that specially. Rename it "frogwobbler" and see if that does the same thing :-)

[simont]

Alternatively, I notice that the exact number of characters from the start of the select statement to the position of the <*> appears to be the suspiciously round number 384. Insanely small arbitrary length limit somewhere?

[arnhem.livejournal.com]

Surely it's that oracle supports left outer joins, but in earlier versions uses a bizarre and deranged syntax for them?

eg this description (http://www.dba-oracle.com/tips_oracle_left_outer_join.htm)

[keirf.livejournal.com]

I can't see the obvious flaw in the SQL (of course, it's SQL...).

So you're doing a left outer join on the committed table and the working table, where the engids match, and selecting based on the the working engid being null, but you're only selecting committed table entries. This seems rather inefficient. If you're joining on matching IDs, can't you just do SELECT where committed.id IS NULL?

[mobbsy.livejournal.com]

Wow, you're still using Oracle 8i (or earlier)? ANSI join syntax has been supported since 9i, which was released in 2001.

MS Access SQL differs from Oracle SQL

[(Anonymous)]

If you want to check your syntax in MS Access then set up a pass through query in Access. That way Access will send your SQL string directly to Oracle and you should get the same ORA-00933 error message. This will confirm that MS Access SQL is being translated into Oracle SQL when it is being passed via the Oracle ODBC driver. When you do the same thing in perl the DBI module does not to the equivalent translation. I have not looked at your SQL that carefully but I suspect the join syntax does not comply with what Oracle expects.