We have 2 schemas in our DB and I am trying to access the external table row count in schema A from schema B.
The external table has a grant GRANT SELECT ON A.LETTER TO B WITH GRANT OPTION;
When I call: SELECT COUNT(*) FROM A.LETTER; from Schema B I always get ORA-06564: object A does not exist
This is the same grant we have on regular tables and we are able to get counts from these tables. I'm not sure what the problem is here or how best to fix it?
Schema B needs to be granted read privileges on the external table's directory (at least; it also needs write unless the table has NOBADFILE and NOLOGFILE):
GRANT READ,WRITE ON DIRECTORY A TO B;
It isn't clear if you actually have a directory object with the same name as the schema, or if you mixed up the names a bit hiding them in the question. But the object name in the ORA-06564 should be a directory object name.
It's also possible the external table references more than one directory - for the data file, logs file, etc. - and appropriate privileges would need to be granted to all of them if so.
If you don't want B to be able to access the directory you could create a view and grant select on that instead:
CREATE VIEW A.V_LETTER AS SELECT * FROM A.LETTER;
GRANT SELECT ON A.V_LETTER TO B;
Then B can SELECT COUNT(*) FROM A.V_LETTER, without needing directory privs; but having a different name may be an issue for you (or at least be slightly confusing). B could create a synonym for A.V_LETTER of course, but then you'd have SELECT COUNT(*) FROM LETTER or FROM B.LETTER, which still don't match the pattern you seem to be using.