[Dbix-class] New DBIx::Class oracle user

[Ben Hitz]

Hi -

We have a well-developed Oracle database but are refactoring our per  
API for it.  So we are checking out Class::DBI and DBIx::Class (at  
Matt's prompting).

I did get the DBIx::Class::Schema::Loader to work, BUT I had to put  
in a little hack to use "$db_schema.$tablename" instead of $tablename in
DBIx::Schema::Loader::DBI::Oracle.

I ran my first test on the new class, and it works great - BUT only  
if I log in as user $db_schema (in Oracle, users and schemas are  
synonymous).   In our current API (and older, raw 'bag o DBI SQL  
code") we basically hard code in $schema.$table.

If you log in as a user you get :
DBIx::Class::ResultSet::search(): DBI Exception: DBD::Oracle::db  
prepare_cached failed: ORA-00942: table or view does not exist (DBD  
ERROR: error possibly near <*> indicator at char 247 in 'SELECT  
me.reference_no, me.source, me.status, me.pdf_status, me.dbxref_id,  
me.citation, me.year, me.pubmed, me.date_published, me.date_revised,  
me.issue, me.page, me.volume, me.title, me.journal_no, me.book_no,  
me.date_created, me.created_by FROM <*>reference me WHERE ( pubmed  
= :p1 )') [for Statement "SELECT me.reference_no, me.source,  
me.status, me.pdf_status, me.dbxref_id, me.citation, me.year,  
me.pubmed, me.date_published, me.date_revised, me.issue, me.page,  
me.volume, me.title, me.journal_no, me.book_no, me.date_created,  
me.created_by FROM reference me WHERE ( pubmed = ? )"] at ./test.pl  
line 10

(no, my user name is not "me")

It's very hard to google for this sort of info because the nouns are  
all "schema" "user" "database" name.

I think there is a simple solution to this, but I am a little stuck.
Some options:
1) can I pass a schema name indepedent of my user name to DBI  
connection string?
2) is there some field I can set in the DBIx::Schema or in the  
ResultSources themselves?
3) Was there some other good way to run DBIx::Class::Schema::Loader  
(without the hack) that I missed?  Another hack to add?

Thanks for your time.

Ben
--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO  
Consortium
Stanford University ** hitz at genome.stanford.edu