Hey there! It’s super frustrating when your connection tests pass but you can’t pull data from existing tables—let’s break down the most likely fixes for your ORA-00942 error.

1. Fix Table Name Case & Schema Ownership Mismatches #

Oracle treats unquoted table names as uppercase by default, but Rails generates queries using lowercase names out of the box. If your actual table is named USERLISTS (not userlists), Rails will fail to locate it.

Quick Fixes: #

• Explicitly set the uppercase table name in your model:

  # app/models/userlist.rb
  class Userlist < ApplicationRecord
    self.table_name = 'USERLISTS'
  end
  

• If the table lives under a different database schema (not your dbuser’s default), specify that too:

  class Userlist < ApplicationRecord
    self.table_name = 'TARGET_SCHEMA.USERLISTS'
    # Alternatively split schema and table:
    # self.schema = 'TARGET_SCHEMA'
    # self.table_name = 'USERLISTS'
  end
  

• Verify your dbuser has access to the table: Run this SQL as a privileged Oracle user to grant permissions if needed:

  GRANT SELECT, INSERT, UPDATE, DELETE ON TARGET_SCHEMA.USERLISTS TO dbuser;
  

2. Clean Up Your database.yml Configuration #

Looking at your current config, there are a couple of red flags:

• Your development environment doesn’t inherit the default settings (like host/port), which could mean you’re connecting to a different instance than your successful test.
• The database field for Oracle Enhanced Adapter should use the full connection string format //host:port/service_name for remote servers.

Updated database.yml Example: #

default: &default
  adapter: oracle_enhanced
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  host: your_server_host
  port: your_port_num
  database: your_service_name # Or use full string: //your_server_host:your_port_num/your_service_name
  username: dbuser
  password: dbpass

development:
  <<: *default # Inherit all default settings
  # Override dev-specific values here if needed

3. Test Directly in Rails Console to Isolate the Issue #

To narrow down whether the problem is with your model or the connection itself, run these commands in rails c:

• First, test a raw SQL query to confirm the table is accessible:

  ActiveRecord::Base.connection.execute("SELECT COUNT(*) FROM USERLISTS").first
  

  If this returns a number, your connection is solid and the issue is with your model’s table name setup.
• If the raw query fails, double-check the table’s schema and your user’s permissions directly in Oracle.

4. Confirm You’re Using the Correct Rails Environment #

Sometimes Rails might accidentally use a different environment (like test) instead of development. Make sure you’re running your server/console in the right mode:

rails server -e development
# Or for console:
rails c development

内容的提问来源于stack exchange，提问作者Chi Hian Chan