Link to home
Start Free TrialLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

table not found error

I am attempting to join two different databases and tables to create a unique list of items.

SELECT FTCSPROD.ADDAS_MEAS_CONFIG.AP_NO,
      APDBMS.ACTIVE_AIRPLANES.AIRPLANENBR
FROM FTCSPROD.ADDAS_MEAS_CONFIG, 
      APDBMS.ACTIVE_AIRPLANES
WHERE FTCSPROD.ADDAS_MEAS_CONFIG.AP_NO = APDBMS.ACTIVE_AIRPLANES.AIRPLANENBR;

Open in new window


I am running the statement from the FTCSPROD database and I am getting the following error.

Can anyone explain why it is not finding the other database when I have both of them available?

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 4 Column: 13
Avatar of Sean Stuber
Sean Stuber

In the usage you have above FTCPROD and APDBMS are "schemas"  not "databases"

ORA-942 errors are caused by only 2 things.

1 - the object really doesn't exist (check for misspelling)

2 - you don't have permissions for the object   - If you don't have permission to read or modify the object, then Oracle won't even let you know if it's really there.


if you are logged in as FTCPROD, then have the APDBMS owner, or a dba run this command

grant select on APDBMS.APDBMS.ACTIVE_AIRPLANES to FTCPROD;
Avatar of Karen Schaefer

ASKER

"If you are logged in as FTCPROD, then have the APDBMS owner, or a dba grant select on the APDBMS tables to FTCPROD ?????????

i am relatively new to this please clarify what you mean.

K
SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I tried the following with out success:
grant select on APDBMS to public;
SELECT AP_NO
FROM ADDAS_MEAS_CONFIG
WHERE AP_NO = (SELECT AIRPLANENBR FROM APDBMS.ACTIVE_AIRPLANES)
ORDER BY AP_NO;

Open in new window


I am the owner of both - so why can't I use the APDBMS table?

K
you might be the "human" responsible for the data and application but as far as oracle is concerned the schema accounts are the owners.  "You" are not a schema.

you are only ever logged in under one account at a time, so no matter which schema you log in as, you are only ever the owner of one set of objects at a time.

what is the error you got with the above?

with what user did you log in?
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks for your input