oci adpater and Oracle

[Nicolas Couturier]

trying to use ruby (1.8.6) and activerecord(1.15.3) with oci adapter.
Tried this code :

require 'active_record'
ActiveRecord::Base.establish_connection(
:adapter => "oci",
:username => "scott",
assword => "ross",
:host => "DCFPROD")
class Credit < ActiveRecord::Base
set_table_name "dc_dossier"
set_primary_key "n_dossier"

end
p Credit.findfirst)

But got this error :
ORA-00942: table or view does not exist: select * from (select
raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM dc_dossier ) raw_sql_
where rownum <= 1) where raw_rnum_ > 0 (ActiveRecord::StatementInvalid)

I googled a bit and found that i should try to add that to the Credit
class :
connection.execute('alter session set current_schema=DCFPROD')

Got that :
OCIError: ORA-01435: user does not exist: alter session set
current_schema=DCFPROD (ActiveRecord::StatementInvalid)

I'm clueless. I don't know much about Oracle.
Is there someone with an idea ?
THX.

 

[F. Senault]

Le 13 novembre 2007 à 14:49, Nicolas Couturier a écrit :

trying to use ruby (1.8.6) and activerecord(1.15.3) with oci adapter.
Tried this code :

:username => "scott",
I googled a bit and found that i should try to add that to the Credit
class :
connection.execute('alter session set current_schema=DCFPROD')

Got that :
OCIError: ORA-01435: user does not exist: alter session set

In Oracle speak, each user has his own schema. You should try :

connection.execute('alter session set current_schema=scott')

(Now, maybe you should create you own user and use that here, as well as
in the username to connect with.)

Fred

 

[Nicolas Couturier]

Thanks for your answer.
But, so far no luck.
I added that line
connection.execute('alter session set current_schema=scott')

Got the first error message :
ORA-00942: table or view does not exist: select * from (select
raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM dc_dossier ) raw_sql_
where rownum <= 1) where raw_rnum_ > 0 (ActiveRecord::StatementInvalid)

Tried to add the :database parameter to the connection. No luck.
Does someone ahve another idea ?

 

[F. Senault]

Le 16 novembre à 10:16, Nicolas Couturier a écrit :

Got the first error message :
ORA-00942: table or view does not exist: select * from (select
raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM dc_dossier ) raw_sql_
where rownum <= 1) where raw_rnum_ > 0 (ActiveRecord::StatementInvalid)

Tried to add the :database parameter to the connection. No luck.
Does someone ahve another idea ?

Well, at first, try to connect to the DB with a query tool (using the
same password and user than rails, of course), and execute the query as
given in the logs. Then, if it doesn't work, try to "decapsulate" it
step by step :

select * from (select raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM
dc_dossier ) raw_sql_ where rownum <= 1) where raw_rnum_ > 0

select raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM dc_dossier)

SELECT * FROM dc_dossier

It may give you a hint of what's wrong (especially the precise Oracle
error).

Fred

 

[Nicolas Couturier]

It did the trick.
The following line :
connection.execute('alter session set current_schema=scott')
was properly set up.
Thanks for your help.