Getting "Table doesn't exist" when trying to use an Oracle Table

[Mark]

Hello,
I'm personally inexperienced with Oracle, but for one aspect of my job
I need to get some data from an Oracle database table that is
elsewhere in the company.

Previously we have had an MS Access application that acts as a front
end to this particular table, and it has
worked OK for daily needs. However Access isn't going to cut it as I
want to do some more detailed work, and will probably need to iterate
through the records collecting the data we need. I've tried doing the
relevant join, but I'm just getting an out of memory error.

Anyway, I may be having a lack-of-knowledge of Oracle problem, or it
may be some kind of problem with the adapter.
I really have no idea so apologies if posting here is not the correct
place.

in a ruby script/console
>> OracleTableName
=> OracleTableName(Table doesn't exist)

and in the development log I have

←[4;35;1mSQL (359.4ms)←[0m ←[0m
select decode(table_name,upper(table_name),lower
(table_name),table_name) name from all_tables where owner = sys_context
('userenv','session_user')←[0m

If I view the table in the linked table manager in MS Access (which is
genuinely my only insight into the Oracle database)

The table in question is named like this.

USER_NAME.TABLE_NAME

Now I don't know if the fact that it is named like
USER_NAME.TABLE_NAME is coincidental or if that's an Oracle standard.

My ActiveRecord class looks like

class OracleTableName< ActiveRecord::Base

set_table_name "USER_NAME.TABLE_NAME"
end

But I've tried all sorts of combinations of set_table_name such as
"USER_NAME.TABLE_NAME"
"[USER_NAME].TABLE_NAME"
"TABLE_NAME"
"[TABLE_NAME]"
"USER_NAME\.TABLE_NAME"

etc

But I'm not sure if this is the problem.

I've also tried searching for "period in Oracle table name" etc to no
avail.

I have already tried to contact the relevant people within the
company, but they don't speak English,
are not technical, and the people who created the oracle database were
an out-sourced company
who have since disappeared.

So apologies for asking here, if this is the wrong place, but I'm
really at a loss for where
to look next.

Hopefully, if it's not a problem with this adapter then someone here
has enough Oracle experience to
know where I'm going wrong?

Also apologies if anonymizing the variable names is confusing, but I
have to be careful not to
reveal anything by accident.

Thanks,

Mark

[Raimonds Simanovskis]

At first please specify user name and table name in lowercase (but I
think uppercase name also should work - need to check):

set_table_name "user_name.table_name"

And then also verify that you have privilege to access this table in
Oracle - connect with sqlplus (or some other tool) to Oracle database
with the same username and password that you specified in database.yml
and try to "SELECT * FROM user_name.table_name"

Raimonds

[Mark Burns]

I've already verified the permissions are OK, as previously I had a typo
in database.yml and was getting rejected on that basis.

Lower case doesn't make a difference.

I tried with SQL Plus and can connect OK, and the select on the table
works as expected.

So still drawing a bit of a blank here.



2009/11/2 Raimonds Simanovskis <raimonds.s...@gmail.com>

[Mark Burns]

I wonder if I have my database.yml file set incorrectly though. I didn't think so because
of the previous error with the connection which is now not happening.

oracle_config:
  adapter: oracle_enhanced
  host: <IP_ADDRESS>
  database: '<same value used for SERVICE_NAME in TNS file>'
  username: <USER_NAME>
  password: <PASSWORD>
  port: <PORT>

[Bill Caputo]

Hi Mark,

On Mon, Nov 2, 2009 at 7:45 AM, Mark Burns <markbur...@gmail.com> wrote:
> I wonder if I have my database.yml file set incorrectly though. I didn't
> think so because
> of the previous error with the connection which is now not happening.

I don't use the host or port settings as they are specified in my
tnsnames.ora file:
oracle_config:
  adapter: oracle_enhanced

  database: '<same value used for SERVICE_NAME in TNS file>'
  username: <USER_NAME>
  password: <PASSWORD>

Maybe that's it?

Bill

[Raimonds Simanovskis]

Well, hard to guess what is wrong :)

Try to run script/console and in it try
ActiveRecord::Base.connection.select_one "SELECT * FROM
user_name.table_name"

This should return first record from that table if your Rails user can
access it.

Raimonds

On Nov 2, 5:38 pm, Bill Caputo <logos...@gmail.com> wrote:
> Hi Mark,
>

[Mark Burns]

Hello Bill
I tried this but it didn't work

[Mark Burns]

Hello Raimonds,

I tried

ActiveRecord::Base.connection.select_one "SELECT * FROM user_name.table_name"

and got

ActiveRecord::StatementInvalid: OCIError: ORA-00942: table or view does not exist: SELECT * FROM user_name.table_name
        from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract_adapter.rb:212:in `rescue in log'
        from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract_adapter.rb:195:in `log'
        from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:130
4:in `log'
        from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:122
8:in `select'
        from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all'
        from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/query_cache.rb:62:in `select_all_with_query_c
ache'
        from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/database_statements.rb:13:in `select_one'
        from (irb):2
        from C:/ruby191/bin/irb:12:in `<main>'

2009/11/2 Raimonds Simanovskis <raimonds.s...@gmail.com>

[Mark Burns]

OK I may have made some progress

I tried

ActiveRecord::Base.connection.select_one "SELECT * FROM table_name"
and it froze, presumably because there are around 10,000,000 records in that table

so I tried


ActiveRecord::Base.connection.select_one "SELECT * FROM table_name WHERE ROWNUM <=1"
and got the following

from C:/ruby191/bin/irb:12:in `<main>'>> ActiveRecord::Base.connection.select_one "SELECT * FROM table_name WHERE ROWNUM <=1"
OCIInvalidHandle: Invalid Handle
from oci8.c:248:in oci8lib_191.so
from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/connection_adapters/oracle_enhanced_oci_connection.rb:223:in `new'
from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/connection_adapters/oracle_enhanced_oci_connection.rb:223:in `new_connection'
from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/connection_adapters/oracle_enhanced_oci_connection.rb:327:in `initialize'
 from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/connection_adapters/oracle_enhanced_oci_connection.rb:30:in `new'
from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/connection_adapters/oracle_enhanced_oci_connection.rb:30:in `initialize'
from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/connection_adapters/oracle_enhanced_connection.rb:9:in `new'
from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/connection_adapters/oracle_enhanced_connection.rb:9:in `create'
from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:49:in `oracle_enhanced_connection'
from C:/ruby191/lib/ruby/gems/1.9.1/gems/activerecord-2.3.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:223:in `new_connection'
etc...

Does this give any further insight?

I'm still at a loss here.

Regards,

Mark

2009/11/3 Mark Burns <markbur...@gmail.com>

[Mark Burns]

Sorry to flood the channel here, but I just realised my last post is a result of changing
the database.yml as Bill suggested. On reverting the database.yml I've got some progress.

I still get the table does not exist when trying to use the ActiveRecord class, but
the SQL does work

ActiveRecord::Base.connection.select_one "SELECT * FROM table_name WHERE ROWNUM <=1"

returns a result as expected.

What does this suggest to anyone?

[Raimonds Simanovskis]

If

ActiveRecord::Base.connection.select_one "SELECT * FROM table_name
WHERE ROWNUM <=1"

returns value then you should be able to do
set_table_name "table_name"

But I'm just curious what is this "table_name"? If the table name is
not in schema of Rails user then there should be some local or public
synonym to table in other schema.

Can you connect with Rails user to database using sqlplus and execute
"DESC table_name"?

Raimonds

[Mark Burns]

If

ActiveRecord::Base.connection.select_one "SELECT * FROM table_name
WHERE ROWNUM <=1"

returns value then you should be able to do
set_table_name "table_name"

But I'm just curious what is this "table_name"? If the table name is
not in schema of Rails user then there should be some local or public
synonym to table in other schema.

I must admit you've slightly lost me here.
Is the term schema in this instance an Oracle specific term? Also what do you mean by "Rails user"?
Or perhaps I need the term "schema of Rails user" explaining.

If it clarifies:

As I've mentioned if I use the same credentials to log in to SQL Plus and use
the same table name then it seems OK.

By that I mean using the same details that are stored in the file
C:\oracle\ora92\network\ADMIN\tnsnames.ora, and the same values in database.yml

Also, I'm purposefully obscuring the table names on here because I don't wish to reveal
anything business specific if that's what your asking. (But I am making sure to use
the real values in my code)

executing
DESC table_name
works as expected too

[Raimonds Simanovskis]

Some clarification
1) In Oracle database "user" (which you use to log in) and
"schema" (where tables are located) are kind of synonyms - each user
have schema with the same name as user.
2) When you reference table in SELECT statement then you can specify
"table_name" or you can specify "schema_name.table_name". If you
specify just "table_name", then it will try to find one of the
following a) table in local schema, b) view in local schema, c) local
synonym to table or view in other schema, d) public synonym to table
or view in other schema. If you specify "schema_name.table_name" then
it will try to find table, view or synonym in the specified
"schema_name"
3) When you try to select from table in other schema then there should
be grant to current user to select from the table in the other schema.
4) By "Rails user" I mean Oracle user that you specify in database.yml
file that is used by ActiveRecord to connect to database.

So what I am trying to understand
1) Is the table (that you try to access) in local Rails user schema or
in other schema?
2) And is this "table_name" a table or maybe view or synonym?
3) Maybe you can show the real table name - maybe it contains some
special non-alphanumeric characters?

And it would probably be easier if you could show the actual table
definition and class definition - it seems that something is "lost in
translation" :)
And when you have errors then please paste the whole error message
with Ruby stack trace - it also can help.

Raimonds