Hello there! I know some Ruby programming, but I''m starting with Rails today, so excuse me if it''s a basic question, I searched about it but did not find an answer. Is there a way to create a controller using something like tablespace.table? I have two distinct tablespaces here and need to specify where is the table I''m using with the controller, like bigtablespace.people for ex. I could put a default tablespace for the user who connects to the database, but the same user must have access on both tablespaces. Thanks! -- Posted via http://www.ruby-forum.com/.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Nov 22, 2005, at 11:42 AM, Eustáquio Rangel wrote:> Is there a way to create a controller using something like > tablespace.table? > > I have two distinct tablespaces here and need to specify where is the > table I''m using with the controller, like bigtablespace.people for > ex. I > could put a default tablespace for the user who connects to the > database, but the same user must have access on both tablespaces.I''m not an Oracle user, but you can try this: class Foobar < ActiveRecord::Base self.table_name = ''sometablespace.foobars'' end jeremy -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (Darwin) iD8DBQFDg4+6AQHALep9HFYRAnpdAJ4tOZHC74/EqM395jsdJEzyDjCAXQCfckpb UHnxUC5wSHzI/rAK9b+w2HI=aa+b -----END PGP SIGNATURE-----
Jeremy Kemper wrote:> -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Nov 22, 2005, at 11:42 AM, Eustáquio Rangel wrote: > >> Is there a way to create a controller using something like >> tablespace.table? >> >> I have two distinct tablespaces here and need to specify where is the >> table I''m using with the controller, like bigtablespace.people for ex. I >> could put a default tablespace for the user who connects to the >> database, but the same user must have access on both tablespaces. > > > I''m not an Oracle user, but you can try this: > > class Foobar < ActiveRecord::Base > self.table_name = ''sometablespace.foobars'' > endUnfortunately, that won''t work. All output here with ''rake freeze_edge''. For our helpdesk system in Oracle, we have a model named Issue for an its.issue table. The application logs in as ''its'', so the Issue model has no set_tablename. As the user who owns the tablespace, this works: >> i = Issue.find(123185) => #<Issue:0xb6ccd2bc @attributes=........ As another user who doesn''t have ''its'' that as a default tablespace, you get this: >> i = Issue.find(123185) ActiveRecord::StatementInvalid: OCIError: ORA-00942: table or view does not exist: select * from (select raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM issue WHERE (issue.issueid = 123185) ) raw_sql_ where rownum <= 1) where raw_rnum_ > 0 from ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:78:in `log'' Adding set_table_name ''its.issue'' to the model gets this >> i = Issue.find(123185) ActiveRecord::StatementInvalid: OCIError: ORA-00904: "ITS"."ISSUE"."ITS"."ISSUEID": invalid identifier: select * from (select raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE (its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where raw_rnum_ > 0 Note the duplicate ''its'' here. I''ll be glad to help out and get this resolved, if somebody else wants to supply patches :) Regards, Blair -- Blair Zajac, Ph.D. <blair-szbw9MROnEZWk0Htik3J/w@public.gmane.org> Subversion and Orca training and consulting http://www.orcaware.com/svn/
> I''ll be glad to help out and get this resolved, if somebody else wants to > supply > patches :)This is only a workaround, not a full solution, but will using Oracle synonyms work? I have a rails app running against Oracle and the db user does not own the tables, but there are public synonyms that make the tables accessible to the user. But then you might run into the problem of rails using user_tab_columns and not finding the column definitions -- I worked around this with the following override of OCIAdapter#columns: module ActiveRecord module ConnectionAdapters class OCIAdapter < AbstractAdapter # Use all_tab_columns instead of user_tab_columns def columns(table_name, name = nil) cols = select_all(%Q{ select column_name, data_type, data_default, data_length, data_scale from all_tab_columns where table_name = ''#{table_name.upcase}''} ).map { |row| # strip quotes from default values default = row[''data_default''] default = default.gsub(/''/,'''').strip if default OCIColumn.new row[''column_name''].downcase, default, row[''data_length''], row[''data_type''], row[''data_scale''] } cols end end end end This is with AR 0.11. I haven''t noticed if this has been addressed in edge rails or not, does anyone know? I can file an item in Trac if not. Cheers, /Nick _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Eustáquio Rangel wrote:> Hello there! > > I know some Ruby programming, but I''m starting with Rails today, so > excuse me if it''s a basic question, I searched about it but did not find > an answer. > > Is there a way to create a controller using something like > tablespace.table? > > I have two distinct tablespaces here and need to specify where is the > table I''m using with the controller, like bigtablespace.people for ex. I > could put a default tablespace for the user who connects to the > database, but the same user must have access on both tablespaces.I think by tablespace you mean schema. Is that correct? In that case you can use synonyms on the database, after granting the apropriate rights to the user you connect with. Or you can use Jeremys solution (but you have to grant [select|insert|update|whatever] to your connecting user first).
Blair Zajac wrote:> Jeremy Kemper wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> On Nov 22, 2005, at 11:42 AM, Eustáquio Rangel wrote: >> >>> Is there a way to create a controller using something like >>> tablespace.table? >>> >>> I have two distinct tablespaces here and need to specify where is the >>> table I''m using with the controller, like bigtablespace.people for >>> ex. I >>> could put a default tablespace for the user who connects to the >>> database, but the same user must have access on both tablespaces. >> >> >> >> I''m not an Oracle user, but you can try this: >> >> class Foobar < ActiveRecord::Base >> self.table_name = ''sometablespace.foobars'' >> end > > > Unfortunately, that won''t work. All output here with ''rake freeze_edge''. > > For our helpdesk system in Oracle, we have a model named Issue for an > its.issue table. The application logs in as ''its'', so the Issue model > has no set_tablename. > > As the user who owns the tablespace, this works: > > >> i = Issue.find(123185) > => #<Issue:0xb6ccd2bc @attributes=........ > > As another user who doesn''t have ''its'' that as a default tablespace, you > get this: > > >> i = Issue.find(123185) > ActiveRecord::StatementInvalid: OCIError: ORA-00942: table or view does > not exist: select * from (select raw_sql_.*, rownum raw_rnum_ from > (SELECT * FROM issue WHERE (issue.issueid = 123185) ) raw_sql_ where > rownum <= 1) where raw_rnum_ > 0 from > ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:78:in > `log'' > > Adding > > set_table_name ''its.issue'' > > to the model gets this > > >> i = Issue.find(123185) > ActiveRecord::StatementInvalid: OCIError: ORA-00904: > "ITS"."ISSUE"."ITS"."ISSUEID": invalid identifier: select * from (select > raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE > (its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where > raw_rnum_ > 0 > > Note the duplicate ''its'' here. > > I''ll be glad to help out and get this resolved, if somebody else wants > to supply patches :)This is a bug in AR for sure. Maybe you can use alter session set current schema=its; before executing the query? I think that should work, but it''s a very ugly hack.
* Nick Sieger (nicksieger-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org) [051122 18:00]:> This is only a workaround, not a full solution, but will using Oracle synonyms > work? I have a rails app running against Oracle and the db user does not own > the tables, but there are public synonyms that make the tables accessible to > the user.We were using public synonyms sort of by default (i.e., our DBA just added them as a matter of course), but as we scaled up development we had each user with his own schema for doing testing and we noticed that what was going on in one schema could affect what was going on in another. After a hair-pulling session one afternoon we identified the problem, nuked the public synonyms and haven''t looked back. Rick -- http://www.rickbradley.com MUPRN: 533 | friends climb in and say, random email haiku | wow, that fresh clean leather >scent | is incredible.
Rick Bradley wrote:> We were using public synonyms sort of by default (i.e., our DBA just > added them as a matter of course), but as we scaled up development we > had each user with his own schema for doing testing and we noticed that > what was going on in one schema could affect what was going on in > another. After a hair-pulling session one afternoon we identified the > problem, nuked the public synonyms and haven''t looked back. > > RickBut you can use private synoyms. for example: connect as user a grant select on person to user_b; connect as user b create synonym person for user_a.person; select * from person; This works, and the synonym person is only visible to user b
Simon Santoro wrote:> Blair Zajac wrote: > >> Jeremy Kemper wrote: >> >>> -----BEGIN PGP SIGNED MESSAGE----- >>> Hash: SHA1 >>> >>> On Nov 22, 2005, at 11:42 AM, Eustáquio Rangel wrote: >>> >>>> Is there a way to create a controller using something like >>>> tablespace.table? >>>> >>>> I have two distinct tablespaces here and need to specify where is the >>>> table I''m using with the controller, like bigtablespace.people for >>>> ex. I >>>> could put a default tablespace for the user who connects to the >>>> database, but the same user must have access on both tablespaces. >>> >>> >>> >>> >>> I''m not an Oracle user, but you can try this: >>> >>> class Foobar < ActiveRecord::Base >>> self.table_name = ''sometablespace.foobars'' >>> end >> >> >> >> Unfortunately, that won''t work. All output here with ''rake freeze_edge''. >> >> For our helpdesk system in Oracle, we have a model named Issue for an >> its.issue table. The application logs in as ''its'', so the Issue model >> has no set_tablename. >> >> As the user who owns the tablespace, this works: >> >> >> i = Issue.find(123185) >> => #<Issue:0xb6ccd2bc @attributes=........ >> >> As another user who doesn''t have ''its'' that as a default tablespace, >> you get this: >> >> >> i = Issue.find(123185) >> ActiveRecord::StatementInvalid: OCIError: ORA-00942: table or view >> does not exist: select * from (select raw_sql_.*, rownum raw_rnum_ >> from (SELECT * FROM issue WHERE (issue.issueid = 123185) ) raw_sql_ >> where rownum <= 1) where raw_rnum_ > 0 from >> ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:78:in >> `log'' >> >> Adding >> >> set_table_name ''its.issue'' >> >> to the model gets this >> >> >> i = Issue.find(123185) >> ActiveRecord::StatementInvalid: OCIError: ORA-00904: >> "ITS"."ISSUE"."ITS"."ISSUEID": invalid identifier: select * from >> (select raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue >> WHERE (its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) >> where raw_rnum_ > 0 >> >> Note the duplicate ''its'' here. >> >> I''ll be glad to help out and get this resolved, if somebody else wants >> to supply patches :) > > > This is a bug in AR for sure. > Maybe you can use > alter session set current schema=its; > before executing the query? > I think that should work, but it''s a very ugly hack.I tried adding this to my AR class, but it didn''t work. Here''s from the command line: $ sqlplus username/passwd@dbhost SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 22 15:20:54 2005 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options SQL> alter session set current schema=its; alter session set current schema=its * ERROR at line 1: ORA-00922: missing or invalid option What''s the correct syntax? Regards, Blair -- Blair Zajac, Ph.D. <blair-szbw9MROnEZWk0Htik3J/w@public.gmane.org> Subversion and Orca training and consulting http://www.orcaware.com/svn/
Simon Santoro wrote:> Rick Bradley wrote: > >> We were using public synonyms sort of by default (i.e., our DBA just >> added them as a matter of course), but as we scaled up development we >> had each user with his own schema for doing testing and we noticed that >> what was going on in one schema could affect what was going on in >> another. After a hair-pulling session one afternoon we identified the >> problem, nuked the public synonyms and haven''t looked back. >> >> Rick > > > But you can use private synoyms. > for example: > connect as user a > grant select on person to user_b; > connect as user b > create synonym person for user_a.person; > select * from person; > > This works, and the synonym person is only visible to user bTo help Rails to make inroads into corporate environments by sneaking under the radar, it would be great Rails could use Oracle databases without having to make any modifications of the db, such as adding synonyms, otherwise the DBA gets involved and may ask more questions than you''d like :) So anything to use a schema would help a lot. Regards, Blair -- Blair Zajac, Ph.D. <blair-szbw9MROnEZWk0Htik3J/w@public.gmane.org> Subversion and Orca training and consulting http://www.orcaware.com/svn/
Blair Zajac wrote:>> This is a bug in AR for sure. >> Maybe you can use >> alter session set current schema=its; >> before executing the query? >> I think that should work, but it''s a very ugly hack. > > > I tried adding this to my AR class, but it didn''t work. Here''s from the > command line: > > $ sqlplus username/passwd@dbhost > > SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 22 15:20:54 2005 > > Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. > > > Connected to: > Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit > Production > With the Partitioning, Real Application Clusters, OLAP and Data Mining > options > > SQL> alter session set current schema=its; > alter session set current schema=its > * > ERROR at line 1: > ORA-00922: missing or invalid option > > > What''s the correct syntax?oh serry. i wrote this without checking. the correct syntax is alter session set current_schema=its; -- +S2
Hello guys, thanks for all the answers and tips.> I think by tablespace you mean schema. Is that correct? In that case you > can use synonyms on the database, after granting the apropriate rights > to the user you connect with. Or you can use Jeremys solution (but you > have to grant [select|insert|update|whatever] to your connecting user > first).The tablespaces are really different files, one not so big named like not_so_big_and_not_so_often_updated with a table called PEOPLE, for example, and other big_and_often_updated with a table called ORDERS, for example, so we keep it on different files. To refer to a table with a user, a need something like not_so_big_and_not_so_often_updated.PEOPLE big_and_often_updated.ORDERS Even if I can insert the "alias" after created the controller, I can''t see a way to create the controller with the "alias" of them and seems that I''ll need to hack it after created, as you told me. And using the current_schema will not allow me to make queries like select a.customer_id, a.name, b.order, b.value from not_so_big_and_not_so_often_updated.PEOPLE a, big_and_often_updated.ORDERS b where a.customer_id=b.customer_id -- Posted via http://www.ruby-forum.com/.
To solve this easily in the meantime, make a public synonym for the table. Create or replace public synonym "MYTABLE" for "SCHEMA"."MYTABLE" And as long as any user in the system has access to the base table, they''ll be able to pull data. (any user will be able to do "select * from mytable" instead of "select * from schema.mytable) http://www.techonthenet.com/oracle/synonyms.php -----Original Message----- From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org [mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On Behalf Of Simon Santoro Sent: Tuesday, November 22, 2005 5:06 PM To: rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org Subject: Re: [Rails] Oracle tablespaces Blair Zajac wrote:> Jeremy Kemper wrote: > >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> On Nov 22, 2005, at 11:42 AM, Eustáquio Rangel wrote: >> >>> Is there a way to create a controller using something like >>> tablespace.table? >>> >>> I have two distinct tablespaces here and need to specify where is >>> the table I''m using with the controller, like bigtablespace.people for >>> ex. I >>> could put a default tablespace for the user who connects to the >>> database, but the same user must have access on both tablespaces. >> >> >> >> I''m not an Oracle user, but you can try this: >> >> class Foobar < ActiveRecord::Base >> self.table_name = ''sometablespace.foobars'' >> end > > > Unfortunately, that won''t work. All output here with ''rake > freeze_edge''. > > For our helpdesk system in Oracle, we have a model named Issue for an > its.issue table. The application logs in as ''its'', so the Issue model > has no set_tablename. > > As the user who owns the tablespace, this works: > > >> i = Issue.find(123185) > => #<Issue:0xb6ccd2bc @attributes=........ > > As another user who doesn''t have ''its'' that as a default tablespace, > you > get this: > > >> i = Issue.find(123185) > ActiveRecord::StatementInvalid: OCIError: ORA-00942: table or view > does > not exist: select * from (select raw_sql_.*, rownum raw_rnum_ from > (SELECT * FROM issue WHERE (issue.issueid = 123185) ) raw_sql_ where > rownum <= 1) where raw_rnum_ > 0 from > ./script/../config/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:78:in > `log'' > > Adding > > set_table_name ''its.issue'' > > to the model gets this > > >> i = Issue.find(123185) > ActiveRecord::StatementInvalid: OCIError: ORA-00904: > "ITS"."ISSUE"."ITS"."ISSUEID": invalid identifier: select * from (select > raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE > (its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where > raw_rnum_ > 0 > > Note the duplicate ''its'' here. > > I''ll be glad to help out and get this resolved, if somebody else wants > to supply patches :)This is a bug in AR for sure. Maybe you can use alter session set current schema=its; before executing the query? I think that should work, but it''s a very ugly hack. _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
* Blair Zajac (blair-szbw9MROnEZWk0Htik3J/w@public.gmane.org) [051122 18:25]:> >But you can use private synoyms. > >for example: > >connect as user a > >grant select on person to user_b; > >connect as user b > >create synonym person for user_a.person; > >select * from person; > > > >This works, and the synonym person is only visible to user b > > To help Rails to make inroads into corporate environments by sneaking under > the radar, it would be great Rails could use Oracle databases without > having to make any modifications of the db, such as adding synonyms, > otherwise the DBA gets involved and may ask more questions than you''d like > :) So anything to use a schema would help a lot.We''ve been using Rails on an Oracle database (and on Postgres) since before RubyConf and we''re not currently using any sort of synonyms, etc. Our DBAs set up a development schema, a production schema, and N testing schemas (1 for each developer, one for nightly demo builds, one for continuous integration builds). I''m not a DBA so perhaps I''m missing a salient detail, but after removing the troublesome synonyms we haven''t had any problems in this configuration. Here''s our database.yml file (with some names changed to protect the guilty): <% # determine our database user name from an environment variable user = (ENV[''PHOENIX_USER''] || ENV[''LOGNAME''] || ENV[''USERNAME'']) puts "User [#{user}]" # determine which database we are connecting to via an environment variable case ENV[''PHOENIX_DB''] when ''oracle'' # connecting to an Oracle database puts "Using Oracle" %> production: adapter: oci host: CNPROD username: unknown password: unknown test: adapter: oci host: CNVOL username: <%= user ? user : ''cnet'' %> password: XXXXXXXX development: adapter: oci host: CNDEV username: cnet password: cnet <% else # connecting to a PostgreSQL database %> production: adapter: postgresql database: centernet host: dbhost username: centernet password: XXXXXXXXX test: adapter: postgresql database: rewrite_<%= user ? "#{user}_" : '''' %>vol host: dbhost username: centernet password: XXXXXXXX development: adapter: postgresql database: rewrite host: dbhost username: centernet password: XXXXXXXXX <% end %> (Yes, I''m going to make another pass through there and merge some of the commonalities between rules). So, by default we''re using Postgres, but we can use Oracle by setting an environment variable (PHOENIX_DB=oracle). In the Oracle environment, each login has its own schema, so everyone''s got an isolated space for test_unit/test_functional. Again, maybe there''s some voodoo they pulled on the back-end that I''m not clear on, but I don''t know of anything at the moment. Rick -- http://www.rickbradley.com MUPRN: 156 | it is based on random email haiku | the Prism2 chipset, then it | is compatible.
> To help Rails to make inroads into corporate environments by sneaking > under the radar, it would be great Rails could use Oracle databases > without having to make any modifications of the db, such as adding > synonyms, otherwise the DBA gets involved and may ask more questions > than you''d like :) So anything to use a schema would help a lot.Accessing tables in a different schema is supported in edge rails. Personally I find that using private synonyms is the cleanest approach, but in environments where that isn''t allowed, you can specify a table as set_table_name "owner.table_name" If that''s not working for you (again, with edge rails), let me know and I''ll help you work through it.
I believe you''ve muddled the concepts of tablespace and schema. Tablespaces can be implemented behind the scenes as files in the underlying OS (though often a single tablespace is also made up of several files), and it''s fairly common to put tables with different characteristics into different tablespaces. But within SQL you don''t reference a table with it''s tablespace. If you''re doing something like... SELECT * FROM foo.people ...then "foo" is a schema/user name, NOT a tablespace name. Eustáquio Rangel wrote:> Hello guys, thanks for all the answers and tips. > > >>I think by tablespace you mean schema. Is that correct? In that case you >>can use synonyms on the database, after granting the apropriate rights >>to the user you connect with. Or you can use Jeremys solution (but you >>have to grant [select|insert|update|whatever] to your connecting user >>first). > > > The tablespaces are really different files, one not so big named like > > not_so_big_and_not_so_often_updated > > with a table called PEOPLE, for example, and other > > big_and_often_updated > > with a table called ORDERS, for example, so we keep it on different > files. > To refer to a table with a user, a need something like > > not_so_big_and_not_so_often_updated.PEOPLE > big_and_often_updated.ORDERS > > Even if I can insert the "alias" after created the controller, I can''t > see a way to create the controller with the "alias" of them and seems > that I''ll need to hack it after created, as you told me. And using the > current_schema will not allow me to make queries like > > select a.customer_id, a.name, b.order, b.value > from > not_so_big_and_not_so_often_updated.PEOPLE a, > big_and_often_updated.ORDERS b > where > a.customer_id=b.customer_id > > > >
> I believe you''ve muddled the concepts of tablespace and schema.You''re right! I messed with some concepts because the tablespaces are the same name as the schemas. I have a "parameters" tablespace and user, and a "data" tablespace and user. :-p> But within SQL you don''t reference a table with it''s tablespace. If > you''re doing something like... > SELECT * FROM foo.people > ...then "foo" is a schema/user name, NOT a tablespace name.That''s the way the queries are here. Using schema/user.table. But, being a tablespace or schema, the best way is using the synonyms or edge rails, right? :-) You gave me a very good tip. I think we''ll be able to use synonyms. Thanks! -- Posted via http://www.ruby-forum.com/.
Simon Santoro wrote:> Blair Zajac wrote: > >>>This is a bug in AR for sure. >>>Maybe you can use >>>alter session set current schema=its; >>>before executing the query? >>>I think that should work, but it''s a very ugly hack. >> >> >>I tried adding this to my AR class, but it didn''t work. Here''s from the >>command line: >> >>$ sqlplus username/passwd@dbhost >> >>SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 22 15:20:54 2005 >> >>Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. >> >> >>Connected to: >>Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - 64bit >>Production >>With the Partitioning, Real Application Clusters, OLAP and Data Mining >>options >> >>SQL> alter session set current schema=its; >>alter session set current schema=its >> * >>ERROR at line 1: >>ORA-00922: missing or invalid option >> >> >>What''s the correct syntax? > > > oh serry. i wrote this without checking. the correct syntax is > alter session set current_schema=its;Thanks, that works like a charm. Changing my model to read like this: class Issue < ActiveRecord::Base connection.execute(''alter session set current_schema=its'') end got the user to properly see the table. Regards, Blair
Michael Schoen wrote:>> To help Rails to make inroads into corporate environments by sneaking >> under the radar, it would be great Rails could use Oracle databases >> without having to make any modifications of the db, such as adding >> synonyms, otherwise the DBA gets involved and may ask more questions >> than you''d like :) So anything to use a schema would help a lot. > > Accessing tables in a different schema is supported in edge rails. > > Personally I find that using private synonyms is the cleanest approach, > but in environments where that isn''t allowed, you can specify a table as > > set_table_name "owner.table_name" > > If that''s not working for you (again, with edge rails), let me know and > I''ll help you work through it.Hi Michael, Yes, using set_table_name isn''t working for me. When I do that, I get this error: >> i = Issue.find(123185) ActiveRecord::StatementInvalid: OCIError: ORA-00904: "ITS"."ISSUE"."ITS"."ISSUEID": invalid identifier: select * from (select raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE (its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where raw_rnum_ > 0 Note the double appearance of its in the WHERE clause. Regards, Blair -- Blair Zajac, Ph.D. <blair-szbw9MROnEZWk0Htik3J/w@public.gmane.org> Subversion and Orca training and consulting http://www.orcaware.com/svn/
Rick Bradley wrote:> * Blair Zajac (blair-szbw9MROnEZWk0Htik3J/w@public.gmane.org) [051122 18:25]: > >>>But you can use private synoyms. >>>for example: >>>connect as user a >>>grant select on person to user_b; >>>connect as user b >>>create synonym person for user_a.person; >>>select * from person; >>> >>>This works, and the synonym person is only visible to user b >> >>To help Rails to make inroads into corporate environments by sneaking under >>the radar, it would be great Rails could use Oracle databases without >>having to make any modifications of the db, such as adding synonyms, >>otherwise the DBA gets involved and may ask more questions than you''d like >>:) So anything to use a schema would help a lot. > > > We''ve been using Rails on an Oracle database (and on Postgres) since > before RubyConf and we''re not currently using any sort of synonyms, etc. > Our DBAs set up a development schema, a production schema, and N testing > schemas (1 for each developer, one for nightly demo builds, one for > continuous integration builds). I''m not a DBA so perhaps I''m missing a > salient detail, but after removing the troublesome synonyms we haven''t > had any problems in this configuration.I''m not saying it doesn''t work when it''s all set up properly. In my case, I needed to write a new Rails script using script/runner and didn''t want to have the DBA make changes for the app, as the less work he did, the better for me and getting Rails used. For the new app, they set up a new user ''svn2its'', which didn''t have ''its'' as its default schema and is also not the owner. So anything in Rails to make this case easier to work with (no synonyms, no default schema, no ownership) would be great. Maybe that''s asking for too much :) But using the class Issue < ActiveRecord::Base connection.execute(''alter session set current_schema=its'') end trick did work. Although, this may not work if you have Rails working with more than one schema. In this case, I think the easiest way to do this is to introduce an intermediate class and have the final model subclass it: class OracleSchemaA < ActiveRecord::Base establish_connection connection.execute(''alter session set current_schema=SCHEMAA'') end class OracleSchemaB < ActiveRecord::Base establish_connection connection.execute(''alter session set current_schema=SCHEMAB'') end class Post < OracleSchemaA set_table_name ''post'' end class Author < OracleSchemaB set_table_name ''author'' end There are two classes that explicitly create separate connections to the database, each with its own current_schema. The set_table_names are required, otherwise AR seems to want to use oracleschemaX as the table name. Regards, Blair
> Yes, using set_table_name isn''t working for me. > When I do that, I get this error: > Note the double appearance of its in the WHERE clause.That may be a bug then, I''ll take a look. Executing the "current_schema" statement is a nice hack, but as you pointed out in your other note, it''s not a terribly good one. Let''s track down the bug and fix it.
> Yes, using set_table_name isn''t working for me. > > When I do that, I get this error: > > >> i = Issue.find(123185) > ActiveRecord::StatementInvalid: OCIError: ORA-00904: > "ITS"."ISSUE"."ITS"."ISSUEID": invalid identifier: select * from (select > raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue WHERE > (its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) where > raw_rnum_ > 0 > > Note the double appearance of its in the WHERE clause.What revision of Rails are you using? I just confirmed again that with edge rails this works properly.
Michael Schoen wrote:>> Yes, using set_table_name isn''t working for me. >> >> When I do that, I get this error: >> >> >> i = Issue.find(123185) >> ActiveRecord::StatementInvalid: OCIError: ORA-00904: >> "ITS"."ISSUE"."ITS"."ISSUEID": invalid identifier: select * from >> (select raw_sql_.*, rownum raw_rnum_ from (SELECT * FROM its.issue >> WHERE (its.issue.its.issueid = 123185) ) raw_sql_ where rownum <= 1) >> where raw_rnum_ > 0 >> >> Note the double appearance of its in the WHERE clause. > > > What revision of Rails are you using? I just confirmed again that with > edge rails this works properly.I just retried with Rails edge (with ''rake freeze_edge''), now at revision 3166. Regards, Blair
>> What revision of Rails are you using? I just confirmed again that with >> edge rails this works properly. > > I just retried with Rails edge (with ''rake freeze_edge''), now at > revision 3166.And? Did you get the same error? Edge is currently 3173, though I don''t see any changes since 3166 that should matter. I''m going offline for a bit. If this still isn''t working let me know and let''s take it off the list -- I''d like to send you some debug statements to apply that may help identify the issue.
Michael Schoen wrote:>>> What revision of Rails are you using? I just confirmed again that >>> with edge rails this works properly. >> >> >> I just retried with Rails edge (with ''rake freeze_edge''), now at >> revision 3166. > > > And? Did you get the same error? Edge is currently 3173, though I don''t > see any changes since 3166 that should matter.Yes, same error, even with r3174.> I''m going offline for a bit. If this still isn''t working let me know and > let''s take it off the list -- I''d like to send you some debug statements > to apply that may help identify the issue.OK. Send me the debug statements privately and I''ll test them out. It would be interesting to see if you can find a similar select statement in your rails application and see what queries its doing. Regards, Blair
> OK. Send me the debug statements privately and I''ll test them out. > > It would be interesting to see if you can find a similar select > statement in your rails application and see what queries its doing.That''s what I tried -- I created what I think is an exact replica or your scenario, and it worked properly -- the schema name was not duplicated.